The DECLARE CURSOR statement associates a query expression and hence a resultant table with a cursor identifier. The statement only defines the contents of the resultant table; it does not establish it.

Embedded Mode P Dynamic Mode Interactive Mode

A cursor can be declared either as static using a static DECLARE CURSOR statement or as dynamic using a dynamic DECLARE CURSOR statement.

A static DECLARE CURSOR statement associates a query expression and the definition of a resultant table with an SQL identifier, namely the cursor identifier. The DECLARE CURSOR statement is only a definition. The OPEN statement associated with this cursor establishes the resultant table at execution time.

Although the characteristics of the derived column list are completely defined, the actual number of rows returned is unknown until execution time. In other words the format of each row associated with the cursor is known but the number of rows established upon opening the cursor is not. This is in direct contrast to the SINGLE ROW SELECT where by definition only one row may be returned. The host program is, therefore, not in a position to receive all the data established upon opening and must sequentially execute associated FETCH statements in order to retrieve one row at a time. This is the classic DECLARE-OPEN-FETCH cycle. The cursor identifier can be thought of as a pointer into the resultant table identifying the row currently under consideration. In general, executing an associated FETCH statement advances the pointer by one row.

In addition to the OPEN and FETCH statements, other associated statements are positioned UPDATE, positioned DELETE, and CLOSE.

The query expression defines the resultant table associated with the cursor. In theory, the expression can be unlimited in complexity. Certain query expressions are considered to be "updatable," i.e., the positioned DELETE or UPDATE statements are valid for this cursor.

For a cursor to be updatable the following rules must be observed:

If the above conditions for a read-only cursor have been met, positioned UPDATE or DELETE statements will result in compilation errors.

A static cursor can be explicitly declared as being non-updatable by use of the FOR FETCH ONLY clause. In such a case, the use of positioned UPDATE or DELETE statements associated with the cursor is not allowed. Furthermore rows will not be locked once they are established, regardless of the default locking specification.

Alternatively, a static cursor can be declared as FOR UPDATE, as long as it is updatable of course. In such a case, rows will be locked regardless of the default locking specification. In general, this clause does not need to be specified. However, if the associated UPDATE or DELETE statement is actually in a separate compilation unit, as is possible with CONNX Embedded SQL, then this clause is required in order to avoid a runtime error.

If neither a FOR FETCH ONLY clause, nor a FOR UPDATE clause is specified, and there are no associated UPDATE or DELETE statements within the same compilation unit, then the resulting rows will or will not be locked according to the system default locking specification.

Similar behavior can be ensured for a dynamic cursor by appending the clause to the dynamic SELECT statement. A column specification list is optional and has no effect.

A dynamic DECLARE CURSOR statement associates a dynamically created and prepared SELECT statement with a cursor identifier. The prepared SELECT statement can be identified either by a hard-coded SQL identifier, or by a host variable containing the unique statement identification provided by the relevant PREPARE statement.

The dynamic DECLARE CURSOR statement thus associates this previously prepared SELECT statement with a cursor identifier. The cursor can be identified in the normal way, or by a host variable, which CONNX Embedded SQL fills with a unique cursor identifier.

The syntax elements host variable identifier 1, host variable identifier 2, and statement identifier are not valid within a static DECLARE CURSOR statement.

Within a dynamic DECLARE CURSOR statement, such host variables must be of data type character-string.

Any ORDER BY clause or FOR UPDATE clause is part of the prepared SELECT, and the use of these clauses is not valid within a dynamic cursor statement, but only in a static DECLARE CURSOR statement.

The use of the FOR UPDATE and FOR FETCH ONLY clauses.

The DECLARE CURSOR statement must precede any other associated statement in the source. All associated statements must be contained within one compilation unit.

The physical order of the associated statements within a compilation unit is irrelevant. The OPEN statement must be present in the same compilation unit as the DECLARE statement, although its relative position is irrelevant. Associated UPDATE, DELETE, FETCH, and CLOSE statements do not need be in the same compilation unit. However, such a program design is more error prone, as full compilation checks cannot be performed.

The physical position of any associated PREPARE statement, relative to the dynamic DECLARE CURSOR statement, is irrelevant.

The function of a dynamic DECLARE CURSOR statement can also be accomplished by an extended OPEN statement. This saves one request to CONNX Embedded SQL, since a dynamic DECLARE CURSOR statement is an executable statement.

The following example declares a cursor to select all the cruise and start dates for every cruise that leaves BARBADOS:

To declare a cursor to list all the start harbors in ASCENDING alphabetical order, and each related cruise id, for each cruise that costs less than 1000, the following syntax applies:

To ensure that the cursor as declared in the first example can only be used for retrieval the following syntax applies: