Collections

A collection is a composite data structure that acts like a list or a single-dimensional array (traditional arrays are not supported in PL/SQL).

There are three different types of collections in PL/SQL: associative arrays (called index-by tables in Oracle8 and Oracle8i and PL/SQL tables before that), nested tables, and VARRAYs (variable arrays). The following sections briefly describe the declarations for these different types, but for information about using and comparing these collection types, see Section 9.4.11 under Section 9.4 later in this chapter. Table 9-2 compares the three collection types.

Collections are implemented as TYPEs. Like any programmer-defined type, you must first define the type; then you can declare instances of that type. The TYPE definition can be stored in the database or declared in the PL/SQL program. Each instance of the TYPE is a collection.

Table 9-2. Comparison of collection types

Characteristic

Associative array

Nested table

VARRAY

Dimensionality

Single

Single

Single

Usable in SQL?

No

Yes

Yes

Usable as a column datatype in a table?

No

Yes; data stored “out of line” (in a separate table)

Yes; data typically stored “in line” (in the same table)

Uninitialized state

Empty (cannot be NULL); elements are undefined

Atomically null; illegal to reference elements

Atomically null; illegal to reference elements

Initialization

Automatic, when declared

Via constructor, fetch, assignment

Via constructor, fetch, assignment ...

Get Oracle in a Nutshell now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.