OPEN - Open cursor

You use OPEN to open a cursor declared with DECLARE CURSOR .

• The host variables in the cursor description or the values for placeholders in a dynamic cursor description are evaluated.

• The special literals (see "Special literals"), as well as the time functions CURRENT_DATE, CURRENT_TIME and CURRENT_TIMESTAMP are evaluated.

  All the values returned contain the same date and/or time (see section "Time functions" ). These values are valid for the cursor table as long as the cursor is open and if the cursor is reopened with RESTORE.

After the OPEN statement, the cursor is positioned before the first row in the derived table, even if the previous cursor position was saved with STORE. A previously saved cursor position cannot be restored with RESTORE after an OPEN statement.

A cursor can only be addressed in the compilation unit in which it was declared with DECLARE CURSOR. The cursor declaration with DECLARE CURSOR must physically precede the OPEN statement in the program text.

In the case of a dynamic cursor, the cursor must be prepared before the OPEN statement is executed.

The cursor must be closed.

---

OPEN cursor

     [USING { variable [, variable ]... | SQL DESCRIPTOR GLOBAL descriptor }]

variable ::= : host_variable [[INDICATOR] : indicator_variable ]

---

cursor

Name of the cursor to be opened.

USING clause

For a dynamic cursor.

Specifies where the input values for the dynamic cursor description are to be read from. You must specify the USING clause if the cursor description includes question marks as placeholders for values.

host_variable

Name of a host variable containing the value to be assigned to a placeholder in the dynamic cursor description.

The data type of a host variable must be compatible with the data type of the corresponding placeholder (see section "Values for placeholders"). If the placeholder represents an aggregate with several elements, the corresponding host variable must be a vector with the same number of elements.

The number of host variables specified must be the same as the number of placeholders in the cursor description. The host variables are assigned values in the order in which the placeholders are specified in the dynamic cursor description.

indicator_variable

Name of the indicator variable for the preceding host variable. If the host variable is a vector, the indicator variable must also be a vector with the same number of elements.

The value of the indicator variable indicates whether the NULL value is to be transferred:

< 0 >= 0

The NULL value is to be assigned. The value of the host variable is to be assigned.

descriptor

Name of an SQL descriptor area containing the data types and values for the placeholders in the dynamic cursor description.

The SQL descriptor area must be created beforehand and supplied with appropriate values:

• • • • The value of the COUNT descriptor area feld must be the same as the number of required input values (for aggregates, one output value for each element) where

        0 <= COUNT <= defined maximum number of item descriptors

      • The values of the DATA fields of the item descriptors (or NULL values if the INDICATOR is negative) are assigned to the placeholders in the dynamic statement in the order of the items in the descriptor area. The data type description of an item must be compatible with the data type of the corresponding placeholder (see section "Values for placeholders" "Values for placeholders").

Example

Open a cursor CUR_CONTACTS. The cursor defines a section of the CONTACTS table containing the LNAME, FNAME and DEPARTMENT for all customers with customer numbers greater than 103.

DECLARE cur_contacts CURSOR FOR SELECT lname, fname, department FROM contacts WHERE cust_num > 103 ORDER BY department ASC, lname DESC OPEN cur_contacts

See also

CLOSE, DECLARE CURSOR, FETCH, PREPARE