Add 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 the following example shows.

Figure 1: Cursor defined with FOREACH and the collection query.

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 shown above 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 rows of the collection-derived table. Now that you have selected one element in pnt, you can update or delete that element, as Update a collection element and Delete a collection element describe.

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

Tip: 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.

Attention: In the program fragment above, the database server would have issued a syntax error if the query (

SELECT * INTO pnt FROM TABLE(vertexes)

) within the FOREACH cursor definition had ended with a semicolon ( ; ) character as a statement terminator. Here the END FOREACH keywords are the logical statement terminator.