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 HCL Informix® Guide to SQL: Syntax. For the syntax of a collection-derived table, see the Collection-Derived Table segment in the HCL 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)