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 the 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