本文共 2379 字，大约阅读时间需要 7 分钟。

PL/SQL ????????????????????????????????????????????????PL/SQL?????????????????????

1. ?????????

??????????????? GROUP BY ??????????????????????????

SELECT class1, SUM(CASE WHEN class1 = '1' THEN callcount ELSE 0 END) AS callcount1,       SUM(CASE WHEN class1 = '2' THEN callcount ELSE 0 END) AS callcount2,       SUM(CASE WHEN class1 = '3' THEN callcount ELSE 0 END) AS callcount3FROM tGROUP BY class1;

2. ??????

???????????????????PL/SQL ??? CURSOR ??????????????????????????

CREATE OR REPLACE FUNCTION get_c2(tmp_c1 NUMBER)RETURN VARCHAR2ISCol_c2 VARCHAR2(4000);BEGIN  FOR cur IN (SELECT c2 FROM t WHERE c1 = tmp_c1)  LOOP    Col_c2 := Col_c2 || cur.c2;  END LOOP;  Col_c2 := rtrim(Col_c2, 1);  RETURN Col_c2;END;

3. ????

????????????????????

CREATE TABLE t (  class1 VARCHAR2(2 BYTE),  calldate DATE,  callcount INTEGER);INSERT INTO t(class1, calldate, callcount)VALUES ('1', TO_DATE('08/08/2005', 'MM/DD/YYYY'), 40);INSERT INTO t(class1, calldate, callcount)VALUES ('1', TO_DATE('08/07/2005', 'MM/DD/YYYY'), 6);INSERT INTO t(class1, calldate, callcount)VALUES ('2', TO_DATE('08/08/2005', 'MM/DD/YYYY'), 77);INSERT INTO t(class1, calldate, callcount)VALUES ('3', TO_DATE('08/09/2005', 'MM/DD/YYYY'), 33);INSERT INTO t(class1, calldate, callcount)VALUES ('3', TO_DATE('08/08/2005', 'MM/DD/YYYY'), 9);INSERT INTO t(class1, calldate, callcount)VALUES ('3', TO_DATE('08/07/2005', 'MM/DD/YYYY'), 21);COMMIT;

4. ?????????

??????????????? CURSOR ? REF CURSOR ????????????????????????

CREATE OR REPLACE FUNCTION fn_rsRETURN pkg_getrecord.myrctypeISs VARCHAR2(4000);CURSOR c1 ISSELECT ',sum(case when Class1='|| class1 || ' THEN CallCount else 0 end) "' AS c2,       class1 || '"CallCount' || class1 || '" ' || c2 || ' FROM tGROUP BY class1;r1 c1%ROWTYPE;list_cursor pkg_getrecord.myrctype;BEGINs := 'SELECT CallDate ';OPEN c1;LOOPFETCH c1 INTO r1;EXIT WHEN c1%NOTFOUND;s := s || r1.c2;END LOOP;CLOSE c1;s := s || ' from T group by CallDate order by CallDate desc ';OPEN list_cursor FOR s;RETURN list_cursor;END fn_rs;

5. ?????????

???????????????????????

var results refcursor;exec :results := fn_rs;print results;

6. ??????

??????????????

CallDate        CallCount1 CallCount2 CallCount32005-08-09      0          0          332005-08-08     40         77          92005-08-07     6          0          21

????????????????????PL/SQL???????????????????????????????????????????????????