Oracle ref cursor和sys_refcursor

1. 自定义 ref cursor 和 sys_refcursor;
2. sys_refcursor 做为参数传递结果集;
3. ref cursor 做为参数传递结果集;

 

1. 自定义 ref cursor 和 sys_refcursor:

  declare

  type df_ref is ref cursor; --定义 ref cursor

  rf df_ref; --声明 rf 是df_ref

  ename varchar2(30);

 begin

  open rf for 'select ename from emp';

  loop

   fetch rf into ename;

   dbms_output.put_line(ename);

  exit when rf%notfound;

 end loop;

 close rf; 

end;

/

sys_refcursor 不需要声明可以直接使用:

 declare

 reft sys_refcursor;

  begin

   open reft for 'select * from emp';

  close  reft;  

 end;

sqlplus 中可以使用refcursor:

OPS$SYWU@sydb%11GR2>variable r refcursor;

OPS$SYWU@sydb%11GR2>exec open :r for 'select * from emp';

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00

OPS$SYWU@sydb%11GR2>print :r;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO

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

      7369 SMITH      CLERK           7902 17-DEC-80        800                    20

      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30

      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30

      7566 JONES      MANAGER         7839 02-APR-81       2975                    20

      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30

      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30

      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10

      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20

      7839 KING       PRESIDENT            17-NOV-81       5000                    10

      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30

      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO

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

      7900 JAMES      CLERK           7698 03-DEC-81        950                    30

      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20

      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

14 rows selected.

2. sys_refcursor 做为参数传递结果集:

 create or replace procedure pro_getEmp(ref_rs out sys_refcursor)

 is

  begin

  open ref_rs for 'select ename,empno from emp';

  ---不能在这里关闭

 end;

/

调用结果集:

declare

refc sys_refcursor;

ename varchar2(30);

empno number;

begin

 pro_getEmp(ref_rs=>refc);

 loop

    fetch refc into ename,empno;

  dbms_output.put_line(ename||''||empno);

 exit when refc%notfound;

 end loop;

end;

/ 

3. ref cursor 做为参数传递结果集:
   在包头定义 ref cursor:

create or replace package pk_cur 

  as 

  type df_cursor is ref cursor;

  function fun_emp return df_cursor;

 end;

/

 create or replace package body pk_cur

  is

   function fun_emp return df_cursor

     is

	fn_cursor df_cursor;

     begin

       open fn_cursor for 'select * from emp';

     return fn_cursor;

   end;

end;

/

OPS$SYWU@sydb%11GR2>  select pk_cur.fun_emp from dual;

FUN_EMP

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

CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO

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

      7369 SMITH      CLERK           7902 17-DEC-80        800                    20

      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30

      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30

      7566 JONES      MANAGER         7839 02-APR-81       2975                    20

      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30

      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30

      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10

      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20

      7839 KING       PRESIDENT            17-NOV-81       5000                    10

      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30

      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO

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

      7900 JAMES      CLERK           7698 03-DEC-81        950                    30

      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20

      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

14 rows selected.