Understanding PL/SQL Collections: Associative Arrays, Nested Tables, and VARRAYs

In the last blog post, I discussed a record, which is a composite data type. In this blog, I’ll talk about another composite data type: a collection.

According to Oracle documentation, “A collection is an ordered group of data elements, all of the same type.” A collection is a data structure similar to a list or array in other programming languages.

PL/SQL supports three types of collections: Associative arrays, Nested tables, and Varrays.

Associative Arrays
These were called index-by tables in Oracle8 and Oracle8i databases. Associative arrays use arbitrary numbers or strings to look up elements, much like hash tables. They are *single-dimensional, *unbounded, and sparse collections. Associative arrays are available only in PL/SQL.

Nested Tables
Nested tables are also single-dimensional, unbounded collections of elements. They must be initially bounded but are allowed to be sparse, such as through deletion. Nested tables can be stored in database tables and manipulated through SQL.

VARRAYs
VARRAYs (variable-sized arrays) are single-dimensional, but they are always bounded and never sparse. VARRAYs can be used both in PL/SQL and in a database.

*single-dimensional: A PL/SQL collection always has just a single column of information in each row.

*bounded and unbounded: A collection is bounded if it has predetermined limits for the number of rows. An unbounded collection has no upper or lower row limits.

Let’s take a closer look at each type.

Associative Arrays
Syntax (to define a collection type):

TYPE type_name IS TABLE OF element_type [NOT NULL]

    INDEX BY [BINARY_INTEGER | PLS_INTEGER | VARCHAR2(size_limit)];

    INDEX BY key_type;

The key_type can be either numeric or string. As they were previously called index-by tables, elements can be looked up by key-value pairs, which can save code and time because they don’t require loops. However, associative arrays are intended to be used only for temporary storage. To make an associative array persistent throughout the life of a database session, declare it in a package specification and populate it in the package body. Note that associative arrays cannot be used with SQL operations, such as INSERT or SELECT INTO.

Nested Tables
Syntax (to define a collection type):

TYPE type_name IS TABLE OF element_type [NOT NULL];

Here, type_name is a type specifier used later to declare collections.

To store nested tables in database tables, you must declare SQL types.
Syntax (for SQL):

CREATE TYPE type_name IS TABLE OF element_type;

VARRAYs
Syntax:

TYPE type_name IS {VARRAY | VARYING ARRAY} (size_limit)

    OF element_type [NOT NULL];

Where type_name is a type specifier used later to declare collections, and size_limit must be specified to indicate the maximum size.

To store VARRAYs in database tables, you must declare SQL types.
Syntax (for SQL):

CREATE TYPE type_name IS TABLE OF element_type;

Collection Constructors
A collection constructor is a system-defined function with the same name as a collection type.

When a collection has no elements during initialization, it is considered an empty collection. However, a null collection does not exist, so it must be initialized --- either as empty or with non-NULL values.

Syntax:

collection_type([value [, value] ... ])

If the parameter list is empty, the constructor returns an empty collection. Otherwise, the constructor returns a collection containing the specified values.

Example (from a documentation):

TYPE Foursome IS VARRAY(4) OF VARCHAR2(15);
team Foursome := Foursome();

VARRAYs and Nested Tables must be initialized; otherwise, they are null collections.
Associative arrays are considered empty, not null, until populated.

When to Use Which Type

Associative Arrays:

• When you need a relatively small lookup table.
• When passing collections to and from the database server.

Varrays:

• When you know the maximum number of elements.
• When you usually access the elements sequentially.

Nested Tables:

• When the number of elements is not fixed.
• When the index values are not consecutive.
• When you need to delete or update some elements, but not all elements simultaneously.
• When you would create a separate lookup table with multiple entries for each row of the main table, and access it through join queries.

The Difference Between a Collection and a Record
In a collection, the internal components, called elements, are always the same data type:

variable_name(index)

In a record, the internal components, called fields, can have different data types.

variable

These are the fundamentals of collections. I’ll dive deeper into collections and share more details in the next blog post.