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. The following figure shows this structure, which is known as a collection-derived table.

Figure 1: Collection-derived table.

FOREACH cursor1 FOR

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

The SELECT statement 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 the following figure shows.

Figure 2: Table with four rows.

'(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 the previous figure, the collection query selects the first element in vertexes and stores it in pnt, so that pnt contains the value '(3.0,1.0)'.

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