Operate on a collection column

The collection variable stores the elements of the collection. However, it has no intrinsic connection with a database column. You must use an INSERT or UPDATE statement to explicitly save the contents of the collection variable into the collection column.

You can use the SELECT, UPDATE, INSERT, and DELETE statements to access a collection column (SET, MULTISET, or LIST), as follows:
  • The SELECT statement fetches all elements from a collection column.
  • The INSERT statement inserts a new collection into a collection column.

    Use the INSERT statement on a table or view name and specify the collection variable in the VALUES clause.

    Insertion of many elements into a collection host variable shows an INSERT statement that saves the contents of a collection variable in a collection column.

  • The UPDATE statement updates the entire collection in a collection column with new values.

    Use an UPDATE statement on a table or view name and specify the collection variable in the SET clause.

    Updating one element in a collection host variable shows an UPDATE statement that saves the contents of a collection variable in a collection column.

For more information about how to use these statements with collection columns, see the HCL OneDB™ Guide to SQL: Tutorial.