OPEN statement

The OPEN statement opens a cursor so that it can be used to fetch rows from its result table.

Invocation

Although an interactive SQL facility might provide an interface that gives the appearance of interactive execution, this statement can only be embedded within an application program. It is an executable statement that cannot be dynamically prepared. When invoked using the command line processor, some options cannot be specified.

For more information, refer to Using command line SQL statements and XQuery statements .

Authorization

If the cursor-variable-name references a global variable, then the privileges held by the authorization ID of the statement must include one of the following authorities:

READ privilege on the global variable that is not defined in a module
EXECUTE privilege on the module of the global variable that is defined in a module
EXECUTEIN privilege on the schema containing the module of the global variable that is defined in a module
DATAACCESS authority on the schema containing the module of the global variable that is defined in a module

Group privileges are not considered because this statement cannot be dynamically prepared.

Syntax

Notes:

¹ An expression other than a variable can only be used in compiled compound statements.

Description

cursor-name

Names a cursor that is defined in a DECLARE CURSOR statement that was stated earlier in the program. If cursor-name identifies a cursor in an SQL procedure declared as WITH RETURN TO CLIENT that is already in the open state, the existing open cursor becomes a result set cursor that is no longer accessible using cursor-name and a new cursor is opened that becomes accessible using cursor-name. Otherwise, when the OPEN statement is executed, the cursor identified by cursor-name must be in the closed state.

The DECLARE CURSOR statement must identify a SELECT statement, in one of the following ways:

Including the SELECT statement in the DECLARE CURSOR statement
Including a statement-name that names a prepared SELECT statement.

The result table of the cursor is derived by evaluating the SELECT statement. The evaluation uses the current values of any special registers, global variables, or PREVIOUS VALUE expressions specified in the SELECT statement, and the current values of any host variables specified in the SELECT statement or the USING clause of the OPEN statement. The rows of the result table may be derived during the execution of the OPEN statement, and a temporary table may be created to hold them; or they may be derived during the execution of subsequent FETCH statements. In either case, the cursor is placed in the open state and positioned before the first row of its result table. If the table is empty, the state of the cursor is effectively after the last row.

cursor-variable-name

Names a cursor variable. The value of the cursor variable must not be null (SQLSTATE 34000). A cursor variable that is directly or indirectly assigned a cursor value constructor can be used only in an OPEN statement that is in the same scope as the assignment (SQLSTATE 51044). If the cursor value constructor assigned to the cursor variable specified a statement-name, the OPEN statement must be in the same scope where that statement-name was explicitly or implicitly declared (SQLSTATE 51044).

When the OPEN statement is executed, the underlying cursor of the cursor variable must be in the closed state. The result table of the underlying cursor is derived by evaluating the SELECT statement or dynamic statement associated with the cursor variable. The evaluation uses the current values of any special registers, global variables, or PREVIOUS VALUE expressions specified in the SELECT statement, and the current values of any variables specified in the SELECT statement or the USING clause of the OPEN statement. The rows of the result table may be derived during the execution of the OPEN statement, and a temporary table may be created to hold them; or they may be derived during the execution of subsequent FETCH statements. In either case, the cursor is placed in the open state and positioned before the first row of its result table. If the table is empty, the state of the cursor is effectively after the last row.

An OPEN statement using a cursor-variable-name can only be used within a compound SQL (compiled) statement.

expression, )

Specifies the arguments associated with the named parameters of a parameterized cursor variable. The cursor-value-constructor assigned to the cursor variable must include a list of parameters with the same number of parameters as the number of arguments specified (SQLSTATE 07006 or 07004). The data type and value of the nth expression must be assignable to the nth parameter (SQLSTATE 07006 or 22018).

USING

Introduces the values that are substituted for the parameter markers or variables in the statement of the cursor. For an explanation of parameter markers, see PREPARE.

If a statement-name is specified in the DECLARE CURSOR statement or the cursor value constructor associated with the cursor variable that includes parameter markers, USING must be used. If the prepared statement does not include parameter markers, USING is ignored.

If a select-statement is specified in the DECLARE CURSOR statement or the non-parameterized cursor value constructor associated with the cursor variable, USING may be used to override the variable values.

