CLOSE statement

The CLOSE statement closes a cursor. If a temporary copy of a result table was created when the cursor was opened, that table is destroyed.

Invocation for CLOSE

This statement can only be embedded in an application program. It is an executable statement that cannot be dynamically prepared. It must not be specified in Java™.

Authorization for CLOSE

See DECLARE CURSOR statement for the authorization required to use a cursor.

Syntax for CLOSE

Description for CLOSE

cursor-name: Identifies the cursor to be closed. The cursor name must identify a declared cursor as explained in DECLARE CURSOR statement. When the CLOSE statement is executed, the cursor must be in the open state.

Notes for CLOSE

Implicit cursor close: At the end of a unit of work, all open cursors declared without the WITH HOLD option that belong to an application process are implicitly closed.

Close cursors for performance: Explicitly closing cursors as soon as possible can improve performance.

Procedure considerations: Special rules apply to cursors within procedures that have not been closed before returning to the calling program. For more information, see CALL statement.

Allocated cursors: The cursor could have been allocated. See ALLOCATE CURSOR statement.

Example for CLOSE

A cursor is used to fetch one row at a time into the application program variables DNUM, DNAME, and MNUM. Finally, the cursor is closed. If the cursor is reopened, it is again located at the beginning of the rows to be fetched.

   EXEC SQL DECLARE C1 CURSOR FOR
      SELECT DEPTNO, DEPTNAME, MGRNO
      FROM DSN8D10.DEPT
      WHERE ADMRDEPT = 'A00'
      END-EXEC.
 
   EXEC SQL OPEN C1 END-EXEC.
 
   EXEC SQL FETCH C1 INTO :DNUM, :DNAME, :MNUM END-EXEC.
 
   IF SQLCODE = 100
      PERFORM DATA-NOT-FOUND
   ELSE
      PERFORM GET-REST-OF-DEPT
      UNTIL SQLCODE IS NOT EQUAL TO ZERO.
 
   EXEC SQL CLOSE C1 END-EXEC.
 
   GET-REST-OF-DEPT.
      EXEC SQL FETCH C1 INTO :DNUM, :DNAME, :MNUM END-EXEC.