Oracle REF CURSOR and Sys_refcursor

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