A collection is a set of ordered data items with the same data type. Generally, the data item is a scalar field, but may also be a user-defined type such as a record type or an object type as long as the structure and the data types that comprise each field of the user-defined type are the same for each element in the set. Each particular data item in the set is referenced by using subscript notation within a pair of parentheses.
Multilevel collections (that is, where the data item of a collection is another collection) are not supported.
The most commonly known type of collection is an array. In PolarDB for PostgreSQL (Compatible with Oracle), the supported collection types are associative arrays (formerly called index-by-tables in Oracle), nested tables, and varrays.
The general steps for using a collection are as follows:
A collection of the desired type must be defined. This can be done in the declaration topic of an SPL program, which results in a local type that is accessible only within that program. For nested table and varray types, this can also be done by using the CREATETYPE statement, which creates a persistent standalone type that can be referenced by any SPL program in the database.
Variables of the collection type are declared. The collection associated with the declared variable is said to be uninitialized at this point if no value assignment is made as part of the variable declaration.
Uninitialized collections of nested tables and varrays are null. A null collection does not yet exist. Generally, a COLLECTION_IS_NULL exception is thrown if a collection method is invoked on a null collection.
Uninitialized collections of associative arrays exist but have no elements. An existing collection with no elements is called an empty collection.
To initialize a null collection, you must either make it an empty collection or assign a non-null value to it. Generally, a null collection is initialized by using its constructor.
To add elements to an empty associative array, you can simply assign values to its keys. For nested tables and varrays, generally its constructor is used to assign initial values to the nested table or varray. For nested tables and varrays, the EXTEND method is then used to grow the collection beyond its initial size established by the constructor.
The specific process for each collection type is described in the following topics.