Informix Guide to SQL: Syntax SQL Statements

Use the DECLARE statement to associate a cursor with a group of rows.

Use this statement with ESQL/C.

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, page 2-352.

• 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 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 Collection Variable.

The DECLARE statement assigns an identifier to the cursor, specifies its uses, and directs the preprocessor to allocate storage to hold the cursor.

The DECLARE statement must precede any other statement that refers to the cursor during the execution of the program.

The maximum length of a DECLARE statement is 64 kilobytes.

The number of prepared items in a single program is limited by the available memory. These items include both statement identifiers that are named in PREPARE statements (statement_id or statement_id_var) and declared cursors. To avoid exceeding the limit, use a FREE statement to release some statements or cursors.

A program can consist of one or more source-code files. By default, the scope of a cursor is global to a program, so a cursor declared in one file can be referenced from another file. In a multiple-file program, if you want to limit the scope of cursors to the files in which they are declared, you must preprocess all the files with the -local command-line option.

To declare multiple cursors, use a single statement identifier. For instance, 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 identifier names and statements that use derived table names. Some error checking is performed at runtime. The following list indicates the typical checks:

• Illegal use of cursors (that is, normal cursors used as scroll cursors)
• Use of undeclared cursors
• Bad cursor 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 an identifier. The following example uses a host variable to hold the cursor name.

EXEC SQL declare x cursor for

	select * from customer;

. . .

stcopy("x", s);

EXEC SQL declare :s cursor for 

	select * from customer;

Functionally, you can declare the following types of cursors with the DECLARE statement:

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

Any of these cursor types can have cursor characteristics: sequential, scroll, and hold. These characteristics determine the structure of the cursor. For more information, see Cursor Characteristics. In addition, a select or function cursor can have a cursor mode: read-only or update. For more information, see Select Cursor or Function Cursor.

The following table summarizes types of cursors that are available.

Cursor Type	Cursor Mode		Cursor Characteristic
	Read-Only	Update	Sequential	Scroll	Hold
Select and Function
Insert

A cursor can also be associated with a statement identifier, enabling you to use a cursor 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. For more information, see Associating a Cursor With a Prepared Statement.

The following sections describe each of these cursor types.

Tip: Cursors for functions behave the same as select cursors that are enabled as update cursors.

Select Cursor or Function Cursor

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:

• When 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.

• When 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, which are enabled as update cursors.

In Enterprise Decision Server, to create a function cursor, you must use the EXECUTE PROCEDURE statement. Enterprise Decision 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 enables you to 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 a DECLARE statement to define a cursor and associate it with a statement.

2. OPEN

Use the OPEN statement 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 the FETCH statement to retrieve successive rows of data from the cursor.

4. CLOSE

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

5. FREE

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

Use the FOR READ ONLY keywords to define a cursor as a read-only cursor. A cursor declared to be read-only cannot be used to update (or delete) any row that it fetches.

The need for the FOR READ ONLY keywords depends on whether your database is an ANSI-compliant database or a database that is not ANSI compliant.

In a database that is not ANSI compliant, the cursor that the DECLARE statement defines is a read-only cursor by default. So you do not need to specify the FOR READ ONLY keywords if you want the cursor to be a read-only cursor. The only advantage of specifying the FOR READ ONLY keywords explicitly is for better program documentation.

In an ANSI-compliant database, the cursor associated with a SELECT statement through the DECLARE statement is an update cursor by default, provided that the SELECT statement conforms to all of the restrictions for update cursors listed in Subset of SELECT Statement Associated with Cursors. If you want a select cursor to be read only, you must use the FOR READ ONLY keywords when you declare the cursor.

The database server can use less stringent locking for a read-only cursor than for an update cursor.

The following example creates a read-only cursor:

EXEC SQL declare z_curs cursor for

	select * from customer_ansi

	for read only;

Use the FOR UPDATE option to declare an update cursor. You can use the update cursor to modify (update or delete) the current row.

In an ANSI-compliant database, you can use a select cursor to update or delete data as long as the cursor was not declared with the FOR READ ONLY keywords and it follows the restrictions on update cursors that are described in Subset of SELECT Statement Associated with Cursors. You do not need to use the FOR UPDATE keywords when you declare the cursor.

