An OPEN statement establishes the contents of a cursor.
|
Embedded Mode P |
Dynamic Mode |
Interactive Mode |
Syntax
|
cursor identifier |
Identifies the cursor to be used. |
|
host variable identifier 1 |
A valid single host variable identifier. It must have been defined in the application program according to the host language rules. The value of the host variable must be a valid cursor identifier. A host variable can be used as cursor identifier only if the cursor is a dynamically declared cursor. |
|
statement identifier |
A valid identifier denoting the name of the prepared statement. |
|
host variable identifier 2 |
A valid single host variable identifier. It must have been defined in the application program according to the host language rules. The value of the host variable must be the value returned by the PREPARE statement and thus identifies the prepared statement. |
|
USING clause |
Defines an SQL descriptor area used to supply data to the associated dynamic cursor. |
The OPEN statement causes the contents of the associated resultant table to be established. The cursor is initially positioned before to the first row. The cursor can be identified by use of a host variable only if the cursor is declared dynamically. Likewise, the USING clause can be used to provide input values only if the cursor is a dynamically declared cursor. Alternatively, values can be provided by the direct use of host variables.
The cursor to be opened must have been declared and must not be open. If the statement does not contain a CURSOR FOR clause the cursor must have been declared before.
The statement must be in the same compilation unit as the associated DECLARE CURSOR statement.
All cursors opened within a transaction are automatically closed by a COMMIT or ROLLBACK statement.
The USING clause must not be used.
The associated DECLARE CURSOR statement must physically precede the OPEN statement in the host program.
The CLOSE statement is the only statement apart from the DISCONNECT statement that closes a cursor.
The cursor identifier can be given as a host variable if the cursor has been dynamically prepared.
The OPEN statement may appear anywhere in relation to the associated cursor statement in the host language.
DML statements must not be mixed with DDL/DCL statements in the same transaction.
Example
The following example opens a cursor.
OPEN cursor1 ;
The following example opens a dynamic cursor and provides values within host variables.
OPEN cursor1 USING :hv1, :hv2, :hv3 ;
