Insert with a Collection-Derived Table

About this task

To declare an Insert cursor for a collection variable, include the Collection- Derived Table segment in the INSERT statement associated with the Collection cursor. An Insert cursor can insert one or more elements in the collection. For a description of INSERT syntax and usage, see INSERT statement.

The Insert cursor must be a sequential cursor. That is, the DECLARE statement cannot specify the SCROLL keyword.

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:

Procedure

  1. Create a client collection variable in your 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, 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.

Results

This example 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.