Preparing for Collection Data Types (IDS)

Before you can access and handle an individual element of a simple or nested collection, you must perform the following tasks:

• Declare a collection variable to hold the collection.
• Declare an element variable to hold an individual element of the collection.
• Select the collection from the database into the collection variable.

After you take these initial steps, you can insert elements into the collection or select and handle elements that are already in the collection.

Each of these steps is explained in the following sections, using the numbers table as an example.

Declaring a Collection Variable

Before you can retrieve a collection from the database into an SPL routine, you must declare a collection variable. Figure 422 shows how to declare a collection variable to retrieve the primes column from the numbers table.

DEFINE p_coll SET( INTEGER NOT NULL );

The DEFINE statement declares a collection variable p_coll, whose type matches the data type of the collection stored in the primes column.

Declaring an Element Variable

After you declare a collection variable, you declare an element variable to hold individual elements of the collection. The data type of the element variable must match the data type of the collection elements.

For example, to hold an element of the SET in the primes column, use an element variable declaration such as the one that Figure 423 shows.

DEFINE p INTEGER;

To declare a variable that holds an element of the twin_primes column, which holds a nested collection, use a variable declaration such as the one that Figure 424 shows.

DEFINE s SET( INTEGER NOT NULL );

The variable s holds a SET of integers. Each SET is an element of the LIST stored in twin_primes.

Selecting a Collection into a Collection Variable

After you declare a collection variable, you can fetch a collection into it. To fetch a collection into a collection variable, enter a SELECT INTO statement that selects the collection column from the database into the collection variable you have named.

For example, to select the collection stored in one row of the primes column of numbers, add a SELECT statement, such as the one that Figure 425 shows, to your SPL routine.

SELECT primes INTO p_coll FROM numbers
   WHERE id = 220;

The WHERE clause in the SELECT statement specifies that you want to select the collection stored in just one row of numbers. The statement places the collection into the collection variable p_coll, which Figure 422 declares.

The variable p_coll now holds a collection from the primes column, which could contain the value SET {5,7,31,19,13}.