Collection

A collection groups elements of the same type in a specified order. Each element has a unique subscript that determines its position in the collection.

PL/SQL has three kinds of collections:

• Associative arrays (formerly called "PL/SQL tables" or "index-by tables")

• Nested tables

• Variable-size arrays (varrays)

Associative arrays can be indexed by either integers or strings. Nested tables and varrays are indexed by integers.

To create a collection, you first define a collection type, and then declare a variable of that type.

Topics:

• Syntax

• Semantics

• Examples

• Related Topics

Syntax

collection_type_definition ::=

assoc_array_type_def ::=

See element_type ::=.

nested_table_type_def ::=

See element_type ::=.

varray_type_def ::=

See element_type ::=.

collection_variable_dec ::=

element_type ::=

Semantics

assoc_array_type_def

The type definition for an associative array.

Restriction on assoc_array_type_def The type definition of an associative array can appear only in the declarative part of a block, subprogram, package specification, or package body.

collection_name

The name of the collection variable that you are declaring.

data_type

A data type specified with either %ROWTYPE or %TYPE that represents either PLS_INTEGER, BINARY_INTEGER, or VARCHAR2(v_size). See datatype ::=.

element_type

The data type of the collection element (any PL/SQL data type except REF CURSOR).

In nested_table_type_def:

• element_type cannot be NCLOB.

• If element_type is an ADT, then nested_table_type_def describes a table whose columns match the name and attributes of the ADT.

• If element_type is a scalar type, then nested_table_type_def describes a table with a single, scalar type column called COLUMN_VALUE.

INDEX BY

Specifies the data type of the indexes of the associative array—PLS_INTEGER, BINARY_INTEGER, VARCHAR2(v_size) or data_type.

nested_table_type_def

The type definition for a nested table.

NOT NULL

Specifies that no element of the collection can have the value NULL.

size_limit

A positive integer literal that specifies the maximum number of elements that the varray can have. The maximum value of size_limit is 2147483647.

type_name

The name of the collection type that you are defining.

v_size

The length of the VARCHAR2 key by which the associative array is indexed.

varray_type_def

The type definition for a variable-size array.

Examples

• Example 5-1, "Declaring and Populating Associative Array Indexed by String"

• Example 5-3, "Declaring Nested Tables, Varrays, and Associative Arrays"

• Example 5-4, "Declaring Collections with %TYPE"

• Example 5-5, "Declaring a Procedure Parameter as a Nested Table"

• Example 5-42, "Declaring and Initializing Record Types"

Related Topics

In this chapter:

• "Collection Method Call"

• "FORALL Statement"

• "Record"

In other chapters:

• "Collection Topics"

• "Retrieving Query Results into Collections"

• "CREATE TYPE Statement"