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:

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:

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:

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:

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.