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 |
|
cursor_identifier |
A valid identifier of no more than 32 characters, and which has not previously been used as a cursor identifier within the same compilation unit. |
|
host_variable_identifier_1 |
A valid single host variable which is used to contain a unique dynamic cursor identifier. |
|
query_expression |
The specification of the resultant table associated with this cursor. |
|
statement_identifier |
A valid SQL identifier identifying a SELECT statement which has previously been prepared. |
|
host_variable_identifier_2 |
A valid single host variable. The value of the host variable must be the value returned by the PREPARE statement and thus identify the prepared statement. |
|
ORDER BY clause |
The specification of a user-defined ordering of the resultant table. Otherwise the resultant table is not ordered. |
|
FOR UPDATE clause |
The explicit indication that this cursor is to be used in conjunction with either an UPDATE and/or DELETE WHERE CURRENT OF cursor-id statement. |
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:
The specification of a UNION operator in a query expression is not allowed. Therefore, the expression must consist of only one query specification.
Derived columns in the derived column list must be based on base tables not views. No operators, functions or literals are allowed in the derived column list.
No column may be specified more than once in the derived column list of the query specification.
The specification of DISTINCT in the derived column list is not allowed.
A grouped or joined query specification is not allowed.
If a subquery is specified, it may not reference the same table as that one referenced in the outer query, i.e the table which would be the subject of any amendment statement.
If the query specification is derived from a view that view must be updatable.
An ORDER BY clause is not specified.
A FOR FETCH ONLY clause is not specified.
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 need not be specified. However, if the associated UPDATE or DELETE statement is actually in a separate compilation unit, as is possible with Adabas SQL Gateway 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 indeed 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 Adabas SQL Gateway 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, 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 need not 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 Adabas SQL Gateway Embedded SQL, since a dynamic DECLARE CURSOR statement is an executable statement.
Example
The following example declares a cursor to select all the cruise and start dates for every cruise that leaves BARBADOS.
DECLARE cursor1 CURSOR FOR
SELECT cruise_identifier,start_date FROM cruise
WHERE start_harbor = 'BARBADOS';
To declare a cursor to list all the start harbor's in ASCENDING alphabetical order and each related cruise id, for each cruise that costs less than 1000 the following syntax applies:
DECLARE cursor1 CURSOR FOR
SELECT cruise_identifier,start_harbor FROM cruise
WHERE cruise_price < 1000
ORDER BY 2 ASC;
To ensure that the cursor as declared in the first example can only be used for retrieval the following syntax applies:
DECLARE cursor1 CURSOR FOR
SELECT cruise_identifier,start_date FROM cruise
WHERE start_harbor = 'BARBADOS'
FOR FETCH ONLY;