The following example declares an update cursor:

EXEC SQL declare new_curs cursor for

	select * from customer_notansi

	for update;

In an update cursor, you can update or delete rows in the active set. After you create an update cursor, you can update or delete the currently selected row by using an UPDATE or DELETE statement with the WHERE CURRENT OF clause. The words CURRENT OF refer to the row that was most recently fetched; they take the place of the usual test expressions in the WHERE clause.

An update cursor lets you perform updates that are not possible with the UPDATE statement because the decision to update and the values of the new data items can be based on the original contents of the row. Your program can evaluate or manipulate the selected data before it decides whether to update. The UPDATE statement cannot interrogate the table that is being updated.

You can specify particular columns that can be updated.

When you declare an update cursor, you can limit the update to specific columns by including the OF keyword and a list of columns. You can modify only those named columns in subsequent UPDATE statements. The columns need not be in the select list of the SELECT clause.

The following example declares an update cursor and specifies that this cursor can update only the fname and lname columns in the customer_notansi table:

EXEC SQL declare name_curs cursor for

	select * from customer_notansi

	for update of fname, lname;

By default, a select cursor in a database that is ANSI compliant is an update cursor. Therefore, the FOR UPDATE keywords are optional. However, if you want an update cursor to be able to modify only some of the columns in a table, you must specify these columns in the FOR UPDATE option.

The principal advantage to specifying columns is documentation and preventing programming errors. (The database server refuses to update any other columns.) An additional advantage is speed, when the SELECT statement meets the following criteria:

• The SELECT statement can be processed using an index.
• The columns that are listed are not part of the index that is used to process the SELECT statement.

If the columns that you intend to update are part of the index that is used to process the SELECT statement, the database server must keep a list of each row that is updated to ensure that no row is updated twice. When you use the OF keyword to specify the columns that can be updated, the database server determines whether to keep the list of updated rows. If the database server determines that the list is unnecessary, then eliminating the work of keeping the list results in a performance benefit. If you do not use the OF keyword, the database server keeps the list of updated rows, although it might be unnecessary.

The following example contains ESQL/C code that uses an update cursor with a DELETE statement to delete the current row. Whenever the row is deleted, the cursor remains between rows. After you delete data, you must use a FETCH statement to advance the cursor to the next row before you can refer to the cursor in a DELETE or UPDATE statement.

EXEC SQL declare q_curs cursor for
select * from customer where lname matches :last_name
for update;

EXEC SQL open q_curs;
for (;;)
{
EXEC SQL fetch q_curs into :cust_rec;
if (strncmp(SQLSTATE, "00", 2) != 0)
break;

/* Display customer values and prompt for answer */
printf("\n%s %s", cust_rec.fname, cust_rec.lname);
printf("\nDelete this customer? ");
scanf("%s", answer);

if (answer[0] == 'y')
EXEC SQL delete from customer where current of q_curs;
if (strncmp(SQLSTATE, "00", 2) != 0)
break;
}
printf("\n");
EXEC SQL close q_curs;

When you use the FOR UPDATE keywords you notify the database server that updating is possible and cause it to use more stringent locking than with a select cursor. You declare an update cursor to let the database server know that the program might update (or delete) any row that it fetches as part of the SELECT statement. The update cursor employs promotable locks for rows that the program fetches. Other programs can read the locked row, but no other program can place a promotable or write lock. Before the program modifies the row, the row lock is promoted to an exclusive lock.

Although it is possible to declare an update cursor with the WITH HOLD keywords, the only reason to do so is to break a long series of updates into smaller transactions. You must fetch and update a particular row in the same transaction.

If an operation involves fetching and updating a very large number of rows, the lock table that the database server maintains can overflow. The usual way to prevent this overflow is to lock the entire table that is being updated. If this action is impossible, an alternative is to update through a hold cursor and to execute COMMIT WORK at frequent intervals. However, you must plan such an application very carefully because COMMIT WORK releases all locks, even those that are placed through a hold cursor.

