Accessing a Collection Through a Collection Variable

When you use the collection-variable format of the collection-derived table segment, you use a host or program variable to access and manipulate the elements of a collection. This format allows you to modify the contents of a variable as you would a table in the database, and then update the actual table with the contents of the collection variable.

You can use the collection-variable format (the TABLE keyword preceding a collection variable) in place of the name of a table, synonym, or view in the following SQL statements (or in the FOREACH statement of SPL):

• The FROM clause of the SELECT statement to access an element of the collection variable
• The INTO clause of the INSERT statement to add a new element to the collection variable
• The DELETE statement to remove an element from the collection variable
• The UPDATE statement to modify an existing element in the collection variable
• The DECLARE statement to declare a select or insert cursor to access multiple elements of an ESQL/C collection host variable
• The FETCH statement to retrieve a single element from a collection host variable that is associated with a select cursor
• The PUT statement to retrieve a single element from a collection host variable that is associated with an insert cursor
• The FOREACH statement to declare a cursor to access multiple elements of an SPL collection variable and to retrieve a single element from this collection variable