variable

Identifies a variable or a host structure declared in the program in accordance with the rules for declaring variables and host variables. The number of variables must be the same as the number of parameter markers in the prepared statement. The nth variable corresponds to the nth parameter marker in the prepared statement. Where appropriate, locator variables and file reference variables can be provided as the source of values for parameter markers.

expression

Specifies values to associate with parameter markers using expressions. An OPEN statement that specifies expressions in the USING clause can only be used within a compound SQL (compiled) statement (SQLSTATE 42601). The number of expressions must be the same as the number of parameter markers in the prepared statement (SQLSTATE 07001). The nth expression corresponds to the nth parameter marker in the prepared statement. The data type and value of the nth expression must be assignable to the type associated with the nth parameter marker (SQLSTATE 07006).

Rules

When the SELECT statement of the cursor is evaluated, each parameter marker in the statement is effectively replaced by its corresponding host variable. For a typed parameter marker, the attributes of the target variable are those specified by the CAST specification. For an untyped parameter marker, the attributes of the target variable are determined according to the context of the parameter marker.
Let V denote a host variable that corresponds to parameter marker P. The value of V is assigned to the target variable for P in accordance with the rules for assigning a value to a column. Thus:
- V must be compatible with the target.
- If V is a string, its length (excluding trailing blanks for strings that are not long strings) must not be greater than the length attribute of the target.
- If V is a number, the absolute value of its integral part must not be greater than the maximum absolute value of the integral part of the target.
- If the attributes of V are not identical to the attributes of the target, the value is converted to conform to the attributes of the target.
When the SELECT statement of the cursor is evaluated, the value used in place of P is the value of the target variable for P. For example, if V is CHAR(6), and the target is CHAR(8), the value used in place of P is the value of V padded with two blanks.
The USING clause is intended for a prepared SELECT statement that contains parameter markers. However, it can also be used when the SELECT statement of the cursor is part of the DECLARE CURSOR statement or the non-parameterized cursor value constructor associated with the cursor variable. In this case the OPEN statement is executed as if each host variable in the SELECT statement were a parameter marker, except that the attributes of the target variables are the same as the attributes of the host variables in the SELECT statement. The effect is to override the values of the host variables in the SELECT statement of the cursor with the values of the host variables specified in the USING clause. A variable value override must not be used when opening a parameterized cursor variable since the SELECT statement will not include any other variables.
SQL data change statements and routines that modify SQL data embedded in the cursor definition are completely executed, and the result set is stored in a temporary table when the cursor opens. If statement execution is successful, the SQLERRD(3) field contains the sum of the number of rows that qualified for insert, update, and delete operations. If an error occurs during execution of an OPEN statement involving a cursor that contains a data change statement within a fullselect, the results of that data change statement are rolled back.
Explicit rollback of an OPEN statement, or rollback to a savepoint before an OPEN statement, closes the cursor. If the cursor definition contains a data change statement within the FROM clause of a fullselect, the results of the data change statement are rolled back.

Changes to rows in a table that is targeted by a data change statement nested within a SELECT statement or a SELECT INTO statement are processed when the cursor opens, and are not undone if an error occurs during a fetch operation against that cursor.

Notes

Closed state of cursors: All cursors in a program are in the closed state when the program is initiated and when it initiates a ROLLBACK statement.
All cursors, except open cursors declared WITH HOLD, are in a closed state when a program issues a COMMIT statement.

A cursor can also be in the closed state because a CLOSE statement was executed or an error was detected that made the position of the cursor unpredictable.

