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 a global
variable is referenced, the privileges held by the authorization ID
of the statement must include one of the following:
- 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
Group privileges are not considered because this statement
cannot be dynamically prepared.
Syntax
>>-OPEN--+-cursor-name----------------------------------------+-->
'-cursor-variable-name--+--------------------------+-'
| .----------------. |
| V | |
'-(----+------------+-+--)-'
'-expression-'
>--+-----------------------------------+-----------------------><
| .-,------------------. |
| V | |
+-USING----+-variable-------+-+-----+
| | (1) | |
| '-expression-----' |
'-USING DESCRIPTOR--descriptor-name-'
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.
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
the example below.)
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:
- 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;
DB2 Version 9.7 for Linux, UNIX, and Windows