1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33
| SQL> create or replace procedure p (r out sys_refcursor) as
2 joblist varchar2(4000);
3 begin
4 SELECT listagg( ''''||libelle||''' as "'||libelle||'"',',') within GROUP (ORDER BY libelle)
5 INTO joblist FROM (SELECT DISTINCT job AS libelle FROM emp);
6
7 open r FOR
8 'select *
9 from (
10 select d.dname, e.job, e.sal
11 from dept d
12 join emp e on e.deptno = d.deptno
13 )
14 pivot (sum(sal) for (job) in ('||joblist||'))';
15 end;
16 /
Procedure created.
SQL> var rc refcursor
SQL> exec p(:rc);
PL/SQL procedure successfully completed.
SQL> print rc
DNAME ANALYST CLERK MANAGER PRESIDENT SALESMAN
-------------- ---------- ---------- ---------- ---------- ----------
ACCOUNTING 1300 2450 5000
RESEARCH 6000 1900 2975
SALES 950 2850 5600
SQL> |
Partager