Modularizing cursor operations v15
This example shows how you can modularize the various operations on cursor variables into separate programs.
The following procedure opens the given cursor variable with a SELECT
command that retrieves all rows:
CREATE OR REPLACE PROCEDURE open_all_emp ( p_emp_refcur IN OUT SYS_REFCURSOR ) IS BEGIN OPEN p_emp_refcur FOR SELECT empno, ename FROM emp; END;
This variation opens the given cursor variable with a SELECT
command that retrieves all rows of a given department:
CREATE OR REPLACE PROCEDURE open_emp_by_dept ( p_emp_refcur IN OUT SYS_REFCURSOR, p_deptno emp.deptno%TYPE ) IS BEGIN OPEN p_emp_refcur FOR SELECT empno, ename FROM emp WHERE deptno = p_deptno; END;
This variation opens the given cursor variable with a SELECT
command that retrieves all rows but from a different table. The function’s return value is the opened cursor variable.
CREATE OR REPLACE FUNCTION open_dept ( p_dept_refcur IN OUT SYS_REFCURSOR ) RETURN SYS_REFCURSOR IS v_dept_refcur SYS_REFCURSOR; BEGIN v_dept_refcur := p_dept_refcur; OPEN v_dept_refcur FOR SELECT deptno, dname FROM dept; RETURN v_dept_refcur; END;
This procedure fetches and displays a cursor variable result set consisting of employee number and name:
CREATE OR REPLACE PROCEDURE fetch_emp ( p_emp_refcur IN OUT SYS_REFCURSOR ) IS v_empno emp.empno%TYPE; v_ename emp.ename%TYPE; BEGIN DBMS_OUTPUT.PUT_LINE('EMPNO ENAME'); DBMS_OUTPUT.PUT_LINE('----- -------'); LOOP FETCH p_emp_refcur INTO v_empno, v_ename; EXIT WHEN p_emp_refcur%NOTFOUND; DBMS_OUTPUT.PUT_LINE(v_empno || ' ' || v_ename); END LOOP; END;
This procedure fetches and displays a cursor variable result set consisting of department number and name:
CREATE OR REPLACE PROCEDURE fetch_dept ( p_dept_refcur IN SYS_REFCURSOR ) IS v_deptno dept.deptno%TYPE; v_dname dept.dname%TYPE; BEGIN DBMS_OUTPUT.PUT_LINE('DEPT DNAME'); DBMS_OUTPUT.PUT_LINE('---- ---------'); LOOP FETCH p_dept_refcur INTO v_deptno, v_dname; EXIT WHEN p_dept_refcur%NOTFOUND; DBMS_OUTPUT.PUT_LINE(v_deptno || ' ' || v_dname); END LOOP; END;
This procedure closes the given cursor variable:
CREATE OR REPLACE PROCEDURE close_refcur ( p_refcur IN OUT SYS_REFCURSOR ) IS BEGIN CLOSE p_refcur; END;
This anonymous block executes all the previous programs:
DECLARE gen_refcur SYS_REFCURSOR; BEGIN DBMS_OUTPUT.PUT_LINE('ALL EMPLOYEES'); open_all_emp(gen_refcur); fetch_emp(gen_refcur); DBMS_OUTPUT.PUT_LINE('****************'); DBMS_OUTPUT.PUT_LINE('EMPLOYEES IN DEPT #10'); open_emp_by_dept(gen_refcur, 10); fetch_emp(gen_refcur); DBMS_OUTPUT.PUT_LINE('****************'); DBMS_OUTPUT.PUT_LINE('DEPARTMENTS'); fetch_dept(open_dept(gen_refcur)); DBMS_OUTPUT.PUT_LINE('*****************'); close_refcur(gen_refcur); END;
The following is the output from the anonymous block:
ALL EMPLOYEES EMPNO ENAME ----- ------- 7369 SMITH 7499 ALLEN 7521 WARD 7566 JONES 7654 MARTIN 7698 BLAKE 7782 CLARK 7788 SCOTT 7839 KING 7844 TURNER 7876 ADAMS 7900 JAMES 7902 FORD 7934 MILLER **************** EMPLOYEES IN DEPT #10 EMPNO ENAME ----- ------- 7782 CLARK 7839 KING 7934 MILLER **************** DEPARTMENTS DEPT DNAME ---- --------- 10 ACCOUNTING 20 RESEARCH 30 SALES 40 OPERATIONS *****************