The underlying cursor of a cursor variable is closed if the cursor variable goes out of scope and there are no other cursor variables that referenced that underlying cursor.
To retrieve rows from the result table of a cursor, execute a FETCH statement when the cursor is open. The only way to change the state of a cursor from closed to open is to execute an OPEN statement.
Effect of materalized result tables: In some cases, such as when the cursor is not read only, the result rows of a cursor are derived during the execution of FETCH statements. In other cases, the materialized result table method is used instead. With the materialized result table method the entire result table is transferred to a temporary buffer during the execution of the OPEN statement. When a temporary buffer is used, the results of a program can differ in these ways:
- An error can occur during OPEN that would otherwise not occur until some later FETCH statement.
- INSERT, UPDATE, and DELETE statements executed in the same transaction while the cursor is open cannot affect the result table.
- Any NEXT VALUE expressions in the SELECT statement are evaluated for every row of the result table during OPEN.
Conversely, if a temporary buffer is not used, INSERT, UPDATE, and DELETE statements executed while the cursor is open can affect the result table if issued from the same unit of work, and any NEXT VALUE expressions in the SELECT statement are evaluated as each row is fetched. This result table can also be affected by operations executed by the same unit of work, and the effect of such operations is not always predictable. For example, if cursor C is positioned on a row of its result table defined as SELECT * FROM T, and a new row is inserted into T, the effect of that insert on the result table is not predictable because its rows are not ordered. Thus a subsequent FETCH C may or may not retrieve the new row of T.
Statement caching affects cursors declared open by the OPEN statement.
Opening the same cursor multiple times: A cursor in an SQL procedure declared as WITH RETURN TO CLIENT can be opened even when a cursor with the same name is already in the open state. In this case, the existing open cursor becomes a result set cursor and is no longer accessible by its cursor name. A new cursor is opened and becomes accessible by the cursor name. Closing the new cursor does not make the cursor that was previously accessible by that name accessible by the cursor name again. The cursors that become result set cursors in this way cannot be accessed at the server and can be processed only at the client.
When an SQL procedure, or an external stored procedure that uses non-blocking cursors, opens a cursor with return but does not fetch any rows from it, the access plan of the cursor query might not be evaluated. The query is evaluated when a row is fetched from the procedure's result set by the caller or client.

Examples

Example 1: Write the embedded statements in a COBOL program that will:

Define a cursor C1 that is to be used to retrieve all rows from the DEPARTMENT table for departments that are administered by (ADMRDEPT) department 'A00'.

Place the cursor C1 before the first row to be fetched.

   EXEC SQL  DECLARE C1 CURSOR FOR
                  SELECT DEPTNO, DEPTNAME, MGRNO
                    FROM DEPARTMENT
                    WHERE ADMRDEPT = 'A00'
   END-EXEC.


   EXEC SQL  OPEN C1
   END-EXEC.

Example 2: Code an OPEN statement to associate a cursor DYN_CURSOR with a dynamically defined select-statement in a C program. Assuming two parameter markers are used in the predicate of the select-statement, two host variable references are supplied with the OPEN statement to pass integer and varchar(64) values between the application and the database. (The related host variable definitions, PREPARE statement, and DECLARE CURSOR statement are also shown in this example.)

   EXEC SQL  BEGIN DECLARE SECTION;
     static short    hv_int;
     char            hv_vchar64[65];
     char            stmt1_str[200];
   EXEC SQL  END DECLARE SECTION;

   EXEC SQL  PREPARE STMT1_NAME FROM :stmt1_str;
   EXEC SQL  DECLARE DYN_CURSOR CURSOR FOR STMT1_NAME;

   EXEC SQL  OPEN DYN_CURSOR USING :hv_int, :hv_vchar64;

Example 3: Code an OPEN statement as in example 2, but in this case the number and data types of the parameter markers in the WHERE clause are not known.

   EXEC SQL  BEGIN DECLARE SECTION;
     char    stmt1_str[200];
   EXEC SQL  END DECLARE SECTION;
   EXEC SQL  INCLUDE SQLDA;

   EXEC SQL  PREPARE STMT1_NAME FROM :stmt1_str;
   EXEC SQL  DECLARE DYN_CURSOR CURSOR FOR STMT1_NAME;

   EXEC SQL  OPEN DYN_CURSOR USING DESCRIPTOR :sqlda;

Example 4: Create a procedure that does the following operations:

Assigns a cursor to the output cursor variable
Opens the cursor

CREATE PROCEDURE PROC1 (OUT P1 CURSOR)LANGUAGE SQL
BEGIN
SET P1=CURSOR FOR SELECT DEPTNO, DEPTNAME, MGRNO FROM DEPARTMENT WHERE ADMRDEPT='A00'; --
OPEN P1; --
END;