Add the collection query to the SPL routine

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

SELECT * INTO pnt FROM TABLE(vertexes)