DECLARE statement

Use the DECLARE statement of dynamic SQL to declare a cursor and to associate it with an SQL statement that returns a set of rows to an SPL or or routine.

Syntax

FOR UPDATE or FOR READ ONLY Select Options

Other Select or Function Options

Notes:

¹ HCL OneDB™ extension
² ESQL/C only
³ See Subset of INSERT Statement with a Sequential Cursor
⁴ See Select with a Collection-Derived Table
⁵ See Insert with a Collection-Derived Table
⁶ See Subset of SELECT statements associated with cursors
⁷ See SELECT statement
⁸ See EXECUTE PROCEDURE statement
⁹ See EXECUTE FUNCTION statement


Element	Description	Restrictions	Syntax
column	Column to update with cursor	Must exist, but need not be listed in Select list of the Projection clause	Identifier
cursor_id	Name declared here for cursor	Must be unique in the routine among names of cursors and prepared objects (and in SPL, among variables)	Identifier
cursor_id_var	Variable holding cursor_id	Must have a character data type	Language-specific
statement_id	Name of prepared statement	Must have been declared by a PREPARE statement	Identifier
statement_id_var	Variable holding statement_id	Must have a character data type	Language-specific

Usage

Except as noted, sections that follow describe how to use the DECLARE statement in routines. For information about the more restricted syntax and semantics of the DECLARE statement in SPL routines, see Declaring a Dynamic Cursor in an SPL Routine.

A cursor is an identifier that you associate with an SQL statement that returns a group of rows. The DECLARE statement associates the cursor with one of the following database objects:

With an SQL statement, such as SELECT, EXECUTE FUNCTION (or EXECUTE PROCEDURE), or INSERT.
Each of these SQL statements creates a different type of cursor. For more information, see Overview of Cursor Types.
With the statement identifier (statement id or statement id variable) of a prepared statement
You can prepare one of the previous SQL statements and associate the prepared statement with a cursor. For more information, see Associating a Cursor with a Prepared Statement.
With a collection variable in programs
The name of the collection variable appears in the FROM clause of a SELECT or the INTO clause of an INSERT. For more information, see Associating a Cursor with a Prepared Statement.

DECLARE assigns an identifier to the cursor, specifies its uses, and directs the preprocessor to allocate storage for it. DECLARE must precede any other statement that references the cursor during program execution.

The cursors and prepared objects that can exist concurrently in a single program are limited by available memory. To avoid exceeding the limit, use the FREE statement to release the resources of prepared statements or cursors that are no longer needed.

An ESQL/C program can consist of one or more source-code files. By default, the scope of reference of a cursor is global to a program, so a cursor that was declared in one source file can be referenced from a statement in another file. If you want to limit the scope of each cursor name to the file where it was declared, you must preprocess each file with the -local command-line option.

Multiple cursors can be declared for the same prepared statement identifier. For example, the following example does not return an error:

EXEC SQL prepare id1 from 'select * from customer';
EXEC SQL declare x cursor for id1;
EXEC SQL declare y scroll cursor for id1;
EXEC SQL declare z cursor with hold for id1;

If you include the -ansi compilation flag (or if DBANSIWARN is set), warnings are generated for statements that use dynamic cursor names, dynamic statement identifiers, or statements that use collection-derived tables. Some error checking is performed at runtime, such as these typical checks:

Invalid use of sequential cursors as scroll cursors
Use of undeclared cursors
Invalid cursor names or statement names (empty)

Checks for multiple declarations of a cursor of the same name are performed at compile time only if the cursor or statement is specified as an identifier. The following example uses a host variable to store the cursor name:

EXEC SQL declare x cursor for select * from customer;
. . .
stcopy("x", s);
EXEC SQL declare :s cursor for select * from customer;

A cursor uses the collating order that was in effect when the cursor was declared, even if this is different from the collation of the session at run time.