The associative array data type is used to represent associative
arrays. It has many features which contribute to its utility.
The associative array data type supports the following associative
array properties:
- No predefined cardinality is specified for associative arrays.
This enables you to continue adding elements to the array without
concern for a maximum size which is useful if you do not know in advance
how many elements will constitute a set.
- The array index value can be a non integer data type. VARCHAR
and INTEGER are supported index values for the associative array index.
- Index values do not have to be contiguous. In contrast to a conventional
array which is indexed by position, an associative array is an array
that is indexed by values of another data type and there are not necessarily
index elements for all possible index values between the lowest and
highest. This is useful if for example you want to create a set that
stores names and phone numbers. Pairs of data can be added to the
set in any order and be sorted using which ever data item in the pair
is defined as the index.
- The elements in an associative array are sorted in ascending order
of index values. The insertion order of elements does not matter.
- Associative array data can be accessed and set using direct references
or by using a set of available scalar functions.
- Associative arrays are supported in SQL PL contexts.
- Associative arrays can be used to manage and pass sets of values
of the same kind in the form of a collection instead of having to:
- Reduce the data to scalar values and use one-element-at-a-time
processing which can cause network traffic problems.
- Use cursors passed as parameters.
- Reduce the data into scalar values and reconstitute them as a
set using a VALUES clause.