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 the following figure shows. Figure 1: Insert into
a SET or MULTISET stored in a collection variable.
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 Handle collections in SELECT statements. The collection that the
previous figure 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 the following figure shows. Figure 2: Virtual table
elements.
5
7
31
19
13
After the insert, p_coll might look like the virtual
table that the following figure shows.Figure 3: Virtual table elements.
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.
Tip: You can
only insert one value at a time into a simple collection.