Insert a collection into the outer collection

Inserting a SET into the LIST is similar to inserting a single value into a simple collection.

To insert a SET into the LIST, declare a collection variable to hold the LIST and select the entire collection into it. When you use the collection variable as a collection-derived table, each SET in the LIST becomes a row in the table. You can then insert another SET at the end of the LIST or at a specified point.

For example, the twin_primes column of one row of numbers might contain the following LIST, as the following figure shows.

Figure 1: Sample LIST.

LIST( SET{3,5}, SET{5,7}, SET{11,13} )

If you think of the LIST as a collection-derived table, it might look similar to the following.

Figure 2: Thinking of the LIST as a collection-derived table.

{3,5}
{5,7}
{11,13}

You might want to insert the value "SET{17,19}" as a second item in the LIST. The statements in the following figure show how to do this.

Figure 3: Insert a value in the LIST.

CREATE PROCEDURE add_set()

   DEFINE l_var LIST( SET( INTEGER NOT NULL ) NOT NULL );

   SELECT twin_primes INTO l_var FROM numbers
      WHERE id = 100;

   INSERT AT 2 INTO TABLE (l_var) VALUES( "SET{17,19}" );

   UPDATE numbers SET twin_primes = l
      WHERE id = 100;

END PROCEDURE;

In the INSERT statement, the VALUES clause inserts the value SET {17,19} at the second position of the LIST. Now the LIST looks like the following figure.

Figure 4: LIST items.

{3,5}
{17,19}
{5,7}
{11,13}

You can perform the same insert by passing a SET to an SPL routine as an argument, as the following figure shows.

Figure 5: Passing a SET to an SPL routine as an argument.

CREATE PROCEDURE add_set( set_var SET(INTEGER NOT NULL), 
   row_id INTEGER );

   DEFINE list_var LIST( SET(INTEGER NOT NULL) NOT NULL );
   DEFINE n SMALLINT;

   SELECT CARDINALITY(twin_primes) INTO n FROM numbers
      WHERE id = row_id;

   LET n = n + 1;

   SELECT twin_primes INTO list_var FROM numbers
      WHERE id = row_id;

   INSERT AT n INTO TABLE( list_var ) VALUES( set_var );

   UPDATE numbers SET twin_primes = list_var
      WHERE id = row_id;

END PROCEDURE;

In add_set(), the user supplies a SET to add to the LIST and an INTEGER value that is the id of the row in which the SET will be inserted.