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

SYS_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 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 following postings of this topic we 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;

Example: 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;

Example: 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;