Closing a cursor v17
Once all the desired rows are retrieved from the cursor result set, close the cursor. After you close the cursor, you can no longer access the result set.
The CLOSE
statement appears as follows:
CLOSE <name>;
name
is the identifier of a cursor that's currently open. After you close a cursor, don't close it again. However, after you close the cursor, you can use the OPEN
statement again on the closed cursor and rebuild the query result set. After that, the FETCH
statement can then retrieve the rows of the new result set.
This example uses 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. Employee number 7369, SMITH
is the first row of the result set.
EXEC cursor_example;
Output
Employee Number: 7369 Employee Name : SMITH