Ref Cursor

REF CURSORS is the powerful, flexible, and scale-able ways to return query results from Oracle database to application. It is a PL/SQL data type whose value is the memory address of a query works on the database. In other word, a REF CURSORS is a pointer to a result set.

REF CURSORS Properties:

1. A REF CURSOR refers to a memory address on the database. Therefore, the client must be connected to the database during the lifetime of the REF CURSOR in order to access it.
2. A REF CURSOR involves an additional database round-trip. While the REF CURSOR is returned to the client, the actual data is not returned until the client opens the REF CURSOR and requests the data. Note that data is not be retrieved until the user attempts to read it.
3. A REF CURSOR is not updatable. The result set represented by the REF CURSOR is read-only. You cannot update the database by using a REF CURSOR.
4. A REF CURSOR is not backward scrollable. The data represented by the REF CURSOR is accessed in a forward-only, serial manner. You cannot position a record pointer inside the REF CURSOR to point to random records in the result set.
5. A REF CURSOR is a PL/SQL data type. You create and return a REF CURSOR inside a PL/SQL code block.

Example: Stored Procedures that uses REF Cursors

CREATE TABLE prabin_emp(
    empno    NUMBER(4,0),
    ename    VARCHAR2(10),
    job      VARCHAR2(9),
    mgr      NUMBER(4,0),
    hiredate DATE,
    sal      NUMBER(7,2),
    comm     NUMBER(7,2),
    deptno   NUMBER(2,0),
    CONSTRAINT pk_emp PRIMARY KEY (empno));

CREATE OR REPLACE PROCEDURE get_emp_rs (p_deptno IN  prabin_emp.deptno%TYPE, p_recordset OUT SYS_REFCURSOR) AS 
BEGIN 
  OPEN p_recordset FOR
    SELECT ename,
           empno,
           deptno
    FROM   prabin_emp
    WHERE  deptno = p_deptno
    ORDER BY ename;
END get_emp_rs;
/
Procedure GET_EMP_RS compiled
</pre>

Example: Returning result using Function:

CREATE OR REPLACE FUNCTION get_emp_rs_test(
    p_deptno IN NUMBER)
  RETURN sys_refcursor
AS
  p_recordset sys_refcursor;
BEGIN
  get_emp_rs(p_deptno,p_recordset);
  RETURN p_recordset;
END;
/
Function GET_EMP_RS_TEST compiled

SELECT my_proc_test(20) FROM dual;
Output:
{<ENAME=ALLEN,EMPNO=7499,DEPTNO=30>,
<ENAME=BLAKE,EMPNO=7698,DEPTNO=30>,
<ENAME=JAMES,EMPNO=7900,DEPTNO=30>,
<ENAME=MARTIN,EMPNO=7654,DEPTNO=30>,
<ENAME=TURNER,EMPNO=7844,DEPTNO=30>,
<ENAME=WARD,EMPNO=7521,DEPTNO=30>,}

Example: Returning Result Using PL/SQL

SET SERVEROUTPUT ON 
DECLARE
  l_cursor  SYS_REFCURSOR;
  l_ename   prabin_emp.ename%TYPE;
  l_empno   prabin_emp.empno%TYPE;
  l_deptno  prabin_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;
/
PL/SQL procedure successfully completed.

Output:
ALLEN  | 7499 | 30
BLAKE  | 7698 | 30
JAMES  | 7900 | 30
MARTIN | 7654 | 30
TURNER | 7844 | 30
WARD   | 7521 | 30