Returning a REF CURSOR From a Function v13
In the following example the cursor variable is opened with a query that selects employees with a given job. Note that the cursor variable is specified in this function’s RETURN statement so the result set is made available to the caller of the function.
CREATE OR REPLACE FUNCTION emp_by_job (p_job VARCHAR2)
RETURN SYS_REFCURSOR
IS
emp_refcur SYS_REFCURSOR;
BEGIN
OPEN emp_refcur FOR SELECT empno, ename FROM emp WHERE job = p_job;
RETURN emp_refcur;
END;This function is invoked in the following anonymous block by assigning the function’s return value to a cursor variable declared in the anonymous block’s declaration section. The result set is fetched using this cursor variable and then it is closed.
DECLARE
v_empno emp.empno%TYPE;
v_ename emp.ename%TYPE;
v_job emp.job%TYPE := 'SALESMAN';
v_emp_refcur SYS_REFCURSOR;
BEGIN
DBMS_OUTPUT.PUT_LINE('EMPLOYEES WITH JOB ' || v_job);
DBMS_OUTPUT.PUT_LINE('EMPNO ENAME');
DBMS_OUTPUT.PUT_LINE('----- -------');
v_emp_refcur := emp_by_job(v_job);
LOOP
FETCH v_emp_refcur INTO v_empno, v_ename;
EXIT WHEN v_emp_refcur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_empno || ' ' || v_ename);
END LOOP;
CLOSE v_emp_refcur;
END;The following is the output when the anonymous block is executed.
EMPLOYEES WITH JOB SALESMAN EMPNO ENAME ----- ------- 7499 ALLEN 7521 WARD 7654 MARTIN 7844 TURNER