Closing a cursor variable v14
Use the CLOSE statement described in Closing a cursor to release the result set.
Note
Unlike static cursors, you don't have to close a cursor variable before you can reopen it. When you reopen it, the result set from the previous open is lost.
This example includes 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 from this procedure:
EXEC emp_by_dept(20) EMPNO ENAME ----- ------- 7369 SMITH 7566 JONES 7788 SCOTT 7876 ADAMS 7902 FORD