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
