Closing a Cursor v11
Once all the desired rows have been retrieved from the cursor result set, the cursor must be closed. Once closed, the result set is no longer accessible. The CLOSE
statement appears as follows:
CLOSE <name>;
name
is the identifier of a cursor that is currently open. Once a cursor is closed, it must not be closed again. However, once the cursor is closed, the OPEN
statement can be issued again on the closed cursor and the query result set will be rebuilt after which the FETCH
statement can then be used to retrieve the rows of the new result set.
The following example illustrates the use of the CLOSE
statement:
CREATE OR REPLACE PROCEDURE cursor_example IS v_emp_rec emp%ROWTYPE; CURSOR emp_cur_1 IS SELECT * FROM emp; BEGIN OPEN emp_cur_1; FETCH emp_cur_1 INTO v_emp_rec; DBMS_OUTPUT.PUT_LINE('Employee Number: ' || v_emp_rec.empno); DBMS_OUTPUT.PUT_LINE('Employee Name : ' || v_emp_rec.ename); CLOSE emp_cur_1; END;
This procedure produces the following output when invoked. Employee number 7369, SMITH
is the first row of the result set.
EXEC cursor_example; Employee Number: 7369 Employee Name : SMITH