OPEN statement Format 1 (Open cursor)
Function
Opens a cursor. The OPEN statement opens a cursor either declared in the DECLARE CURSOR statement or allocated by the ALLOCATE CURSOR statement, and positions the cursor before the first row of retrieval results so that the retrieval results can be fetched.
In Format 1, the OPEN instruction opens a cursor by using an embedded variable to assign a value to the ? parameter.
Privileges
To open a cursor, the user must have the SELECT privilege for all tables that are specified in the cursor declaration or in the ALLOCATE CURSOR statement.
Format 1: Opening a cursor (by assigning values to ? parameters using embedded variables)
OPEN {cursor-name | extended-cursor-name} [USING :embedded-variable[,:embedded-variable] ...]
Operands
Specifies new embedded variables when the embedded variables specified in the SELECT statement of the DECLARE CURSOR statement are to be changed.
When values are assigned to the ? parameters specified in the SELECT statement preprocessed by the PREPARE statement, the embedded variables to which the values are assigned should be specified.
The values of the embedded variables specified in the SELECT statement of the DECLARE CURSOR statement or the values of the ? parameters remain in effect as SQL-runtime values until the cursor is closed. These values can be modified by closing the cursor and then reopening it.
The embedded variables specified in the USING clause replace in the order in which they are specified the embedded variables specified in the SELECT statement in the cursor declaration.
The embedded variables specified in the USING clause assign values to the ? parameters specified in the prepared SELECT statement in the order in which they are specified.
Notes
Example
Open cursor CR1 in order to fetch retrieval results from a stock table named STOCK:
OPEN CR1