The following example demonstrates declaring cursors:
exec sql declare c1 cursor for
select type, price from titles
where type like :wk-type;
In this example, c1 is declared as a cursor for the rows that will be returned for the type and price columns. The precompiler generates no code for the declare cursor statement. It simply stores the select statement associated with the cursor.
When the cursor opens, the select statement or procedure in the declare cursor statement executes. When the data is fetched, the results are copied to the host variables.
Each cursor’s open and declare statements
must be in the same file. Host variables used within the declare statement
must have the same scope as the one in which the open statement
is defined. However, once the cursor is open, you can perform fetch and update or delete
where current of on the cursor in any file.
The declare cursor statement is a declaration,
not an executable statement. Therefore, it may appear anywhere in
a file; SQLCODE, SQLSTATE, and SQLCA are not set after this statement.
