Selecting from a Collection Variable

The SELECT statement in conjunction with the Collection-Derived Table segment allows you to select elements from a collection variable.

About this task

The Collection-Derived Table segment identifies the collection variable from which to select the elements. (See Collection-Derived Table.)

To modify the contents of a column of a collection data type, you can use the SELECT statement with a collection variable in various ways:
  • You can select the contents (if any) of a collection column into a collection variable.

    You can assign the data type of the column to a collection variable of type COLLECTION (that is, an untyped collection variable).

  • You can select the contents from a collection variable to determine the data that you might want to update.
  • You can select the contents from a collection variable INTO another variable in order to update certain collection elements.

    The INTO clause identifies the variable for the element value that is selected from the collection variable. The data type of the host variable in the INTO clause must be compatible with that of the corresponding collection element.

  • You can use a Collection cursor to select one or more elements from the collection variable.

    For more information, including restrictions on the SELECT statement, see Associating a Cursor with a Prepared Statement.

  • You can use a Collection cursor to select one or more elements from an SPL collection variable.

    For more information, including restrictions on the SELECT statement, see Using a SELECT ... INTO Statement.

When one of the tables to be joined is a collection, the FROM clause cannot specify a join. This restriction applies when the collection variable holds your collection-derived table. See also Collection-Derived Table. and the INSERT, UPDATE, and DELETE statement descriptions in this chapter.