Select with a Collection-Derived Table

Enterprise Edition Home | Express Edition Home | Previous Page | Next Page SQL Statements > DECLARE >

Select with a 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 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 cannot include the FOR READ ONLY keywords as 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 specify 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.

Using a SELECT Cursor with a Collection Variable

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

To select more than one element

Create a client collection variable in your ESQL/C program.
Declare the collection cursor for the SELECT statement with the DECLARE statement.
To modify 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.
Open this cursor with the OPEN statement.
Fetch the elements from the collection cursor with the FETCH statement and the INTO clause.
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).
Close the collection cursor with the CLOSE statement.

This 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 (IDS).