DB2 Version 10.1 for Linux, UNIX, and Windows

Closing a cursor (PL/SQL)

After all rows have been retrieved from the result set that is associated with a cursor, the cursor must be closed. The result set cannot be referenced after the cursor has been closed.

However, the cursor can be reopened and the rows of the new result set can be fetched.

Syntax


>>-CLOSE--cursor-name------------------------------------------><

Description

cursor-name: Specifies an identifier for an open cursor that was previously declared within a PL/SQL context.

Example

The following example shows a CLOSE statement for a cursor that is part of the CURSOR_EXAMPLE procedure:

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;