Access a collection variable

In SQL statements, the application specifies a collection-derived table in place of a table name to perform the following operations on the collection host variable:
  • You can insert an element into the collection host variable with the collection-derived table clause after the INTO keyword of an INSERT, or with the PUT statement.

    For more information, see Insert elements into a collection variable.

  • You can select an element from a collection host variable with the collection-derived table clause in the FROM clause of the SELECT statement.

    For more information, see Select from a collection variable.

  • You can update all or some elements in the collection host variable with the collection-derived table clause (instead of a table name) after the UPDATE keyword in an UPDATE statement.

    For more information, see Update a collection variable.

  • You can delete all or some elements from the collection host variable with the collection-derived table clause after the FROM keyword in the DELETE statement.

    For more information, see Delete elements from a collection variable.

Tip: If you only need to insert or update a collection column with literal values, you do not need to use a collection host variable. Instead, you can explicitly list the literal-collection value in either the INTO clause of the INSERT statement or the SET clause of the UPDATE statement.

For more information, see Insert into and update a collection column.

After the collection host variable contains valid elements, you update the collection column with the contents of the host variable. For more information, see Operate on a collection column. For more information about the syntax of the collection-derived table clause, see the description of the collection-derived table segment in the HCL OneDB™ Guide to SQL: Syntax.