As indicated in the diagram for DECLARE, to create an insert cursor, you associate a sequential cursor with a restricted form of the INSERT statement. The INSERT statement must include a VALUES clause; it cannot contain an embedded SELECT statement.

The following example contains ESQL/C code that declares an insert cursor:

EXEC SQL declare  ins_cur  cursor  for

	insert  into  stock  values

	(:stock_no,:manu_code,:descr,:u_price,:unit,:u_desc); 

The insert cursor simply inserts rows of data; it cannot be used to fetch data. When an insert cursor is opened, a buffer is created in memory to hold a block of rows. The buffer receives rows of data as the program executes PUT statements. The rows are written to disk only when the buffer is full. You can use the CLOSE, FLUSH, or COMMIT WORK statement to flush the buffer when it is less than full. This topic is discussed further under the PUT and CLOSE statements. You must close an insert cursor to insert any buffered rows into the database before the program ends. You can lose data if you do not close the cursor properly.

For a complete description of INSERT syntax and usage, see INSERT.

When you associate an INSERT statement with a cursor, the cursor is called an insert cursor. An insert cursor is a data structure that represents the rows that the INSERT statement is to add to the database. The insert cursor simply inserts rows of data; it cannot be used to fetch data. To create an insert cursor, you associate a cursor with a restricted form of the INSERT statement. The INSERT statement must include a VALUES clause; it cannot contain an embedded SELECT statement.

Create an insert cursor if you want to add multiple rows to the database in an INSERT operation. An insert cursor allows bulk insert data to be buffered in memory and written to disk when the buffer is full, as the following steps describe:

1. Use a DECLARE statement to define an insert cursor for the INSERT statement.
2. Open the cursor with the OPEN statement. The database server creates the insert buffer in memory and positions the cursor at the first row of the insert buffer.
3. Put successive rows of data into the insert buffer with the PUT statement.
4. The database server writes the rows to disk only when the buffer is full. You can use the CLOSE, FLUSH, or COMMIT WORK statement to flush the buffer when it is less than full. This topic is discussed further under the PUT and CLOSE statements.
5. Close the cursor with the CLOSE statement when the insert cursor is no longer needed. You must close an insert cursor to insert any buffered rows into the database before the program ends. You can lose data if you do not close the cursor properly.
6. Free the cursor with the FREE statement. The FREE statement releases the resources that are allocated for an insert cursor.

An insert cursor increases processing efficiency (compared with embedding the INSERT statement directly). This process reduces communication between the program and the database server and also increases the speed of the insertions.

In addition to select and function cursors, insert cursors can also have the sequential cursor characteristic. To create an insert cursor, you associate a sequential cursor with a restricted form of the INSERT statement. (For more information, see Insert Cursor.) The following example contains Informix ESQL/C code that declares a sequential insert cursor:

EXEC SQL declare  ins_cur  cursor  for

	insert  into  stock  values

	(:stock_no,:manu_code,:descr,:u_price,:unit,:u_desc); 

Structurally, you can declare a cursor as a sequential cursor (the default condition), a scroll cursor (using the SCROLL keyword), or a hold cursor (using the WITH HOLD keywords). The following sections explain these structural characteristics.

A select or function cursor can be either a sequential or a scroll cursor. An insert cursor can only be a sequential cursor. Select, function, and insert cursors can optionally be hold cursors. For a graphical representation of this information, see Overview of Cursor Types.

If you use only the CURSOR keyword, you create a sequential cursor, which can fetch only the next row in sequence from the active set. The sequential cursor can read through the active set only once each time it is opened. If you are using a sequential cursor for a select cursor, on each execution of the FETCH statement, the database server returns the contents of the current row and locates the next row in the active set.

The following example creates a read-only sequential cursor in a database that is not ANSI compliant and an update sequential cursor in an ANSI-compliant database:

EXEC SQL declare  s_cur  cursor  for

	select  fname,  lname  into  :st_fname,  :st_lname

	from  orders  where  customer_num  =  114;

