Returning a REF CURSOR from a function v14
This example opens the cursor variable with a query that selects employees with a given job. The cursor variable is specified in this function’s RETURN
statement, which makes the result set 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