After all the desired rows have been retrieved from the cursor result set, the cursor must be closed. After the cursor is closed, the result set is no longer accessible. The CLOSE statement appears as follows:
name is the identifier of a cursor that is currently open. After a cursor is closed, it must not be closed again. However, after 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