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.



1 2  %Subset of SELECT Statement6
2.1 1 FOR UPDATE?  OF + , column
Other Select or Function Options

1  FOR
2.1 2 %SELECT Statement7
2.1 statement_id
2.1 12
2.2.1 statement_id_var
2.2.1  %EXECUTE PROCEDURE Statement8
2.1  2 %EXECUTE
FUNCTION Statement9
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


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.