1. Custom ref cursor and sys_refcursor;
2. Sys_refcursor as a parameter to pass the result set;
3. The REF CURSOR is passed as a parameter to the result set;
1. Custom REF CURSOR and Sys_refcursor:
Declare
Type df_ref is REF CURSOR; --Define REF CURSOR
RF Df_ref; --Declaring that RF is 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;
End
/
Sys_refcursor does not require a declaration to be used directly:
Declare
Reft Sys_refcursor;
Begin
Open Reft for ' select * from EMP ';
Close Reft;
End
Refcursor can be used in Sqlplus:
[email protected]%11gr2>variable R Refcursor;
[Email protected]%11gr2>exec open:r for ' select * from EMP ';
PL/SQL procedure successfully completed.
elapsed:00:00:00.00
[Email protected]%11gr2>print:r;
EMPNO ename JOB MGR hiredate SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH Clerk 7902 17-dec-80 20
7499 ALLEN salesman 7698 20-feb-81 30
7521 WARD salesman 7698 22-feb-81 1250 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 20
7839 KING President 17-nov-81 10
7844 TURNER salesman 7698 08-sep-81 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 20
7934 MILLER Clerk 7782 23-jan-82 1300 10
Rows selected.
2. Sys_refcursor to pass the result set as a parameter:
Create or Replace procedure pro_getemp (ref_rs out Sys_refcursor)
Is
Begin
Open ref_rs for ' select Ename,empno from emp ';
---can't close here.
End
/
Call result set:
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 passes the result set as a parameter:
Define REF CURSOR in Baotou:
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
/
[Email protected]%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 20
7499 ALLEN salesman 7698 20-feb-81 30
7521 WARD salesman 7698 22-feb-81 1250 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 20
7839 KING President 17-nov-81 10
7844 TURNER salesman 7698 08-sep-81 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 20
7934 MILLER Clerk 7782 23-jan-82 1300 10
Source: https://www.cnblogs.com/lanston/p/3993936.html
Oracle REF CURSOR and Sys_refcursor