Closing a cursor v16

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;

Employee Number: 7369
Employee Name  : SMITH