SYS_REFCUSOR

FROM: http://www.digcode.com/default.aspx?page=ed51cde3-d979-4daf-afae-fa6192562ea9&article=a6c43213-77f5-4973-a696-8b4fb0e9565a

How to Use the SYS_REFCURSOR and REF CURSOR in the Oracle Stored Procedure and Function

SYS_REFCURSOR is a built-in REF CURSOR type that allows any result set to be associated with it. SYS_REFCURSOR  can be used to:

• Delcare a cursor variable in an Oracle stored precedure/function;
• Pass cursors from and to an Oracle stored precedure/function.

Exampel 1: Using a cursor varaible in an Oracle Stored Procedure

The following Oracle stored procedure demonstrates how to use a REFCURSOR cursor variable in a stored procedure:

CREATE OR REPLACE PROCEDURE print_emp_by_dept (
    i_deptno        emp.deptno%TYPE
)
IS
    emp_refcur    SYS_REFCURSOR;
    empno         emp.empno%TYPE;
    ename         emp.ename%TYPE;
BEGIN
    OPEN emp_refcur FOR SELECT empno, ename FROM emp WHERE deptno = i_deptno;
    DBMS_OUTPUT.PUT_LINE('EMPNO    ENAME');
    DBMS_OUTPUT.PUT_LINE('-----    -------');
    LOOP
        FETCH emp_refcur INTO empno, ename;
        EXIT WHEN emp_refcur%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(empno || '     ' || ename);
    END LOOP;
    CLOSE emp_refcur;
END;

The following PL/SQL statement is used to harness the above Oracle stored procedure

DECLARE
  deptno        emp.deptno%TYPE;
BEGIN
    print_emp_by_dept(deptno);
END;

Exampel 2: Getting a cursor out from an Oracle Stored Procedure

The following Oracle stored procedure demonstrates how to get a REFCURSOR cursor variable out from a stored procedure:

CREATE OR REPLACE PROCEDURE print_emp_by_dept (
    i_deptno          emp.deptno%TYPE,
    emp_refcur in out SYS_REFCURSOR
)
IS
BEGIN
    OPEN emp_refcur FOR SELECT empno, ename FROM emp WHERE deptno = i_deptno;
END;

The following PL/SQL statement is used to harness the above Oracle stored procedure

DECLARE
  deptno        emp.deptno%TYPE;
  empno         emp.empno%TYPE;
  ename         emp.ename%TYPE;
  emp_refcur    SYS_REFCURSOR;
BEGIN
    print_emp_by_dept(deptno, emp_refcur);
    DBMS_OUTPUT.PUT_LINE('EMPNO    ENAME');
    DBMS_OUTPUT.PUT_LINE('-----    -------');
    LOOP
        FETCH emp_refcur INTO empno, ename;
        EXIT WHEN emp_refcur%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(empno || '     ' || ename);
    END LOOP;
    CLOSE emp_refcur;
END;


Exampel 3: Getting a cursor out from an Oracle Function
=============================================================







The following Oracle function demonstrates how to get a REFCURSOR cursor variable out from a function:

=============================================================

CREATE OR REPLACE FUNCTION get_emp_by_dept (i_deptno 
emp.deptno%TYPE)
RETURN SYS_REFCURSOR
IS
    
emp_refcur      SYS_REFCURSOR;
BEGIN
    OPEN 
emp_refcur FOR SELECT empno, ename FROM emp WHERE deptno = i_deptno;
    
RETURN emp_refcur;
END;
The following PL/SQL statement is used to harness the above Oracle 
function
DECLARE
  deptno        emp.deptno%TYPE;
  empno         emp.empno%TYPE;
  ename         emp.ename%TYPE;
  emp_refcur    SYS_REFCURSOR;
BEGIN
    emp_refcur := get_emp_by_dept(deptno);
    DBMS_OUTPUT.PUT_LINE('EMPNO    ENAME');
    DBMS_OUTPUT.PUT_LINE('-----    -------');
    LOOP
        FETCH emp_refcur INTO empno, ename;
        EXIT WHEN emp_refcur%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(empno || '     ' || ename);
    END LOOP;
    CLOSE emp_refcur;
END;
 
REFCURSOR vs. REF CURSOR 
Oracle supports the declaration of a cursor 
variable using both the SYS_REFCURSOR built-in data type as 
well as creating a type of REF CURSOR and then declaring 
a variable of that type.
Only the declaration of SYS_REFCURSOR and user defined 
REF CURSOR variable's is different. The remaining usage 
like opening the cursor, selecting into the cursor and closing the cursor is the 
same across both the cursor types. In the above examples wel primarily make use 
of the SYS_REFCURSOR cursors. All you need to change in the 
examples to make them work for user defined REF CURSOR's 
is the declaration section. 
The following is an example of a SYS_REFCURSOR variable declaration. 
DECLARE
    emp_refcur      SYS_REFCURSOR;
The following is an example of a cursor variable declaration. 
DECLARE
    TYPE emp_cur_type IS REF CURSOR RETURN emp%ROWTYPE;
    my_rec emp_cur_type;