CLOSE statement

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

Invocation

This statement can be embedded in an application program or issued interactively. It is an executable statement that cannot be dynamically prepared. When invoked using the command line processor, some options cannot be specified. For more information, refer to Using command line SQL statements and XQuery statements.

Authorization

If the cursor-variable-name references a global variable, then the privileges held by the authorization ID of the statement must include one of the following authorities:

READ privilege on the global variable that is not defined in a module
EXECUTE privilege on the module of the global variable that is defined in a module

For the authorization required to use a cursor, see DECLARE CURSOR.

Syntax

Description

cursor-name: Identifies the cursor to be closed. The cursor-name must identify a declared cursor as explained in the DECLARE CURSOR statement. When the CLOSE statement is executed, the cursor must be in the open state.
cursor-variable-name: Identifies the cursor to be closed. The cursor-variable-name must identify a cursor variable. When the CLOSE statement is executed, the underlying cursor of cursor-variable-name must be in the open state (SQLSTATE 24501). A CLOSE statement using cursor-variable-name can only be used within a compound SQL (compiled) statement.
WITH RELEASE: The release of all locks that have been held for the cursor is attempted. Note that not all of the locks are necessarily released; these locks may be held for other operations or activities.

Notes

At the end of a unit of work, all cursors that belong to an application process and that were declared without the WITH HOLD option are implicitly closed.
An underlying cursor of a cursor variable is implicitly closed when it becomes an orphaned cursor. An underlying cursor becomes orphaned when it is no longer an underlying cursor of any cursor variable. For example, this could occur if all the cursor variables for an underlying cursor are in the same scope and all of them go out of scope at the same time.
The WITH RELEASE clause has no effect when closing cursors defined in functions or methods. The clause also has no effect when closing cursors defined in procedures called from functions or methods.
The WITH RELEASE clause has no effect for cursors that are operating under isolation levels CS or UR. When specified for cursors that are operating under isolation levels RS or RR, WITH RELEASE terminates some of the guarantees of those isolation levels. Specifically, if the cursor is opened again, an RS cursor may experience the 'nonrepeatable read' phenomenon and an RR cursor may experience either the 'nonrepeatable read' or 'phantom' phenomenon.
If a cursor that was originally either RR or RS is reopened after being closed using the WITH RELEASE clause, new locks will be acquired.
Special rules apply to cursors within a procedure that have not been closed before returning to the calling program.
While a cursor is open (that is, it has not been closed yet), any changes to sequence values as a result of statements involving that cursor (for example, a FETCH or an UPDATE using the cursor that includes a NEXT VALUE expression for a sequence) will not result in an update to PREVIOUS VALUE for those sequences as seen by that cursor. The PREVIOUS VALUE values for these affected sequences are updated when the cursor is closed explicitly with the CLOSE statement. In a partitioned database environment, if a cursor is closed implicitly by a commit or a rollback, the PREVIOUS VALUE may not be updated with the most recently generated value for the sequence.

Example

A cursor is used to fetch one row at a time into the C 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 TDEPT
     WHERE ADMRDEPT = 'A00';

   EXEC SQL OPEN C1;

   while (SQLCODE==0) {                                             .
     EXEC SQL FETCH C1 INTO :dnum, :dname, :mnum;
        .
        .
   }
   EXEC SQL CLOSE C1;