Inserting into a Collection

Home | Previous Page | Next Page Creating and Using SPL Routines > Handling Collections (IDS) >

Inserting into a Collection

You can insert a value into a collection without declaring a cursor. If the collection is a SET or MULTISET, the value is added to the collection but the position of the new element is undefined because the collection has no particular order. If the value is a LIST, you can add the new element at a specific point in the LIST or at the end of the LIST.

In the manager table, the direct_reports column contains collections of SET type, and the projects column contains a LIST. To add a name to the SET in the direct_reports column, use an INSERT statement with a collection-derived table, as Figure 446 shows.

Figure 446.

CREATE PROCEDURE new_emp( emp VARCHAR(30), mgr VARCHAR(30) )

   DEFINE r SET(VARCHAR(30) NOT NULL);

   SELECT direct_reports INTO r FROM manager
      WHERE mgr_name = mgr;

   INSERT INTO TABLE (r) VALUES(emp);

   UPDATE manager SET direct_reports = r
      WHERE mgr_name = mgr;

END PROCEDURE;

This SPL procedure takes an employee name and a manager name as arguments. The procedure then selects the collection in the direct_reports column for the manager the user has entered, adds the employee name the user has entered, and updates the manager table with the new collection.

The INSERT statement in Figure 446 inserts the new employee name that the user supplies into the SET contained in the collection variable r. The UPDATE statement then stores the new collection in the manager table.

Notice the syntax of the VALUES clause. The syntax rules for inserting literal data and variables into collection variables are as follows:

Use parentheses after the VALUES keyword to enclose the complete list of values.
If the collection is SET, MULTISET, or LIST, use the type constructor followed by brackets to enclose the list of values to be inserted. In addition, the collection value must be enclosed in quotes.
```
VALUES( "SET{ 1,4,8,9 }" )
```
If the collection contains a row type, use ROW followed by parentheses to enclose the list of values to be inserted:
```
VALUES( ROW( 'Waters', 'voyager_project' ) )
```
If the collection is a nested collection, nest keywords, parentheses, and brackets according to how the data type is defined:
```
VALUES( "SET{ ROW('Waters', 'voyager_project'),
             ROW('Adams', 'horizon_project') }")
```

For more information on inserting values into collections, see Modifying Data.

Inserting into a Nested Collection

If you want to insert into a nested collection, the syntax of the VALUES clause changes. Suppose, for example, that you want to insert a value into the twin_primes column of the numbers table that Figure 420 shows.

With the twin_primes column, you might want to insert a SET into the LIST or an element into the inner SET. The following sections describe each of these tasks.

Inserting 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 Figure 447 shows.

Figure 447.

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 one that Figure 448 shows.

Figure 448.

{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 Figure 449 show how to do this.

Figure 449.

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 one that Figure 450 shows.

Figure 450.

{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 Figure 451 shows.

Figure 451.

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.

Inserting 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 Figure 452 shows, and you want to insert the value 18 into the last SET in the LIST.

Figure 452.

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

Figure 453 shows the beginning of a procedure that inserts the value.

Figure 453.

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.

Figure 454 shows how to declare a cursor so that you can move through the elements of the outer collection.

Figure 454.

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

   FOREACH element_cursor FOR