Insert 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 the following figure shows.

Figure 1: Insert a value into a collection.

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 the previous figure 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 Modify data.