LIST collection types

A LIST is an ordered collection of elements that allows duplicate values. A LIST differs from a MULTISET in that each element in a LIST has an ordinal position in the collection. The order of the elements in a list corresponds with the order in which values are inserted into the LIST. You can define a column as a LIST collection type when you want to store collections whose elements have the following characteristics:

• The elements have a specific order associated with them.
• The elements might not be unique.

To illustrate how you might use a LIST, suppose your sales department wants to keep a monthly record of the sales total for each salesperson. You can use a LIST to define a column in a table that contains the monthly sales totals for each salesperson. The following example creates a table in which the month_sales column is a LIST. The first entry (element) in the LIST, with an ordinal position of 1, might correspond to the month of January, the second element, with an ordinal position of 2, February, and so forth:

CREATE TABLE sales_person
(
   name         CHAR(30),
   month_sales  LIST(MONEY NOT NULL)
);

You can use the month_sales column in this statement to store and access the monthly sales totals for each salesperson. More specifically, you might perform queries on the month_sales column to find out:

• The total sales that a salesperson generated during a specified month
• The total sales for every salesperson during a specified month