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.
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.
collection_type_definition ::=
Description of the illustration collection_type_def.gif
Description of the illustration assoc_array_type_def.gif
Description of the illustration nested_table_type_def.gif
Description of the illustration varray_type_def.gif
Description of the illustration collection_variable_dec.gif
Description of the illustration element_type_definition.gif
Keyword and Parameter Descriptions
The name that you give to the variable of the collection type that you defined.
The data type of the collection element (any PL/SQL data type except REF CURSOR).
For a nested table:
-
If
element_typeis an object type, then the nested table type describes a table whose columns match the name and attributes of the object type. -
If
element_typeis a scalar type, then the nested table type describes a table with a single, scalar type column calledCOLUMN_VALUE. -
You cannot specify
NCLOBforelement_type. However, you can specifyCLOBorBLOB.
For an associative array, the data type of its indexes—PLS_INTEGER, BINARY_INTGER, or VARCHAR2.
Specifies that no element of the collection can have the value NULL.
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.
The name that you give to the collection type that you are defining.
For an associative array, the length of the VARCHAR2 key by which it is indexed.
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.