In addition to select and function cursors, insert cursors can also have the sequential cursor characteristic. To create an insert cursor, you associate a sequential cursor with a restricted form of the INSERT statement. (For more information, see Insert Cursor.) The following example declares a sequential insert cursor:

EXEC SQL declare  ins_cur  cursor  for

	insert  into  stock  values

	(:stock_no,:manu_code,:descr,:u_price,:unit,:u_desc); 

Use the SCROLL keyword to create a scroll cursor, which can fetch rows of the active set in any sequence.

The database server retains the active set of the cursor as a temporary table until the cursor is closed. You can fetch the first, last, or any intermediate rows of the active set as well as fetch rows repeatedly without having to close and reopen the cursor. (See FETCH.)

On a multiuser system, the rows in the tables from which the active-set rows were derived might change after the cursor is opened and a copy is made in the temporary table. If you use a scroll cursor within a transaction, you can prevent copied rows from changing either by setting the isolation level to Repeatable Read or by locking the entire table in share mode during the transaction. (See SET ISOLATION and LOCK TABLE.)

The following example creates a scroll cursor for a SELECT:

DECLARE  sc_cur  SCROLL  CURSOR  FOR

	SELECT  *  FROM  orders

You can create scroll cursors for select and function cursors but not for insert cursors. Scroll cursors cannot be declared as FOR UPDATE.

Use the WITH HOLD keywords to create a hold cursor. A hold cursor allows uninterrupted access to a set of rows across multiple transactions. Ordinarily, all cursors close at the end of a transaction. A hold cursor does not close; it remains open after a transaction ends.

A hold cursor can be either a sequential cursor or a scroll cursor.

You can use the WITH HOLD keywords to declare select and function cursors (sequential and scroll), and insert cursors. These keywords follow the CURSOR keyword in the DECLARE statement. The following example creates a sequential hold cursor for a SELECT:

DECLARE  hld_cur  CURSOR  WITH  HOLD  FOR

	SELECT  customer_num,  lname,  city  FROM  customer

You can use a select hold cursor as the following ESQL/C code example shows. This code fragment uses a hold cursor as a master cursor to scan one set of records and a sequential cursor as a detail cursor to point to records that are located in a different table. The records that the master cursor scans are the basis for updating the records to which the detail cursor points. The COMMIT WORK statement at the end of each iteration of the first WHILE loop leaves the hold cursor c_master open but closes the sequential cursor c_detail and releases all locks. This technique minimizes the resources that the database server must devote to locks and unfinished transactions, and it gives other users immediate access to updated rows.

EXEC SQL BEGIN DECLARE SECTION;
int p_custnum,
int save_status;
long p_orddate;
EXEC SQL END DECLARE SECTION;

EXEC SQL prepare st_1 from
'select order_date
from orders where customer_num = ? for update';
EXEC SQL declare c_detail cursor for st_1;

EXEC SQL declare c_master cursor with hold for
select customer_num
from customer where city = 'Pittsburgh';

EXEC SQL open c_master;
if(SQLCODE==0) /* the open worked */
EXEC SQL fetch c_master into :p_custnum; /* discover first customer */
while(SQLCODE==0) /* while no errors and not end of pittsburgh customers */
{
EXEC SQL begin work; /* start transaction for customer p_custnum */
EXEC SQL open c_detail using :p_custnum;
if(SQLCODE==0) /* detail open succeeded */
EXEC SQL fetch c_detail into :p_orddate; /* get first order */
while(SQLCODE==0) /* while no errors and not end of orders */
{
EXEC SQL update orders set order_date = '08/15/94'
where current of c_detail;
if(status==0) /* update was ok */
EXEC SQL fetch c_detail into :p_orddate; /* next order */
}
if(SQLCODE==SQLNOTFOUND) /* correctly updated all found orders */
EXEC SQL commit work; /* make updates permanent, set status */
else /* some failure in an update */
{
save_status = SQLCODE; /* save error for loop control */
EXEC SQL rollback work;
SQLCODE = save_status; /* force loop to end */
}
if(SQLCODE==0) /* all updates, and the commit, worked ok */
EXEC SQL fetch c_master into :p_custnum; /* next customer? */
}
EXEC SQL close c_master;

