DECLARE CURSOR
Valid in: ESQL, OpenAPI
The DECLARE CURSOR statement associates a cursor name with a SELECT statement.
The DECLARE CURSOR statement has the following format:
EXEC SQL DECLARE cursor_name CURSOR
FOR SELECT [ALL | DISTINCT] result_expression {, result_expression}
FROM table {table}
[WHERE search_condition]
[GROUP BY column {, column}]
[HAVING search_condition]
[UNION [all] full_select]
[ORDER BY ordering-expression [ASC | DESC]
{, ordering-expression [ASC | DESC]}]
[FOR UPDATE OF column {, column}]
Dynamic SQL form:
EXEC SQL DECLARE cursor_name CURSOR
FOR statement_name;
cursor_name
Assigns a name to the cursor. The name can be specified using a quoted or unquoted string literal or a host language string variable. If cursor_name is a reserved word, it must be specified in quotes.
Limits: The cursor name cannot exceed 24 characters.
DECLARE CURSOR is a compile-time statement and must appear before the first statement that references the cursor. Despite its declarative nature, a DECLARE CURSOR statement must not be located in a host language variable declaration section. A cursor cannot be declared for repeated select.
A typical cursor-based program performs the following steps:
1. Issue a DECLARE CURSOR statement to associate a cursor with a SELECT statement.
2. Open the cursor. When the cursor is opened, the DBMS Server executes the SELECT statement that was specified in the DECLARE CURSOR statement.
3. Process rows one at a time. The FETCH statement returns one row from the results of the SELECT statement that was executed when the cursor was opened.
4. Close the cursor by issuing the CLOSE statement.
A source file can have multiple cursors, but the same cursor cannot be declared twice. If you want to declare several cursors using the same host language variable to represent cursor_name, it is only necessary to declare the cursor once, since declare cursor is a compile‑time statement. Multiple declarations of the same cursor_name will cause a preprocessor error, even if its actual value is to be changed between declarations. For example, the following statements cause a preprocessor error:
exec sql declare :cname[i] cursor for s1;
i = i + 1
/* The following statement causes preprocessor error */
exec sql declare :cname[i] cursor for s2;
Instead, declare the cursor once. The value assigned to the host language variable cursor_name is not determined until the open cursor statement is executed. For example:
exec sql declare :cname[i] cursor for :sname[i];
loop incrementing i
exec sql open :cname[i];
end loop;
If a cursor is declared using a host language variable, all subsequent references to that cursor must use the same host language variable. At runtime, a dynamically specified cursor name, that is, a cursor declared using a variable, must be unique among all dynamically specified cursor names in an application. In a similar manner, any cursors referenced in a dynamic statement, for example a dynamic UPDATE or DELETE CURSOR statement, must be unique among all open cursors within the current transaction.
A cursor name declared in one source file cannot be referred to in another file, since the scope of a cursor declaration is the source file. If the cursor is redeclared in another file with the same associated query, it will still not identify the same cursor, not even at runtime. For example, if a cursor c1 is declared in source file, file1, then all references to c1 must be made within file1. Failure to follow this rule results in runtime errors. For example, if you declare cursor c1 in an include file, open it in one file and fetch from it in another file, at runtime the DBMS returns an error indicating that the cursor c1 is not open on the fetch.
This rule applies equally to dynamically specified cursor names. If a dynamic UPDATE or DELETE CURSOR statement is executed, the cursor referenced in the statement must be declared in the same file in which the update or delete statement appears.
The embedded SQL preprocessor does not generate any code for the DECLARE CURSOR statement. Therefore, in a language that does not allow empty control blocks, (for example, COBOL, which does not allow empty IF blocks), the DECLARE CURSOR statement should not be the only statement in the block.
The FOR UPDATE clause must be included if there is any possibility that the cursor will be used to update rows. List any column that might be updated. If you only intend to delete rows, then the FOR UPDATE clause is not required. The actual updating or deleting takes place with the cursor version of the UPDATE or DELETE statement, respectively.
A cursor cannot be declared for updating if its SELECT statement:
• Refers to more than one table.
For example, the following cursor declaration causes a compile-time error, and is illegal because two tables are used in the SELECT statement:
/* illegal join on different tables for update */
exec sql declare c1 cursor for
select employee.id, accounts.sal
from employee, accounts
where employee.salno = accounts.accno
for update of sal;
• Refers to a non-updatable view.
In the following example, if empdept is a read-only view, the following code generates a runtime error when the OPEN statement is executed. No preprocessor error is generated, because the preprocessor does not know that empdept is a view.
/* empdept is a read-only view */
exec sql declare c2 cursor for
select name, deptinfo
from empdept
for update of deptinfo;
exec sql open c2;
• Includes a DISTINCT, GROUP BY, HAVING, ORDER BY, or UNION clause.
• Includes a column that is a constant or is based on a calculation.
For example, the following cursor declaration causes an error when attempting to update the column named constant:
/* "constant" cannot be declared for update */
exec sql declare c3 cursor for
select constant = 123, ename
from employee
for update of constant;
If an updatable column has been assigned a result column name using the syntax:
column_name AS result_name
the column referred to in the FOR UPDATE list must see the table column name, and not the result column name.
Updates associated with a cursor take effect on the underlying table when the statement is executed. The effects of the updates can be seen by the program before the cursor is closed. The actual committal of the changes does not override or interfere with COMMIT or ROLLBACK statements that may be executed subsequently in the program. Because changes take effect immediately, avoid updating keys that cause the current row to move “forward” with respect to the current position of the cursor, because this may result in fetching the same row more than once.
If the FOR UPDATE clause is specified, the cursor can still be opened for reading only. The OPEN statement accepts the optional FOR READONLY clause, which specifies that, though the cursor may have been declared for update, the cursor is not being opened for update. Including this clause in the OPEN statement can improve the performance of the cursor retrieval.
Not all database management systems allow the use of a cursor to update a row more than once. For details, see your Enterprise Access product and DBMS-specific documentation for details.
The UNION form of the SELECT statement can be used in a cursor declaration. To select all columns, use SELECT *. Each column does not need to be listed individually.
When the ORDER BY clause is specified, the ordering is performed according to SQL comparison rules. Each column specified in the ordering must specify either a column name, which identifies a column of the result table, or an integer, which identifies a numbered column of the result table. A named result column can be identified by an ordering name or a number. An unnamed result column must be identified by an ordering number.
Host language variables can be used in the SELECT statement of a declare cursor to substitute for expressions in the SELECT clause or in the search condition. When the search condition is specified within a single string variable (as when the query is constructed using the form system query mode) then all the following clauses, such as the ORDER BY or UPDATE clause, can be included within the variable. These variables must be valid at the time of the cursor’s open statement, because that is when the select is actually evaluated—they need not have defined values at the time of the DECLARE CURSOR statement. Host language variables cannot substitute for any table or column names.
You can also use the dynamic OpenSQL syntax and specify a prepared statement name instead of a SELECT statement. The statement name must identify a SELECT statement that has been prepared previously. The statement name must not be the same as another prepared statement name that is associated with a currently open cursor.