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 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 quotation marks.
See 1.1.7 Specification of names for details on cursor names.
Specifies that a holdable cursor is to be used. A holdable cursor cannot be used in the following cases:
For details on holdable cursors, see the HiRDB Version 8 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.8.3 Results-set return facility.
Specifies the SQL statement identifier assigned to the SELECT statement that was preprocessed by the PREPARE statement.
Common rules
Rule related to referential constraints
Notes
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