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

Users with the SELECT privilege

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

cursor-name
Specifies the name of the cursor to be opened.
extended-cursor-name
Specifies the extended cursor name that identifies the cursor to be opened.
For extended cursor names, see 2.27 Extended cursor name.

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

  1. To reopen a cursor that is already open, it must be closed and then reopened.
  2. For details about the trigger for closing a cursor by issuing a CLOSE statement internally, see Common rule 1 in CLOSE statement (Close cursor) in this chapter.
  3. When the FETCH statement is to be executed, the OPEN statement must be used to open a cursor and then the FETCH statement relative to that cursor must be executed.
  4. When embedded variables are specified in the USING clause, values must have been assigned to them before the OPEN statement is executed.
  5. If the embedded variable specified in the USING clause contains character data that uses the default character set, and that is different from the character set used for character data type items associated with the ? parameter or embedded variable specified in the SELECT statement, it is automatically converted to the character set used by the ? parameter or embedded variable specified in the SELECT statement.
  6. When the OPEN statement is executed, HiRDB positions the cursor and performs lock control during lock of the associated FETCH statement.
  7. A cursor name, similar to an embedded variable name, is effective only within a compile-unit module. Multiple SQLs relative to the same cursor cannot be used in multiple modules.
  8. The USING clause cannot be specified in a procedure.
  9. Multiple holdable cursors cannot be opened for a single table.

Example

Open cursor CR1 in order to fetch retrieval results from a stock table named STOCK:

OPEN CR1