Insert into a LIST

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.

The following figure shows the statements you need to define a collection variable and select a LIST from the numbers table into the collection variable.

Figure 1: Defining a collection variable and selecting a LIST.

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

Figure 2: Add an element at a specific point in a LIST.

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.