>>-DECLARE--+-cursor_id------------+---------------------------->
            |  (1)                 |
            '--------cursor_id_var-'
 
                                         (1)                                  (2)
>--+-CURSOR--+------------------+--+-FOR-------| Subset of INSERT Statement |-----------------+----+-><
   |         |  (1)             |  +-| Select Options |---------------------------------------+    |
   |         '--------WITH HOLD-'  |                             (3)                          |    |
   |                               '-FOR--+-| SELECT Statement |----------------------------+-'    |
   |                                      +-statement_id------------------------------------+      |
   |                                      |  (1)                                            |      |
   |                                      +--------+-statement_id_var---------------------+-+      |
   |                                      |        |                                 (4)  | |      |
   |                                      |        '-| EXECUTE PROCEDURE Statement |------' |      |
   |                                      |  (5)                                  (6)       |      |
   |                                      '--------| EXECUTE FUNCTION Statement |-----------'      |
   |  (1)                                                             (3)                          |
   +--------SCROLL CURSOR--+-----------+--FOR--+-| SELECT Statement |----------------------------+-+
   |                       '-WITH HOLD-'       +-statement_id------------------------------------+ |
   |                                           |  (1)                                            | |
   |                                           +--------+-statement_id_var---------------------+-+ |
   |                                           |        |                                 (4)  | | |
   |                                           |        '-| EXECUTE PROCEDURE Statement |------' | |
   |                                           |  (5)                                  (6)       | |
   |                                           '--------| EXECUTE FUNCTION Statement |-----------' |
   |  (5)                                                          (7)                             |
   '--------CURSOR FOR--+-| SELECT with Collection-Derived Table |------+--------------------------'
                        |                                          (8)  |
                        '-| INSERT with Collection-Derived Table |------'
 
Select Options:
 
                                        .-FOR READ ONLY-------------------------.
                                  (9)   |  (1)                                  |
|--| Subset of SELECT Statement |-------+--------FOR UPDATE--+----------------+-+--|
                                                             |     .-,------. |
                                                             |     V        | |
                                                             '-OF----column-+-'
 

1. Informix extension
2. See Subset of INSERT Statement Associated with a Sequential Cursor
3. See SELECT
4. See EXECUTE PROCEDURE
5. Dynamic Server only
6. See EXECUTE FUNCTION
7. See Select with a Collection-Derived Table
8. See Insert with a Collection-Derived Table
9. See Subset of SELECT Statement Associated with Cursors

A cursor is an identifier that you associate with 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.

• Using Dynamic Server with a collection variable in an ESQL/C program

  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 ESQL/C preprocessor to allocate storage for it. DECLARE must precede any other statement that refers to the cursor during program execution.

The maximum length of a DECLARE statement is 64 kilobytes. The number of cursors and prepared objects that can exist concurrently in a single program is limited by the available memory. To avoid exceeding the limit, use the FREE statement to release some prepared statements or cursors.

A 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. In a multiple-file program, if you want to limit the scope of cursor names to the files in which they are declared, you must preprocess all of the files with the -local command-line option.

Multiple cursors can be declared for the same prepared statement identifier. For example, the following ESQL/C 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 or dynamic statement identifiers and (for Dynamic Server only) 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 runtime.

Cursors are typically required for data manipulation language (DML) operations on more than one row of data (or on an ESQL/C collection variable). You can declare the following types of cursors with the DECLARE statement:

• A select cursor is a cursor associated with a SELECT statement.
• A function cursor is a cursor associated with an EXECUTE FUNCTION (or EXECUTE PROCEDURE) statement.
• An insert cursor is a cursor associated with an INSERT statement.

Sections that follow describe each of these cursor types. Cursors can also have sequential, scroll, and hold characteristics (but an insert cursor cannot be a scroll cursor). These characteristics determine the structure of the cursor; see Cursor Characteristics. In addition, a select or function cursor can specify read-only or update mode. For more information, see Select Cursor or Function Cursor.

A cursor that is associated with a statement identifier can be used with an INSERT, SELECT, EXECUTE FUNCTION (or EXECUTE PROCEDURE) statement that is prepared dynamically, and to use different statements with the same cursor at different times. In this case, the type of cursor depends on the statement that is prepared at the time the cursor is opened. (See Associating a Cursor with a Prepared Statement.)

When an SQL statement returns more than one group of values to an ESQL/C program, you must declare a cursor to save the multiple groups, or rows, of data and to access these rows one at a time. You must associate the following SQL statements with cursors:

• If you associate a SELECT statement with a cursor, the cursor is called a select cursor.

  A select cursor is a data structure that represents a specific location within the active set of rows that the SELECT statement retrieved.

• If you associate an EXECUTE FUNCTION (or EXECUTE PROCEDURE) statement with a cursor, the cursor is called a function cursor.

  The function cursor represents the columns or values that a user-defined function returns. Function cursors behave the same as select cursors that are enabled as update cursors.

In Extended Parallel Server, to create a function cursor, you must use the EXECUTE PROCEDURE statement. Extended Parallel Server does not support the EXECUTE FUNCTION statement.

In Dynamic Server, for backward compatibility, if an SPL function was created with the CREATE PROCEDURE statement, you can create a function cursor with the EXECUTE PROCEDURE statement. With external functions, you must use the EXECUTE FUNCTION statement.

When you associate a SELECT or EXECUTE FUNCTION (or EXECUTE PROCEDURE) statement with a cursor, the statement can include an INTO clause. However, if you prepare the SELECT or EXECUTE FUNCTION (or EXECUTE PROCEDURE) statement, you must omit the INTO clause in the PREPARE statement and use the INTO clause of the FETCH statement to retrieve the values from the collection cursor.

A select or function cursor can scan returned rows of data and to move data row by row into a set of receiving variables, as the following steps describe:

1. DECLARE

   Use DECLARE to define a cursor and associate it with a statement.

2. OPEN

   Use OPEN to open the cursor. The database server processes the query until it locates or constructs the first row of the active set.

3. FETCH

   Use FETCH to retrieve successive rows of data from the cursor.

4. CLOSE

   Use CLOSE to close the cursor when its active set is no longer needed.

5. FREE

   Use FREE to release the resources that are allocated for the cursor.