Declaring Collection Variables

A local variable of type COLLECTION, SET, MULTISET, or LIST can hold a collection of values fetched from the database. You cannot define a collection variable as global (with the GLOBAL keyword) or with a default value.

A variable declared with the keyword COLLECTION is an untyped (or generic) collection variable that can hold a collection of any data type.

A variable declared as type SET, MULTISET, or LIST is a typed collection variable. It can hold a collection of its specified data type only.

You must use the NOT NULL keywords when you define the elements of a typed collection variable, as in the following examples:

DEFINE a SET ( INT NOT NULL );

DEFINE b MULTISET ( ROW ( b1 INT,
                   b2 CHAR(50)
                 ) NOT NULL );

DEFINE c LIST( SET( INTEGER NOT NULL ) NOT NULL );

With variable c, both the INTEGER values in the SET and the SET values in the LIST are defined as NOT NULL.

You can define collection variables with nested complex types to hold matching nested complex type data. Any type or depth of nesting is allowed. You can nest ROW types within collection types, collection types within ROW types, collection types within collection types, ROW types within collection and ROW types, and so on.

If you declare a variable as COLLECTION type, the variable acquires varying data type declarations if it is reassigned within the same statement block, as in the following example:

DEFINE a COLLECTION;
LET a = setB;
...
LET a = listC;

In this example, varA is a generic collection variable that changes its data type to the data type of the currently assigned collection. The first LET statement makes varA a SET variable. The second LET statement makes varA a LIST variable.