Blog do Marcio Portes

Uma das grandes dúvidas que qualquer desenvolvedor que inicia com Oracle é: como eu faço uma procedure que me devolva o conteúdo de uma query. Use refcursor.

- Exemplo para 8i, tínhamos que criar o tipo ref cursor em uma package para ser usado mais tarde em sua procedure.

ops$marcio@WIN10GR2> create or replace package demo_pkg
  2  as
  3     type refx is ref cursor;
  4  end;
  5  /

Package created.

ops$marcio@WIN10GR2> create or replace procedure p1 ( p_x out demo_pkg.refx )
  2  as
  3  begin
  4     open p_x for select * from emp;
  5  end;
  6  /

Procedure created.

ops$marcio@WIN10GR2> variable x refcursor
ops$marcio@WIN10GR2>
ops$marcio@WIN10GR2> exec p1 ( :x )

PL/SQL procedure successfully completed.


        EMPNO ENAME      JOB                 MGR HIREDATE                      SAL          COMM        DEPTNO
------------- ---------- --------- ------------- ------------------- ------------- ------------- -------------
         7369 SMITH      CLERK              7902 17/12/1980 00:00:00           800                          20
         7499 ALLEN      SALESMAN           7698 20/02/1981 00:00:00          1600           300            30
         7521 WARD       SALESMAN           7698 22/02/1981 00:00:00          1250           500            30
         7566 JONES      MANAGER            7839 02/04/1981 00:00:00          2975                          20
         7654 MARTIN     SALESMAN           7698 28/09/1981 00:00:00          1250          1400            30
         7698 BLAKE      MANAGER            7839 01/05/1981 00:00:00          2850                          30
         7782 CLARK      MANAGER            7839 09/06/1981 00:00:00          2450                          10
         7788 SCOTT      ANALYST            7566 19/04/0087 00:00:00          3000                          20
         7839 KING       PRESIDENT               17/11/1981 00:00:00          5000                          10
         7844 TURNER     SALESMAN           7698 08/09/1981 00:00:00          1500             0            30
         7876 ADAMS      CLERK              7788 23/05/0087 00:00:00          1100                          20
         7900 JAMES      CLERK              7698 03/12/1981 00:00:00           950                          30
         7902 FORD       ANALYST            7566 03/12/1981 00:00:00          3000                          20
         7934 MILLER     CLERK              7782 23/01/1982 00:00:00          1300                          10

14 rows selected.

- Exemplo para 9i, a partir desta versão, existe o tipo nativo sys_refcursor.
A procedure p1 recebe como parâmetro uma query, processa e devolve o resultado em um refcursor (sys_refcursor).

ops$marcio@WIN10GR2> create or replace
  2  procedure p1 ( p_query in varchar2, p_x out sys_refcursor )
  3  as
  4  begin
  5     open p_x for p_query;
  6  end;
  7  /

Procedure created.

ops$marcio@WIN10GR2> show error
No errors.
ops$marcio@WIN10GR2>
ops$marcio@WIN10GR2> variable x refcursor
ops$marcio@WIN10GR2>
ops$marcio@WIN10GR2> exec p1 ( 'select * from dual', :x )

PL/SQL procedure successfully completed.


D
-
X

1 row selected.

ops$marcio@WIN10GR2> exec p1 ( 'select * from emp', :x )

PL/SQL procedure successfully completed.


        EMPNO ENAME      JOB                 MGR HIREDATE                      SAL          COMM        DEPTNO
------------- ---------- --------- ------------- ------------------- ------------- ------------- -------------
         7369 SMITH      CLERK              7902 17/12/1980 00:00:00           800                          20
         7499 ALLEN      SALESMAN           7698 20/02/1981 00:00:00          1600           300            30
         7521 WARD       SALESMAN           7698 22/02/1981 00:00:00          1250           500            30
         7566 JONES      MANAGER            7839 02/04/1981 00:00:00          2975                          20
         7654 MARTIN     SALESMAN           7698 28/09/1981 00:00:00          1250          1400            30
         7698 BLAKE      MANAGER            7839 01/05/1981 00:00:00          2850                          30
         7782 CLARK      MANAGER            7839 09/06/1981 00:00:00          2450                          10
         7788 SCOTT      ANALYST            7566 19/04/0087 00:00:00          3000                          20
         7839 KING       PRESIDENT               17/11/1981 00:00:00          5000                          10
         7844 TURNER     SALESMAN           7698 08/09/1981 00:00:00          1500             0            30
         7876 ADAMS      CLERK              7788 23/05/0087 00:00:00          1100                          20
         7900 JAMES      CLERK              7698 03/12/1981 00:00:00           950                          30
         7902 FORD       ANALYST            7566 03/12/1981 00:00:00          3000                          20
         7934 MILLER     CLERK              7782 23/01/1982 00:00:00          1300                          10

14 rows selected.