Closing a Cursor Variable v11
Use the CLOSE
statement described in Closing a Cursor to release the result set.
Note
Unlike static cursors, a cursor variable does not have to be closed before it can be re-opened again. The result set from the previous open will be lost.
The example is completed with the addition of the CLOSE
statement.
CREATE OR REPLACE PROCEDURE emp_by_dept ( p_deptno emp.deptno%TYPE ) IS emp_refcur SYS_REFCURSOR; v_empno emp.empno%TYPE; v_ename emp.ename%TYPE; BEGIN OPEN emp_refcur FOR SELECT empno, ename FROM emp WHERE deptno = p_deptno; DBMS_OUTPUT.PUT_LINE('EMPNO ENAME'); DBMS_OUTPUT.PUT_LINE('----- -------'); LOOP FETCH emp_refcur INTO v_empno, v_ename; EXIT WHEN emp_refcur%NOTFOUND; DBMS_OUTPUT.PUT_LINE(v_empno || ' ' || v_ename); END LOOP; CLOSE emp_refcur; END;
The following is the output when this procedure is executed.
EXEC emp_by_dept(20) EMPNO ENAME ----- ------- 7369 SMITH 7566 JONES 7788 SCOTT 7876 ADAMS 7902 FORD