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