PolarDB:Collection types

PL/SQL provides three types of collections: variable-size arrays (varrays), nested tables, and associative arrays. The collection types can be declared at the schema level, in packages, or PL/SQL blocks. Each collection type has special usage. For example, you can use collection type constructor functions to initialize varrays and nested tables, use collection methods to add, remove, or query collections, take values from collections, assign values to collections, and use collections to interact with tables.

The following table describes the differences among the preceding collection types.

Number of elements

If the number of elements is specified, the value is the maximum number of elements in the collection. If the number of elements is not specified, the maximum number of elements in the collection is the upper limit of the index type.

Index (subscript) type

An index is used to obtain the element at a specific position of a collection type.

Uninitialized state

The preceding collection types have three states: NULL, empty, and populated. Varrays and nested tables can be in any of the preceding states. When a varray or nested table variable is declared but not initialized, the status of the variable is NULL, which indicates that the collection specified by the variable does not exist. In this case, if you want to obtain an element from the collection variable by using an index, an error indicating that the collection does not exist is reported. You can invoke a constructor function to initialize the collection variable. In this case, the collection variable enters the empty state, which indicates that the collection exists but does not contain elements. In this case, if you want to obtain an element from the collection variable by using an index, an error indicating that the element does not exist is reported. By default, when an associative array variable is declared, the variable is initialized to the empty state. Therefore, the variable does not have the NULL state.

Where defined

The collection type defined in a PL/SQL block is a local type. The collection type is available only in the block, and is stored in the database only if the block is in a subprogram. The collection type defined at the schema level or in a package is persistently stored in the system tables of a database. If you want to delete the collection type, you must run the DROP TYPE or DROP PACKAGE command.

Limits

• Associative array initialization constructors are not supported.

• Construction forms for qualified expressions and iterator are not supported.

• Multidimensional collection types are not supported. The multilevel nesting of collection types and record types is also not supported.

• MULTISET-related operations are not supported.

• When you use the DELETE collection method to delete elements from a collection variable but retain placeholders, do not use the collection variable and tables to perform operations to prevent unexpected results.

• You can use IS [NOT] NULL to determine whether a collection variable is in the NULL state. However, if you use the =, <, or > operator to compare the equality or inequality between collection variables, the returned results are meaningless.