Use either the CLOSE statement to close the hold cursor explicitly or the CLOSE DATABASE or DISCONNECT statements to close it implicitly. The CLOSE DATABASE statement closes all cursors.

If you associate a hold cursor with an INSERT statement, you can use transactions to break a long series of PUT statements into smaller sets of PUT statements. Instead of waiting for the PUT statements to fill the buffer and trigger an automatic write to the database, you can execute a COMMIT WORK statement to flush the row buffer. If you use a hold cursor, the COMMIT WORK statement commits the inserted rows but leaves the cursor open for further inserts. This method can be desirable when you are inserting a large number of rows, because pending uncommitted work consumes database server resources.

As indicated in the diagram for DECLARE, not all SELECT statements can be associated with a read-only or update cursor. If the DECLARE statement includes one of these options, you must observe certain restrictions on the SELECT statement that is included in the DECLARE statement (either directly or as a prepared statement).

If the DECLARE statement includes the FOR READ ONLY option, the SELECT statement must conform to the following restrictions:

• The SELECT statement cannot have a FOR READ ONLY option.
• The SELECT statement cannot have a FOR UPDATE option.

For a complete description of SELECT syntax and usage, see SELECT.

If the DECLARE statement includes the FOR UPDATE option, the SELECT statement must conform to the following restrictions:

• The statement can select data from only one table.
• The statement cannot include any aggregate functions.
• The statement cannot include any of the following clauses or keywords: DISTINCT, FOR READ ONLY, FOR UPDATE, GROUP BY, INTO TEMP, ORDER BY, UNION, or UNIQUE.
• In Enterprise Decision Server, the statement cannot include the INTO EXTERNAL and INTO SCRATCH clauses.

In a database that is not ANSI compliant, a cursor associated with a SELECT statement is a read-only cursor by default. The following example declares a read-only cursor in a non-ANSI database:

EXEC SQL declare cust_curs cursor for

	select * from customer_notansi;

If you want to make it clear in the program code that this cursor is a read-only cursor, you can specify the FOR READ ONLY option as shown in the following example:

EXEC SQL declare cust_curs cursor for

	select * from customer_notansi

	for read only;

If you want this cursor to be an update cursor, you need to specify the FOR UPDATE option in your DECLARE statement. The following example declares an update cursor:

EXEC SQL declare new_curs cursor for

	select * from customer_notansi

	for update;

If you want an update cursor to be able to modify only some of the columns in a table, you need to specify these columns in the FOR UPDATE option. The following example declares an update cursor and specifies that this cursor can update only the fname and lname columns in the customer_notansi table:

EXEC SQL declare name_curs cursor for

	select * from customer_notansi

	for update of fname, lname;

In an ANSI-compliant database, a cursor associated with a SELECT statement is an update cursor by default. The following example declares an update cursor in an ANSI-compliant database:

EXEC SQL declare x_curs cursor for

	select * from customer_ansi;

If you want to make it clear in the program documentation that this cursor is an update cursor, you can specify the FOR UPDATE option as shown in the following example:

EXEC SQL declare x_curs cursor for

	select * from customer_ansi

	for update;

If you want an update cursor to be able to modify only some of the columns in a table, you must specify these columns in the FOR UPDATE option. The following example declares an update cursor and specifies that this cursor can update only the fname and lname columns in the customer_ansi table:

EXEC SQL declare y_curs cursor for

	select * from customer_ansi

	for update of fname, lname;

If you want a cursor to be a read-only cursor, you must override the default behavior of the DECLARE statement by specifying the FOR READ ONLY option in your DECLARE statement. The following example declares a read-only cursor:

EXEC SQL declare z_curs cursor for

	select * from customer_ansi

	for read only;

The PREPARE statement lets you assemble the text of an SQL statement at runtime and pass the statement text to the database server for execution. If you anticipate that a dynamically prepared SELECT, EXECUTE FUNCTION (or EXECUTE PROCEDURE) statement that returns values could produce more than one row of data, the prepared statement must be associated with a cursor. (See PREPARE.)

