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

1. 自定义 ref cursor 和 sys_refcursor;

2. sys_refcursor 做为参数传递结果集; 3. ref cursor 做为参数传递结果集;

 

1. 自定义 ref cursor 和 sys_refcursor:

type df_ref  ref ; --定义 ref cursor

rf df_ref; --声明 rf 是df_ref

ename 2(30);

rf  'select ename from emp';

loop

rf  ename;

dbms_output.put_line(ename);

rf%notfound;

loop;

rf;

;

/

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

reft sys_refcursor;

reft  'select * from emp';

reft;

;

sqlplus 中可以使用refcursor:

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

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

PL/  successfully completed.

Elapsed: 00:00:00.00

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

EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO

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

7369 SMITH      CLERK           7902 17--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--81        950                    30

7902 FORD       ANALYST         7566 03--81       3000                    20

7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

14  selected.

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

replace  pro_getEmp(ref_rs out sys_refcursor)

ref_rs  'select ename,empno from emp';

---不能在这里关闭

;

/

调用结果集:

refc sys_refcursor;

ename 2(30);

empno number;

pro_getEmp(ref_rs=>refc);

loop

refc  ename,empno;

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

refc%notfound;

loop;

;

/

3. ref cursor 做为参数传递结果集:

   在包头定义 ref cursor:

replace package pk_cur

type df_cursor  ref ;

fun_emp  df_cursor;

;

/

replace package body pk_cur

fun_emp  df_cursor

fn_cursor df_cursor;

fn_cursor  'select * from emp';

fn_cursor;

;

;

/

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

FUN_EMP

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

STATEMENT : 1

STATEMENT : 1

EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO

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

7369 SMITH      CLERK           7902 17--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--81        950                    30

7902 FORD       ANALYST         7566 03--81       3000                    20

7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

14  selected.