Selecting Elements from a Collection

Suppose you want your SPL routine to select elements from the collection stored in the collection variable, one at time, so that you can handle the elements.

To move through the elements of a collection, you first need to declare a cursor using a FOREACH statement, just as you would declare a cursor to move through a set of rows. Figure 432 shows the FOREACH and END FOREACH statements, with no statements between them yet.

Figure 432.

FOREACH cursor1 FOR
. . .
END FOREACH

The FOREACH statement is described in Using Cursors and the IBM Informix: Guide to SQL Syntax.

The next section, The Collection Query, describes the statements that are omitted between the FOREACH and END FOREACH statements.

The examples in the following sections are based on the polygons table of Figure 421.

The Collection Query

After you declare the cursor between the FOREACH and END FOREACH statements, you enter a special, restricted form of the SELECT statement known as a collection query.

A collection query is a SELECT statement that uses the FROM TABLE keywords followed by the name of a collection variable. Figure 433 shows this structure, which is known as a collection-derived table.

Figure 433.

FOREACH cursor1 FOR

   SELECT * INTO pnt FROM TABLE(vertexes)
   . . .
END FOREACH

The SELECT statement in Figure 433 uses the collection variable vertexes as a collection-derived table. You can think of a collection-derived table as a table of one column, with each element of the collection being a row of the table. For example, you can visualize the SET of four points stored in vertexes as a table with four rows, such as the one that Figure 434 shows.

Figure 434.

'(3.0,1.0)'
'(8.0,1.0)'
'(3.0,4.0)'
'(8.0,4.0)'

After the first iteration of the FOREACH statement in Figure 434, the collection query selects the first element in vertexes and stores it in pnt, so that pnt contains the value '(3.0,1.0)'.

Because the collection variable vertexes contains a SET, not a LIST, the elements in vertexes have no defined order. In a real database, the value '(3.0,1.0)' might not be the first element in the SET.

Adding the Collection Query to the SPL Routine

Now you can add the cursor defined with FOREACH and the collection query to the SPL routine, as Figure 435 shows.

Figure 435.

CREATE PROCEDURE shapes()

   DEFINE vertexes SET( point NOT NULL );
   DEFINE pnt point;

   SELECT definition INTO vertexes FROM polygons
      WHERE id = 207;

   FOREACH cursor1 FOR
      SELECT * INTO pnt FROM TABLE(vertexes);
   . . .
   END FOREACH
. . .
END PROCEDURE;

The statements that Figure 435 shows form the framework of an SPL routine that handles the elements of a collection variable. To decompose a collection into its elements, use a collection-derived table. After the collection is decomposed into its elements, the routine can access elements individually as table rows of the collection-derived table. Now that you have selected one element in pnt, you can update or delete that element, as Updating a Collection Element and Deleting a Collection Element describe.

For the complete syntax of the collection query, see the SELECT statement in the IBM Informix: Guide to SQL Syntax. For the syntax of a collection-derived table, see the Collection-Derived Table segment in the IBM Informix: Guide to SQL Syntax.

If you are selecting from a collection that contains no elements or zero elements, you can use a collection query without declaring a cursor. However, if the collection contains more than one element and you do not use a cursor, you will receive an error message.