DB2 10.5 for Linux, UNIX, and Windows

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 a global variable is referenced, 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

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

Syntax

Read syntax diagramSkip visual syntax diagram
>>-OPEN--+-cursor-name----------------------------------------+-->
         '-cursor-variable-name--+--------------------------+-'   
                                 |    .----------------.    |     
                                 |    V                |    |     
                                 '-(----+------------+-+--)-'     
                                        '-expression-'            

>--+-----------------------------------+-----------------------><
   |        .-,------------------.     |   
   |        V                    |     |   
   +-USING----+-variable-------+-+-----+   
   |          |            (1) |       |   
   |          '-expression-----'       |   
   '-USING DESCRIPTOR--descriptor-name-'   

Notes:
  1. 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

Notes

Examples

Example 1:  Write the embedded statements in a COBOL program that will:
  1. 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'.
  2. 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:
  1. Assigns a cursor to the output cursor variable
  2. 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;