Using Collection Variables

The FOREACH statement allows you to declare a cursor for an SPL collection variable. Such a cursor is called a Collection cursor. Use a collection variable to access the elements of a collection (SET, MULTISET, LIST) column. Use a cursor when you want to access one or more elements in a collection variable.

The following excerpt from an SPL routine shows how to fill a collection variable and then how to use a cursor to access individual elements:

DEFINE a SMALLINT;
DEFINE b SET(SMALLINT NOT NULL);
SELECT numbers INTO b FROM table1 WHERE id = 207;
FOREACH cursor1 FOR
   SELECT * INTO a FROM TABLE(b);
...
END FOREACH;

In this example, the SELECT statement selects one element at a time from the collection variable b into the element variable a. The projection list is an asterisk, because the collection variable b contains a collection of built-in types. The variable b is used with the TABLE keyword as a Collection-Derived Table. For more information, see Collection-Derived Table.

The next example also shows how to fill a collection variable and then how to use a cursor to access individual elements. This example, however, uses a list of ROW-type fields in its projection list:

DEFINE employees employee_t;
DEFINE n VARCHAR(30);
DEFINE s INTEGER;

SELECT emp_list into employees FROM dept_table
   WHERE dept_no = 1057;
FOREACH cursor1 FOR
   SELECT name,salary 
      INTO n,s FROM TABLE( employees ) AS e;
...
END FOREACH;

Here the collection variable employees contains a collection of ROW types. Each ROW type contains the fields name and salary. The collection query selects one name and salary combination at a time, placing name into n and salary into s. The AS keyword declares e as an alias for the collection-derived table employees. The alias exists as long as the SELECT statement executes.