Collection data types

Collection data types enable you to store and manipulate collections of data within a single row of a table. A collection data type has two components: a type constructor, which determines whether the collection type is a SET, MULTISET, or LIST, and an element type, which specifies the type of data that the collection can contain. (The SET, MULTISET, and LIST collection types are described in detail in the following sections.)

The elements of a collection can be of most any data type. (For a list of exceptions, see Restrictions on collections.) The elements of a collection are the values that the collection contains. In a collection that contains the values: {'blue', 'green', 'yellow', and 'red'}, 'blue' represents a single element in the collection. Every element in a collection must be of the same type. For example, a collection whose element type is INTEGER can contain only integer values.

The element type of a collection can represent a single data type (column) or multiple data types (row). In the following example, the col_1 column represents a SET of integers: 
col_1 SET(INTEGER NOT NULL)
To define a collection data type that contains multiple data types, you can use a named row type or an unnamed row type. In the following example, the col_2 column represents a SET of rows that contain name and salary fields: 
col_2 SET(ROW(name VARCHAR(20), salary INTEGER) NOT NULL)
Important: When you define a collection data type, you must include the NOT NULL constraint as part of the type definition. No other column constraints are allowed on a collection data type.
After you define a column as a collection data type, you can perform the following operations on the collection:
  • Select and modify individual elements of a collection (from programs only).
  • Count the number of elements that a collection contains.
  • Determine if certain values are in a collection.

For information about the syntax that you use to create collection data types, see the Data Type segment in the HCL OneDB™ Guide to SQL: Syntax. For information about how to convert a value of one collection type to another collection type, see the HCL OneDB Guide to SQL: Tutorial.