OPEN

Opens a cursor.

 

Usage

Embedded, Procedural.

Description

The cursor is opened and references a set of rows in accordance with the definition of the cursor. The result set is defined when the OPEN statement is executed, any inserts, updates or deletes occurring after the open will not be reflected in the result set. The cursor is placed ‘before’ the first row in the addressed set.

The descriptor-name is identified by a host variable or a literal.

See ALLOCATE CURSOR for a description of extended cursors.

If the cursor is declared for a dynamically prepared SELECT statement, the source form of which contains parameter markers, the OPEN statement must include a USING clause.

The first variable in the variable list or referenced in the descriptor area takes the place of the first parameter marker, the second variable takes the place of the second marker, and so on.

The number of variables provided in the USING clause must be equal to the number of parameter markers in the source statement, and the data types of the variables must be assignment-compatible with their usage in the source statement. See the Mimer SQL Programmer's Manual, Dynamic SQL, for a more detailed description of the use of dynamically prepared statements.

Open cursors can be closed using one of the statements CLOSE, COMMIT or ROLLBACK, except for cursors declared WITH HOLD which remain open after COMMIT.

An open cursor must be closed before it can be opened again, unless it is declared as REOPENABLE.

A cursor declared as REOPENABLE can have several open instances of a cursor. The state of the current instance is stored on a cursor stack when a new instance is opened. The state of the preceding cursor instance is restored when a cursor is closed, see CLOSE. In this context, the state of a cursor instance includes both the set of rows addressed by the cursor and the position of the cursor within the set.

Restrictions

SELECT access is required to the table(s) or view(s) addressed by the cursor.

EXECUTE access is required to the result set procedure addressed by the cursor.

In a procedural usage context, extended-cursor-name cannot be used to identify the cursor and neither of the USING options may be used.

Notes

A cursor must be declared with a DECLARE CURSOR statement or allocated with an ALLOCATE CURSOR statement before it may be opened.

All access rights that the current ident holds to the table(s) or view(s) addressed by the cursor are checked when the cursor is opened.

If SELECT access is lacking, the OPEN statement will fail.

If UPDATE or DELETE access is lacking, the cursor may be opened but any corresponding UPDATE CURRENT or DELETE CURRENT statements will fail.

Only cursors declared for dynamically prepared statements may be opened with a USING clause.

Example

DECLARE c_1 CURSOR FOR SELECT product, producer, format,

                             extract_date(release_date), price, item_id

                         FROM product_details

…

…

OPEN c_1;

LOOP

   FETCH c_1 INTO c1_row;

…

END LOOP;

CLOSE c_1;

 

For more information, see the Mimer SQL Programmer's Manual, Using Cursors.

Standard Compliance