Using Collection Variables (IDS)

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.

Restrictions

When you use a collection cursor to fetch individual elements from a collection variable, the FOREACH statement has the following restrictions:

• It cannot contain the WITH HOLD keywords.
• It must contain a restricted SELECT statement in the FOREACH loop.

In addition, the SELECT statement that you associate with the collection cursor has the following restrictions:

• Its general structure is SELECT... INTO ... FROM TABLE. The statement selects one element at a time from a collection variable specified after the TABLE keyword into another variable called an element variable.
• It cannot contain an expression in the projection list.
• It cannot include the following clauses or options: WHERE, GROUP BY, ORDER BY, HAVING, INTO TEMP, and WITH REOPTIMIZATION.
• The data type of the element variable must be the same as the element type of the collection.
• The data type of the element variable can be any opaque, distinct, or collection data type, or any built-in data type except SERIAL, SERIAL8, TEXT, BYTE, CLOB, or BLOB.
• If the collection contains opaque, distinct, built-in, or collection types, the projection list must be an asterisk ( * ) symbol.
• If the collection contains ROW types, the projection list can be a list of one or more field names.

Examples

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.

Modifying Elements in a Collection Variable

To update an element of a collection within an SPL routine, you must first declare a cursor with the FOREACH statement.

Then, within the FOREACH loop, select elements one at a time from the collection variable, using the collection variable as a collection-derived table in a SELECT query.

When the cursor is positioned on the element to be updated, you can use the WHERE CURRENT OF clause, as follows:

• The UPDATE statement with the WHERE CURRENT OF clause updates the value in the current element of the collection variable.
• The DELETE statement with the WHERE CURRENT OF clause deletes the current element from the collection variable.

Calling a UDR in the FOREACH Loop

Related Statements