Declare a cursor for each select statement that returns multiple rows of data. You must declare the cursor before using it, and you cannot declare it within a declare section.
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.
The syntax for declaring a cursor is:
exec sql declare cursor_name cursor
for select_statement ;
where:
cursor_name identifies the cursor. The name must be unique and have a maximum of 255 characters. The name must begin with a letter of the alphabet or with the symbols “#” or “_”.
select_statement is a select statement that can return multiple rows of data. The syntax for select is the same as that shown in the Adaptive Server Enterprise Reference Manual, except that you cannot use into or compute clauses.
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.
