Statements › CLOSE

CLOSE

The CLOSE statement places a specified cursor in the closed state or disassociates a received cursor from the current returned result set and associates it with the next result set returned by the procedure. Use this statement only in SQL that is embedded in a program.

Authorization

None required.

Syntax

►►─── CLOSE cursor-name ──────────────────────────────────────────────────────►◄

Parameter

cursor-name

Specifies the cursor to be closed. Cursor-name must identify a cursor previously defined by a DECLARE CURSOR statement within the application program or by an ALLOCATE CURSOR statement executed within the same SQL transaction.

Usage

Automatic Closing of Cursors

The COMMIT statement without the CONTINUE clause and the ROLLBACK statement automatically close all open cursors used by the application program.

Closing Declared Global Cursors

Cursors declared with the GLOBAL option may be closed only within the same application program in which the global cursor declaration was made. This restriction does not apply to global cursors defined using an ALLOCATE CURSOR statement.

Closing a Received Cursor

A received cursor is a dynamically allocated cursor used to process one or more result sets returned by an SQL-invoked procedure. Returned result sets are maintained in an ordered list. An ALLOCATE CURSOR statement associates the cursor with the first result set in the list and removes it from the list.

If the list of returned result sets is not empty when a received cursor is closed, the CLOSE statement causes the following actions to be taken:

• Disassociates the cursor from its current result set
• Associates the cursor with the first result set in the list of returned result sets
• Removes the result set from the list
• Positions the cursor at the same point at which the corresponding returnable cursor was left by the procedure
• Returns a warning "Additional result sets returned" (SQLSTATE "0100D)

Closing the cursor associated with the last result set of a session started by the called procedure, releases that session.

Examples

Closing a Cursor

The following CLOSE statement places the cursor named ALL_EMP_CURSOR in the closed state:

EXEC SQL
   CLOSE ALL_EMP_CURSOR
END-EXEC

Closing a Global Dynamic Cursor

The following statement closes the global cursor whose name is passed in :CNAME:

EXEC SQL
  CLOSE GLOBAL :CNAME
END-EXEC

More Information

• For more information about defining and manipulating cursors, see DECLARE CURSOR, FETCH, and OPEN.
• For more information about using cursors in an application program, see the CA IDMS SQL Programming Guide.
• For more information about closing a received cursor, see ALLOCATE CURSOR.