The result of a PREPARE statement is a statement identifier (statement id or id variable), which is a data structure that represents the prepared statement text. To declare a cursor for the statement text, associate a cursor with the statement identifier.

You can associate a sequential cursor with any prepared SELECT or EXECUTE FUNCTION (or EXECUTE PROCEDURE) statement. You cannot associate a scroll cursor with a prepared INSERT statement or with a SELECT statement that was prepared to include a FOR UPDATE clause.

After a cursor is opened, used, and closed, a different statement can be prepared under the same statement identifier. In this way, it is possible to use a single cursor with different statements at different times. The cursor must be redeclared before you use it again.

The following example contains ESQL/C code that prepares a SELECT statement and declares a sequential cursor for the prepared statement text. The statement identifier st_1 is first prepared from a SELECT statement that returns values; then the cursor c_detail is declared for st_1.

EXEC SQL prepare  st_1  from

	'select  order_date  

		from  orders  where  customer_num  =  ?';

EXEC SQL declare  c_detail  cursor  for  st_1;

If you want to use a prepared SELECT statement to modify data, add a FOR UPDATE clause to the statement text that you wish to prepare, as the following ESQL/C example shows:

EXEC SQL prepare sel_1 from 

	'select * from customer for update';

EXEC SQL declare sel_curs cursor for sel_1;

The DECLARE statement allows you to declare a cursor for an ESQL/C collection variable. Such a cursor is called a collection cursor. You use a collection variable to access the elements of a collection (SET, MULTISET, LIST) column. Use a cursor when you want to access one or more elements in a collection variable.

The Collection Derived Table segment identifies the collection variable for which to declare the cursor. For more information, see Collection Derived Table.

The diagram for DECLARE refers to this section.

To declare a select cursor for a collection variable, include the Collection Derived Table segment with the SELECT statement that you associate with the collection cursor. A select cursor allows you to select one or more elements from the collection variable.

For a complete description of SELECT syntax and usage, see SELECT.

When you declare a select cursor for a collection variable, the DECLARE statement has the following restrictions:

• It can not include the FOR READ ONLY keywords that specify the read-only cursor mode.

The select cursor is an update cursor.

• It cannot include the SCROLL or WITH HOLD keywords.

The select cursor must be a sequential cursor.

In addition, the SELECT statement that you associate with the collection cursor has the following restrictions:

• It cannot include the following clauses or options: WHERE, GROUP BY, ORDER BY, HAVING, INTO TEMP, and WITH REOPTIMIZATION.
• It cannot contain expressions in the select list.

• If the collection contains elements of opaque, distinct, built-in, or other collection data types, the select list must be an asterisk (*).
• Column names in the select list must be simple column names.

These columns cannot use the following syntax:

	database@server:table.column --INVALID SYNTAX

• It must contain the name of the collection variable in the FROM clause.

You cannot specify an input parameter (the question-mark (?) symbol) for the collection variable. Likewise you cannot use the virtual table format of the Collection Derived Table segment.

A collection cursor that includes a SELECT statement with the Collection Derived Table clause allows you to access the elements in a collection variable.

To select more than one element, follow these general steps:

1. Create a client collection variable in your ESQL/C program.
2. Declare the collection cursor for the SELECT statement with the DECLARE statement.

If you want to modify the elements of the collection variable, declare the select cursor as an update cursor with the FOR UPDATE keywords. You can then use the WHERE CURRENT OF clause of the DELETE and UPDATE statements to delete or update elements of the collection.

3. Open this cursor with the OPEN statement.

1. Fetch the elements from the collection cursor with the FETCH statement and the INTO clause.
2. If necessary, perform any updates or deletes on the fetched data and save the modified collection variable in the collection column.

Once the collection variable contains the correct elements, use the UPDATE or INSERT statement to save the contents of the collection variable in the actual collection column (SET, MULTISET, or LIST).

3. Close the collection cursor with the CLOSE statement.

The following DECLARE statement declares a select cursor for a collection variable:

EXEC SQL BEGIN DECLARE SECTION;

	client collection set(integer not null) a_set;

EXEC SQL END DECLARE SECTION;
...
EXEC SQL declare set_curs cursor for

	select * from table(:a_set);

