Declare Cursor

Valid in: ESQL

The Declare Cursor statement associates a cursor name with a select statement. For information about the select statement, see Select (interactive). 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.

You can use select * in a cursor select statement.

Syntax

The Declare Cursor statement has the following format:

EXEC SQL DECLARE cursor_name CURSOR [[NO] SCROLL]
              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}]

• cursor_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 characters.

Dynamic SQL form:

EXEC SQL DECLARE cursor_name CURSOR
              FOR statement_name;