Declare collection variables

In order to hold a collection fetched from the database, a variable must be of type SET, MULTISET, or LIST.

Important: A collection variable must be defined as a local variable. You cannot define a collection variable as a global variable.

A variable of SET, MULTISET, or LIST type is a collection variable that holds a collection of the type named in the DEFINE statement. The following figure shows how to define typed collection variables.

Figure 1: Define typed collection variables.

DEFINE a SET ( INT NOT NULL );

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

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

You must always define the elements of a collection variable as NOT NULL. In this example, the variable a is defined to hold a SET of non-NULL integers; the variable b holds a MULTISET of non-NULL row types; and the variable c holds a LIST of non-NULL sets of non-NULL decimal values.

In a variable definition, you can nest complex types in any combination or depth to match the data types stored in your database.

You cannot assign a collection variable of one type to a collection variable of another type. For example, if you define a collection variable as a SET, you cannot assign another collection variable of MULTISET or LIST type to it.