Oracle ref cursor and sys_refcursor, refsys_refcursor

Oracle ref cursor and sys_refcursor, refsys_refcursor

1. Customize ref cursor and sys_refcursor;
2. sys_refcursor is used as the parameter to pass the result set;
3. ref cursor is used as the parameter to pass the result set;

 

1. Custom ref cursor and sys_refcursor:

  declare
Type df_ref is ref cursor; -- Define ref cursor
Rf df_ref; -- declare 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;
 close rf; 
end;
/

Sys_refcursor can be directly used without declaration:

 declare
 reft sys_refcursor;
  begin
   open reft for 'select * from emp';
  close  reft;  
 end;

You can use refcursor in sqlplus:

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 is used as the parameter to pass the result set:

 create or replace procedure pro_getEmp(ref_rs out sys_refcursor)
 is
  begin
  open ref_rs for 'select ename,empno from emp';
--- Cannot be disabled 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:
Define ref cursor in the header:

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. 

Oracle ref cursor Problems

Yes
However, your type is incorrect.
If you only apply for one
Type c_cursor is ref cursor
Then you can directly
V_a package. c_cursor
Okay.
The following % rowtype is not required
 
How to Use ref cursor to process Oracle result sets

Type -------------------- -------- ------------ empno not null number (4) ENAME VARCHAR2 (10) JOB VARCHAR2 (9) mgr number (4) hiredate date sal number (7, 2) comm number (7, 2) deptno number (2) Finally, use ref cursor to obtain the result set output: SQL> set serveroutput on SQL> DECLARE 2 TYPE mytable IS TABLE OF emp % ROWTYPE; 3 l_data mytable; 4 l_refc sys_refcursor; 5 BEGIN 6 OPEN l_refc FOR 7 SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno FROM emp; 8 9 FETCH l_refc bulk collect into l_data; 10 11 CLOSE l_refc; 12 13 FOR I IN 1 .. l_data.COUNT 14 LOOP 15 DBMS_OUTPUT.put_line (l_data (I ). ename 16 | 'was hired since '17 | l_data (I ). hiredate 18); 19 end loop; 20 END; 21/SMITH was hired since 17-DEC-80 ALLEN was hired since 20-FEB-81 WARD was hired since Dow JONES was hired since MARTIN was hired since between BLAKE was hired since 01-MAY-81 CLARK was hired since copyright SCOTT was hired since indexing KING was hired since 17-NOV-81 TURNER was hired since 08-SEP-81 ADAMS was hired since 23-MAY-87 JAMES was hired since FORD was hired since indexing MILLER was hired since 23 -JAN-82 PL/SQL procedure successfully completed. (responsible editor: Lu Zhaolin)