For an extended code example that uses a collection cursor for a SELECT statement, see Fetching From a Collection Cursor.

The diagram for DECLARE refers to this section.

To declare an insert cursor for a collection variable, include the Collection Derived Table segment with the INSERT statement that you associate with the collection cursor. An insert cursor allows you to insert one or more elements in the collection.

For a complete description of INSERT syntax and usage, see INSERT.

The insert cursor must be a sequential cursor, that is the DECLARE statement cannot contain the WITH HOLD keywords.

When you declare an insert cursor for a collection variable, the Collection- Derived Table clause of the INSERT statement must contain the name of the collection variable. You cannot specify an input parameter (the question-mark (?) symbol) for the collection variable. However, you can use an input parameter in the VALUES clause of the INSERT statement. This parameter indicates that the collection element is to be provided later by the FROM clause of the PUT statement.

A collection cursor that includes an INSERT statement with the Collection- Derived Table clause allows you to insert more than one element into a collection variable.

To insert more than one element, follow these general steps:

1. Create a client collection variable in your ESQL/C program.
2. Declare the collection cursor for the INSERT statement with the DECLARE statement.
3. Open the cursor with the OPEN statement.
4. Put the elements into the collection cursor with the PUT statement and the FROM clause.
5. Once the collection variable contains all the elements, you then use the UPDATE statement or the INSERT statement on a table name to save the contents of the collection variable in a collection column (SET, MULTISET, or LIST).
6. Close the collection cursor with the CLOSE statement.

For example, the following DECLARE statement declares an insert cursor for the a_set collection variable:

EXEC SQL BEGIN DECLARE SECTION;

	client collection multiset(smallint not null) a_mset;

	int an_element;

EXEC SQL END DECLARE SECTION;
...
EXEC SQL declare mset_curs cursor for

	insert into table(:a_mset)

	values (?);

EXEC SQL open mset_curs;
while (1)

{

...

	EXEC SQL put mset_curs from :an_element;

...

}

To insert the elements into the collection variable, use the PUT statement with the FROM clause. For a code example that uses a collection cursor for an INSERT statement, see Inserting into a Collection Cursor.

To roll back a modification, you must perform the modification within a transaction. A transaction in a database that is not ANSI compliant begins only when the BEGIN WORK statement is executed.

In an ANSI-compliant database, transactions are always in effect.

The database server enforces the following guidelines for select and update cursors. These guidelines ensure that modifications can be committed or rolled back properly:

• Open an insert or update cursor within a transaction.
• Include PUT and FLUSH statements within one transaction.
• Modify data (update, insert, or delete) within one transaction.

The database server lets you open and close a hold cursor for an update outside a transaction; however, you should fetch all the rows that pertain to a given modification and then perform the modification all within a single transaction. You cannot open and close hold or update cursors outside a transaction.

The following example uses an update cursor within a transaction:

EXEC SQL declare  q_curs  cursor  for

	select  customer_num, fname, lname  from  customer  

	where  lname  matches  :last_name

		for  update;

EXEC SQL open  q_curs;

EXEC SQL begin  work;

EXEC SQL fetch  q_curs  into  :cust_rec; /* fetch after begin */

EXEC SQL update  customer  set lname = 'Smith' 

	where  current  of  q_curs;

/* no error */

EXEC SQL commit  work;

When you update a row within a transaction, the row remains locked until the cursor is closed or the transaction is committed or rolled back. If you update a row when no transaction is in effect, the row lock is released when the modified row is written to disk.

If you update or delete a row outside a transaction, you cannot roll back the operation.

In a database that uses transactions, you cannot open an insert cursor outside a transaction unless it was also declared with the with hold keywords.

Related statements: CLOSE, DELETE, EXECUTE PROCEDURE, FETCH, FREE, INSERT, OPEN, PREPARE, PUT, SELECT, and UPDATE

For discussions of cursors and data modification, see the Informix Guide to SQL: Tutorial.

For more advanced issues related to cursors or information about using cursors with collection variables, see the Informix ESQL/C Programmer's Manual.