Oracle REF Cursor Example

CREATE OR REPLACE PROCEDURE get_emp_rs (p_deptno      IN     emp.deptno%TYPE,

                                        p_recordset      OUT SYS_REFCURSOR)

AS

BEGIN

   OPEN p_recordset FOR

        SELECT ename, empno, deptno

          FROM emp

         WHERE deptno = p_deptno

      ORDER BY ename;

END get_emp_rs;

-- The resulting cursor can be referenced from PL/SQL as follows.

DECLARE

   l_cursor   SYS_REFCURSOR;

   l_ename    emp.ename%TYPE;

   l_empno    emp.empno%TYPE;

   l_deptno   emp.deptno%TYPE;

BEGIN

   GET_EMP_RS (p_deptno => 30, p_recordset => l_cursor);

   LOOP

      FETCH l_cursor INTO l_ename, l_empno, l_deptno;

      EXIT WHEN l_cursor%NOTFOUND;

      DBMS_OUTPUT.PUT_LINE (l_ename || ' | ' || l_empno || ' | ' || l_deptno);

   END LOOP;

   CLOSE l_cursor;

END;