REF Cursor with example

A REF Cursor is a datatype that holds a cursor value in the same way that a VARCHAR2 variable will hold a string value.

A REF Cursor can be opened on the server and passed to the client as a unit rather than fetching one row at a time. One can use a Ref Cursor as target of an assignment, and it can be passed as parameter to other program units. Ref Cursors are opened with an OPEN FOR statement. In most other ways they behave similar to normal cursors.

Example:

CREATE OR REPLACE FUNCTION fnc_dept_refcursor(p_deptno employees.department_id%type) RETURN SYS_REFCURSOR

as

c_cur SYS_REFCURSOR;

BEGIN

  OPEN c_cur FOR SELECT employee_id,last_name,salary FROM employees WHERE department_id=p_deptno;

  RETURN c_cur;

END;

--==================================================

DECLARE

  c SYS_REFCURSOR;

  TYPE  emp_rec IS RECORD(employee_id employees.employee_id%type,

                          last_name employees.last_name%type,

                          salary employees.salary%type);

  v_rec emp_rec;

BEGIN

  c:=fnc_dept_refcursor(p_deptno=>80); --Change Departmnet_Id as required

  dbms_output.put_line('Emplpyee_ID'||','||'Last_Name'||','||'Salary');

  LOOP

    FETCH c INTO v_rec;

    EXIT WHEN c%NOTFOUND;

    dbms_output.put_line(v_rec.employee_id||','||v_rec.last_name||','||v_rec.salary);

  END LOOP;

END;

Output:

Employee_ID,Last_Name,Salary

145,Russell,14000

146,Partners,13500

147,Errazuriz,12000

148,Cambrault,11000

149,Zlotkey,10500

150,Tucker,10000

151,Bernstein,9500

152,Hall,9000

153,Olsen,8000

154,Cambrault,7500