CLOSE statement (Close cursor)
Function
The CLOSE statement closes a cursor and terminates fetching of retrieval results by the FETCH statement.
Privileges
Format
CLOSE {cursor-name | extended-cursor-name}
Operands
- {cursor-name | extended-cursor-name}
- cursor-name
- Specifies the cursor that is to be closed, which must be a cursor opened by the OPEN statement.
- extended-cursor-name
- Specifies the name of an extended cursor that identifies the cursor to be closed. The cursor to be closed is one that is opened by the OPEN statement or one that is allocated to a result set returned by a procedure and one that references the result set.
- For extended cursor names, see 2.27 Extended cursor name.
Common rules
- Executing any of the SQL statements listed below closes all cursors that are open at that time. All cursors are also closed when an error with implicit rollback occurs.
Definition SQL statements (when YES is specified for PDCMMTBFDDL in the client environment definition)
PURGE TABLE statement
COMMIT statement
DISCONNECT statement
ROLLBACK statement
PREPARE statement (when YES is specified for PDPRPCRCLS in the client environment definition)
Internal DISCONNECT (termination of a UAP without executing a DISCONNECT statement)
Note that holdable cursors are not closed when a COMMIT statement is executed. When a PURGE TABLE statement is executed and a table opened using a holdable cursor is set to check pending status, the holdable cursor is closed.
- If the CLOSE statement is executed on the cursor allocated to the group of result sets returned by a procedure by the ALLOCATE CURSOR statement Format 2, and if there is a result set next to the result set currently referenced, the currently referenced result set is closed. In that case, the cursor references another result set, and the following return codes are assigned:
- A return code 121 to the SQLCODE area of the SQL communications area
- A return code 121 to the SQLCODE variable
- A return code 0100D to the SQLSTATE variable
In this case, the cursor remains open.
If another result set does not exist, the currently referenced result set is closed, and the following return codes are assigned:
- A return code 100 to the SQLCODE area of the SQL communications area
- A return code 100 to the SQLCODE variable
- A return code 02001 to the SQLSTATE variable
In this case, the extended cursor name ceases to identify any cursor.
For details about the series of operations to be performed when a cursor is allocated to the group of result sets returned from a procedure, see 1.8.3 Results-set return facility.
Note
A cursor name, similar to an embedded variable name, is effective within a compile-unit module. Therefore, multiple SQLs related to the same cursor cannot be used in multiple modules.
Example
Close cursor CR1:
CLOSE CR1