Skip Headers
Oracle® Database PL/SQL Language Reference
11g Release 1 (11.1)

B28370-05
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

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 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.

Note:

This topic applies to collection types that you define inside a PL/SQL block or package, which are different from standalone stored collection types that you create with the CREATE TYPE Statement.

In a PL/SQL block or package, you can define all three collection types. With the CREATE TYPE statement, you can create nested table types and varray types, but not associative array types.

Syntax

collection_type_definition ::=

collection_type_definition
Description of the illustration collection_type_def.gif

assoc_array_type_def ::=

assoc_array_type_def
Description of the illustration assoc_array_type_def.gif

(element_type ::=)

nested_table_type_def ::=

nested_table_type_def
Description of the illustration nested_table_type_def.gif

(element_type ::=)

varray_type_def ::=

varray_type_def
Description of the illustration varray_type_def.gif

(element_type ::=)

collection_variable_dec ::=

collection_variable_dec
Description of the illustration collection_variable_dec.gif

element_type ::=

element_type
Description of the illustration element_type_definition.gif

Keyword and Parameter Descriptions

collection_name

The name that you give to the variable of the collection type that you defined.

element_type

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

For a nested table:

INDEX BY

For an associative array, the data type of its indexes—PLS_INTEGER, BINARY_INTGER, or VARCHAR2.

NOT NULL

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

size_limit

For a varray, a positive integer literal that specifies the maximum number of elements it can contain. A maximum limit is imposed. See Referencing Collection Elements.

type_name

The name that you give to the collection type that you are defining.

v_size

For an associative array, the length of the VARCHAR2 key by which it is indexed.

Usage Notes

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

The type definition of a nested table or varray can appear either in the declarative part of a block, subprogram, package specification, or package body (in which case it is local to the block, subprogram, or package) or in the CREATE TYPE Statement (in which case it is a standalone stored type).

Nested tables extend the functionality of associative arrays, so they differ in several ways. See Choosing Between Nested Tables and Associative Arrays.

Nested tables and varrays can store instances of an object type and, conversely, can be attributes of an object type.

Collections work like the arrays of most third-generation programming languages. A collection has only one dimension. To model a multidimensional array, declare a collection whose items are other collections.

Collections can be passed as parameters. You can use them to move columns of data into and out of database tables or between client-side applications and stored subprograms.

Every element reference includes the collection name and one or more subscripts enclosed in parentheses; the subscripts determine which element is processed. Except for associative arrays, which can have negative subscripts, collection subscripts have a fixed lower bound of 1. Subscripts for multilevel collections are evaluated in any order; if a subscript includes an expression that modifies the value of a different subscript, the result is undefined. See Referencing Collection Elements.

Associative arrays and nested tables can be sparse (have nonconsecutive subscripts), but varrays are always dense (have consecutive subscripts). Unlike nested tables, varrays retain their ordering and subscripts when stored in the database. Initially, associative arrays are sparse. That enables you, for example, to store reference data in a temporary variable using a primary key (account numbers or employee numbers for example) as the index.

Collections follow the usual scoping and instantiation rules. In a package, collections are instantiated when you first reference the package and cease to exist when you end the database session. In a block or subprogram, local collections are instantiated when you enter the block or subprogram and cease to exist when you exit.

Until you initialize it, a nested table or varray is atomically null (that is, the collection itself is null, not its elements). To initialize a nested table or varray, you use a constructor, which is a system-defined function with the same name as the collection type. This function constructs (creates) a collection from the elements passed to it.

For information about collection comparisons that are allowed, see Comparing Collections.

Collections can store instances of an object type and, conversely, can be attributes of an object type. Collections can also be passed as parameters. You can use them to move columns of data into and out of database tables or between client-side applications and stored subprograms.

When invoking a function that returns a collection, you use the following syntax to reference elements in the collection:

function_name(parameter_list)(subscript)

See Example 5-16, "Referencing an Element of an Associative Array" and Example B-2, "Using the Dot Notation to Qualify Names".

With the Oracle Call Interface (OCI) or the Oracle Precompilers, you can bind host arrays to associative arrays (index-by tables) declared as the formal parameters of a subprogram. That lets you pass host arrays to stored functions and procedures.

Examples

Related Topics