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 ::=

• datatype ::=

nested_table_type_def ::=

See element_type ::=.

varray_type_def ::=

See element_type ::=.

collection_variable_dec ::=

element_type ::=

Semantics

collection_type_definition

type_name

The name of the collection type that you are defining.

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.

nested_table_type_def

The type definition for a nested table.

varray_type_def

The type definition for a variable-size array.

assoc_array_type_def

element_type

The data type of the elements of the associative array—any PL/SQL data type except REF CURSOR.

NOT NULL

Specifies that no element of the associative array can have the value NULL.

PLS_INTEGER

Specifies that the data type of the indexes of the associative array is PLS_INTEGER.

BINARY_INTEGER

Specifies that the data type of the indexes of the associative array is BINARY_INTEGER.

VARCHAR2 (v_size)

Specifies that the data type of the indexes of the associative array is VARCHAR2 with length v_size.

data_type

Specifies that the data type of the indexes of the associative array is a data type specified with either %ROWTYPE or %TYPE. This data type must represent either PLS_INTEGER, BINARY_INTEGER, or VARCHAR2(v_size).

nested_table_type_def

element_type

The data type of the elements of the nested table—any PL/SQL data type except REF CURSOR or NCLOB.

If element_type is a scalar type, then the nested table has a single, scalar type column called COLUMN_VALUE.

If element_type is an ADT, then the columns of the nested table match the name and attributes of the ADT.

NOT NULL

Specifies that no nested table element can have the value NULL.

varray_type_def

size_limit

The maximum number of elements that the varray can have—an integer literal in the range from 1 through 2147483647.

element_type

The data type of the associative array element—any PL/SQL data type except REF CURSOR.

NOT NULL

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

collection_variable_dec

collection_name

The name of the collection variable that you are declaring.

type_name

The type of the collection variable that you are defining.

element_type

cursor_name

The name of an explicit cursor.

db_table_name

The name of a database table or view, which must be accessible when the collection definition is elaborated.

column_name

The name of a column of the database table or view db_table_name.

object_name

An instance of a user-defined type.

[ REF ] object_type_name

The name of a user-defined type.

record_name

The name of a record.

field_name

The name of a field of the record record_name.

record_type_name

The name of a user-defined type that was defined with the data type specifier RECORD.

scalar_datatype_name

The name of a predefined scalar data type, including any qualifiers for size, precision, and character or byte semantics. For information about predefined scalar data types, see "Predefined PL/SQL Scalar Data Types and Subtypes".

variable_name

The name of a variable.

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"

• "%ROWTYPE Attribute"

• "%TYPE Attribute"

In other chapters:

• "Collection Topics"

• "Retrieving Query Results into Collections"

• "CREATE TYPE Statement"