---------------------------------------------------------------建表

----------------判断表是否存在

declare num number;

begin

select count(1) into num from user_tables where table_name='TEST';

if num>0 then

execute immediate 'drop table TEST';

end if;

end;

----------------建表

CREATE TABLE TEST(

WL VARCHAR2(10),

XYSL INTEGER,

XYCK VARCHAR2(10),

XCLCK VARCHAR2(10),

XCLCKSL INTEGER,

PC INTEGER

);

----------------第一部分测试数据

INSERT INTO TEST VALUES('A1', 2, 'C1', 'C1' , 20, 123);

INSERT INTO TEST VALUES('A1', 2, 'C1', 'C2' , 30, 111);

INSERT INTO TEST VALUES('A1', 2, 'C1', 'C2' , 20, 222);

INSERT INTO TEST VALUES('A1', 2, 'C1', 'C3' , 10, 211);

INSERT INTO TEST VALUES('A2', 3, 'C4', 'C1' , 40, 321);

INSERT INTO TEST VALUES('A2', 3, 'C4', 'C4' , 50, 222);

INSERT INTO TEST VALUES('A2', 3, 'C4', 'C4' , 60, 333);

INSERT INTO TEST VALUES('A2', 3, 'C4', 'C5' , 70, 223);

COMMIT;

--select * from test;

---------------------------------------------------------------行转列的存储过程

CREATE OR REPLACE PROCEDURE P_TEST IS

V_SQL VARCHAR2(2000);

CURSOR CURSOR_1 IS SELECT DISTINCT T.XCLCK FROM TEST T ORDER BY XCLCK;

BEGIN

V_SQL := 'SELECT WL,XYSL,XYCK';

FOR V_XCLCK IN CURSOR_1

LOOP

V_SQL := V_SQL || ',' || 'SUM(DECODE(XCLCK,''' || V_XCLCK.XCLCK ||

''',XCLCKSL,0)) AS ' || V_XCLCK.XCLCK;

END LOOP;

V_SQL := V_SQL || ' FROM TEST GROUP BY WL,XYSL,XYCK ORDER BY WL,XYSL,XYCK';

--DBMS_OUTPUT.PUT_LINE(V_SQL);

V_SQL := 'CREATE OR REPLACE VIEW RESULT AS '|| V_SQL;

--DBMS_OUTPUT.PUT_LINE(V_SQL);

EXECUTE IMMEDIATE V_SQL;

END;

----------------------------------------------------------------结果

----------------执行存储过程，生成视图

BEGIN

P_TEST;

END;

----------------结果

SELECT * FROM RESULT T;

WL XYSL XYCK C1 C2 C3 C4 C5

---------- --------------------------------------- ---------- ---------- ---------- ---------- ---------- ----------

A1 2 C1 20 50 10 0 0

A2 3 C4 40 0 0 110 70

----------------第二部分测试数据

INSERT INTO TEST VALUES('A1', 2, 'C1', 'C6' , 20, 124);

INSERT INTO TEST VALUES('A2', 2, 'C1', 'C7' , 30, 121);

INSERT INTO TEST VALUES('A3', 2, 'C1', 'C8' , 20, 322);

COMMIT;

----------------报告存储过程，生成视图

BEGIN

P_TEST;

END;

----------------结果

SELECT * FROM RESULT T;

WL XYSL XYCK C1 C2 C3 C4 C5 C6 C7 C8

----- ----- -------- ------- -------- ---------- ---------- ---------- ---------- ---------- ----------

A1 2 C1 20 50 10 0 0 20 0 0

A2 2 C1 0 0 0 0 0 0 30 0

A2 3 C4 40 0 0 110 70 0 0 0

A3 2 C1 0 0 0 0 0 0 0 20

--------------- 删除实体

DROP VIEW RESULT;

DROP PROCEDURE P_TEST;

DROP TABLE TEST;

分享到：

2012-07-06 16:08

浏览 701

评论