Insert a value into the inner collection

In an SPL routine, you can also insert a value into the inner collection of a nested collection. In general, to access the inner collection of a nested collection and add a value to it, perform the following steps:

Declare a collection variable to hold the entire collection stored in one row of a table.
Declare an element variable to hold one element of the outer collection. The element variable is itself a collection variable.
Select the entire collection from one row of a table into the collection variable.
Declare a cursor so that you can move through the elements of the outer collection.
Select one element at a time into the element variable.
Use a branch or loop to locate the inner collection you want to update.
Insert the new value into the inner collection.
Close the cursor.
Update the database table with the new collection.

As an example, you can use this process on the twin_primes column of numbers. For example, suppose that twin_primes contains the values that the following figure shows, and you want to insert the value 18 into the last SET in the LIST.

Figure 1: The twin_primes list.

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

The following figure shows the beginning of a procedure that inserts the value.

Figure 2: Procedure that inserts the value.

CREATE PROCEDURE add_int()

   DEFINE list_var LIST( SET( INTEGER NOT NULL ) NOT NULL );
   DEFINE set_var SET( INTEGER NOT NULL );

   SELECT twin_primes INTO list_var FROM numbers
         WHERE id = 100;

So far, the attaint procedure has performed steps 1, 2, and 3. The first DEFINE statement declares a collection variable that holds the entire collection stored in one row of numbers.

The second DEFINE statement declares an element variable that holds an element of the collection. In this case, the element variable is itself a collection variable because it holds a SET. The SELECT statement selects the entire collection from one row into the collection variable, list_var.

The following figure shows how to declare a cursor so that you can move through the elements of the outer collection.

Figure 3: Declare a cursor to move through the elements of the outer collection.

FOREACH list_cursor FOR
   SELECT * INTO set_var FROM TABLE( list_var);

   FOREACH element_cursor FOR