DECLARE CURSOR
Valid in: ESQL, OpenAPI
The DECLARE CURSOR statement associates a cursor name with a
SELECT statement (as described in SELECT (interactive)).
The DECLARE CURSOR statement has the following format:
EXEC SQL DECLARE cursor_name CURSOR
FOR SELECT [ALL | DISTINCT] result_expression {, result_expression}
FROM [schema.]table [correlation] {, [schema.]table [correlation]}
[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 [DEFERRED | DIRECT] 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 32 bytes.
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. Rows can be retrieved in any sequence if a scrollable cursor is declared.
4. Close the cursor by issuing the CLOSE statement.
You can use SELECT * in a cursor SELECT statement.
Last modified date: 01/30/2023