After you retrieve a collection into a collection variable, you can insert a value into the collection variable. The syntax of the INSERT statement varies slightly, depending on the type of the collection to which you want to add values.
To insert into a SET or MULTISET stored in a collection variable, use an INSERT statement and follow the TABLE keyword with the collection variable, as Figure 426 shows.
INSERT INTO TABLE(p_coll) VALUES(3);
The TABLE keyword makes the collection variable a collection-derived table. Collection-derived tables are described in the section Handling Collections in SELECT Statements (IDS). The collection that Figure 426 derives is a virtual table of one column, with each element of the collection representing a row of the table. Before the insert, consider p_coll as a virtual table that contains the rows (elements) that Figure 427 shows.
5 7 31 19 13
After the insert, p_coll might look like the virtual table that Figure 428 shows.
5 7 31 19 13 3
Because the collection is a SET, the new value is added to the collection, but the position of the new element is undefined. The same principle is true for a MULTISET.
If the collection is a LIST, you can add the new element at a specific point in the LIST or at the end of the LIST. As with a SET or MULTISET, you must first define a collection variable and select a collection from the database into the collection variable.
Figure 429 shows the statements you need to define a collection variable and select a LIST from the numbers table into the collection variable.
DEFINE e_coll LIST(INTEGER NOT NULL); SELECT evens INTO e_coll FROM numbers WHERE id = 99;
At this point, the value of e_coll might be LIST {2,4,6,8,10}. Because e_coll holds a LIST, each element has a numbered position in the list. To add an element at a specific point in a LIST, add an AT position clause to the INSERT statement, as Figure 430 shows.
INSERT AT 3 INTO TABLE(e_coll) VALUES(12);
Now the LIST in e_coll has the elements {2,4,12,6,8,10}, in that order.
The value you enter for the position in the AT clause can be a number or a variable, but it must have an INTEGER or SMALLINT data type. You cannot use a letter, floating-point number, decimal value, or expression.
At times you might want to add an element at the end of a LIST. In this case, you can use the cardinality() function to find the number of elements in a LIST and then enter a position that is greater than the value cardinality() returns.
Dynamic Server allows you to use the cardinality() function with a collection that is stored in a column but not with a collection that is stored in a collection variable. In an SPL routine, you can check the cardinality of a collection in a column with a SELECT statement and return the value to a variable.
Suppose that in the numbers table, the evens column of the row whose id column is 99 still contains the collection LIST {2,4,6,8,10}. This time, you want to add the element 12 at the end of the LIST. You can do so with the SPL procedure end_of_list, as Figure 431 shows.
CREATE PROCEDURE end_of_list()
DEFINE n SMALLINT;
DEFINE list_var LIST(INTEGER NOT NULL);
SELECT CARDINALITY(evens) FROM numbers INTO n
WHERE id = 100;
LET n = n + 1;
SELECT evens INTO list_var FROM numbers
WHERE id = 100;
INSERT AT n INTO TABLE(list_var) VALUES(12);
END PROCEDURE;In end_of_list, the variable n holds the value that CARDINALITY() returns, that is, the count of the items in the LIST. The LET statement increments n, so that the INSERT statement can insert a value at the last position of the LIST. The SELECT statement selects the collection from one row of the table into the collection variable list_var. The INSERT statement inserts the element 12 at the end of the list.
The syntax of the VALUES clause is different when you insert into an SPL collection variable from when you insert into a collection column. The syntax rules for inserting literals into collection variables are as follows: