DECLARE CURSOR Format 2 (Declare cursor)

Function

DECLARE CURSOR declares a cursor to be used by the FETCH statement to fetch on a row-by-row basis the results of a retrieval by a query specification.

In Format 2, this SQL statement declares a cursor for a SELECT statement preprocessed by a PREPARE statement (dynamic SELECT statement).

Privileges

None.

Format 2: Declaring a cursor relative to a SELECT statement (dynamic SELECT statement) preprocessed by the PREPARE statement

DECLARE cursor-name CURSOR
  [WITH HOLD][ {WITH RETURN | WITHOUT RETURN} ] FOR SQL-statement-identifier

Operands

Specifies the name of the desired cursor.

When a cursor name is specified in a UAP, it must not be enclosed in double quotation marks, even when the cursor name is the same as an SQL reserved word. However, when a cursor name that is the same as an SQL reserved name is specified in a procedure, it must be enclosed in double quotation marks.

See 1.1.7 Specification of names for details about cursor names.

Specifies that a holdable cursor is to be used. A holdable cursor cannot be used in the following cases:

For details about holdable cursors, see the HiRDB Version 9 UAP Development Guide.

In a cursor declaration in an SQL statement, these operands specify the returnability of the result set for the cursor. A cursor declared by specifying WITH RETURN is referred to as a result set cursor. If the procedure terminates when the result set cursor declared in an SQL procedure is still open, the result set for the cursor cannot be returned to the calling source for the procedure. For details about how to use a returned result set, see 1.9.3 Results-set return facility.

Specifies the SQL statement identifier assigned to the SELECT statement that was preprocessed by the PREPARE statement.

Common rules

  1. The declared cursor remains closed.
  2. A declared cursor cannot be used in the UPDATE or DELETE statement.
  3. When multiple cursor declarations are made for the same SQL statement identifier, cursor declarations with and without the WITH HOLD specification cannot be mixed.

Rule related to referential constraints

  1. A holdable cursor that is used to retrieve a table in which a foreign key is defined is closed when the table being retrieved goes into check pending status.

Notes

  1. Similar to an embedded variable, a cursor name is effective within a compile-unit module. Multiple SQLs relative to the same cursor cannot be used in different modules.
  2. The corresponding PREPARE statement must be coded before the cursor declaration.
  3. A cursor declaration must be coded before any SQL statement that references the cursor name used in the declaration.
  4. Because DECLARE CURSOR is not an executable statement, no return code is returned to SQLCODE (and return code testing should not be performed).
  5. By applying the work table creation suppression feature of the update SQL statement in the SQL optimization option and using the index key-value no-lock facility, you can update, add, or delete rows while using a cursor for which neither FOR UPDATE nor FOR UPDATE OF is specified.

Example

Declare cursor CR1 to fetch rows, one row at a time, that have been specified by a prepared SELECT statement (SQL statement identifier is SEL):

DECLARE CR1 CURSOR FOR SEL