Using a Select Cursor with a Collection Variable

About this task

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:

Procedure

  1. Create a client collection variable in your program.
  2. 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.
  3. Open this cursor with the OPEN statement.
  4. Fetch the elements from the Collection cursor with the FETCH statement and the INTO clause.
  5. 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).
  6. Close the Collection cursor with the CLOSE statement.

Results

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.