Working with Collections

 An Oracle PL/SQL collection is a single-dimensional array; it consists of one or more elements accessible through an index value. 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, also known as index-by tables, let you look up elements using arbitrary numbers and strings for subscript values. These are similar to hash tables in other programming languages.

Nested tables hold an arbitrary number of elements. They use sequential numbers as subscripts. You can define equivalent SQL types, allowing nested tables to be stored in database tables and manipulated through SQL.

Varrays (short for variable-size arrays) hold a fixed number of elements (although you can change the number of elements at runtime). They use sequential numbers as subscripts. You can define equivalent SQL types, allowing varrays to be stored in database tables. They can be stored and retrieved through SQL, but with less flexibility than nested tables.

To use collections in an application, you define one or more PL/SQL types, then define variables of those types. You can define collection types in a procedure, function, or package. You can pass collection variables as parameters to stored subprograms.

To look up data that is more complex than single values, you can store PL/SQL records or SQL object types in collections. Nested tables and varrays can also be attributes of object types.

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 ::=
TYPE type_name IS 
{ assoc_array_type_def
| nested_table_type_def
| varray_type_def
} ;

assoc_array_type_def ::=
TABLE OF element_type [ NOT NULL ]
[ INDEX BY { PLS_INTEGER | BINARY_INTEGER | VARCHAR2 ( v_size ) } ]

nested_table_type_def ::=
TABLE OF element_type [ NOT NULL ]

varray_type_def ::=
{ VARRAY | VARYING ARRAY } ( size_limit )
OF element_type [ NOT NULL ]

collection_variable_dec ::=
collection_name type_name ;

element_type ::=
{ cursor_name%ROWTYPE
| db_table_name{%ROWTYPE | .column_name%TYPE}
| object_name%TYPE
| [REF] object_type_name
| scalar_datatype_name
| record_name[.field_name]%TYPE
| record_type_name
| variable_name%TYPE
}

Keyword and Parameter Description

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:

• If element_type is an object type, then the nested table type describes a table whose columns match the name and attributes of the object type.
• If element_type is a scalar type, then the nested table type describes a table with a single, scalar type column called COLUMN_VALUE.
• You cannot specify NCLOB for element_type. However, you can specify CLOB or BLOB.

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.

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.

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

Understanding Associative Arrays (Index-By Tables)
============================================
Associative arrays are sets of key-value pairs, where each key is unique and is used to locate a corresponding value in the array. The key can be an integer or a string.

Assigning a value using a key for the first time adds that key to the associative array. Subsequent assignments using the same key update the same entry. It is important to choose a key that is unique. For example, key values might come from the primary key of a database table, from a numeric hash function, or from concatenating strings to form a unique string value.

Declaring Index-By-Table Collection Type:
DECLARE  TYPE population_type IS TABLE OF NUMBER INDEX BY VARCHAR2(64);
  country_population population_type;
  continent_population population_type;
  howmany NUMBER;
  which VARCHAR2(64);
BEGIN
  country_population('Greenland') := 100000; -- Creates new entry
  country_population('Iceland') := 750000;   -- Creates new entry
-- Looks up value associated with a string
  howmany := country_population('Greenland');
  continent_population('Australia') := 30000000;
  continent_population('Antarctica') := 1000; -- Creates new entry
  continent_population('Antarctica') := 1001; -- Replaces previous value 
-- Returns 'Antarctica' as that comes first alphabetically.
  which := continent_population.FIRST;
-- Returns 'Australia' as that comes last alphabetically.  which := continent_population.LAST;
-- Returns the value corresponding to the last key, in this
-- case the population of Australia.
  howmany := continent_population(continent_population.LAST);
END;
/

Associative arrays help you represent data sets of arbitrary size, with fast lookup for an individual element without knowing its position within the array and without having to loop through all the array elements. It is like a simple version of a SQL table where you can retrieve values based on the primary key. For simple temporary storage of lookup data, associative arrays let you avoid using the disk space and network operations required for SQL tables.

Because associative arrays are intended for temporary data rather than storing persistent data, you cannot use them with SQL statements such as INSERT and SELECT INTO. You can make them persistent for the life of a database session by declaring the type in a package and assigning the values in a package body.

Understanding Nested Tables

=========================

PL/SQL nested tables represent sets of values. You can think of them as one-dimensional arrays with no declared number of elements. You can model multi-dimensional arrays by creating nested tables whose elements are also nested tables.
Within the database, nested tables are column types that hold sets of values. Oracle stores the rows of a nested table in no particular order. When you retrieve a nested table from the database into a PL/SQL variable, the rows are given consecutive subscripts starting at 1. That gives you array-like access to individual rows.

Declaring Nested-Table Collection Type:

1  DECLARE

  2     TYPE list_of_names_t IS TABLE OF VARCHAR2 (100);
3

    happyfamily   list_of_names_t := list_of_names_t ();
  5
4
      children      list_of_names_t := list_of_names_t ();

;
  7  BEGIN
  8     happyfamily.EXTEND (4);
  9     happyfa
6     parents       list_of_names_t := list_of_names_t (
)mily (1) := ‘Veva’;
 10     happyfamily (2) := ‘Chris’;
 11     happyfamily (3) := ‘Eli’;

n.LAST) := ‘Chris’;
 16     children
12     happyfamily (4) := ‘Steven’;
 13
 14     children.EXTEND;
 15     children (childr
e.EXTEND;
 17     children (children.LAST) := ‘Eli’;
 18
 19     parents := happyfamily MULTISET EXCEPT children;
 20

OP;
 25  END;
21     FOR l_row IN 1 .. parents.COUNT
 22     LOOP
 23        DBMS_OUTPUT.put_line (parents (l_row));
 24     END L

O

Nested tables differ from arrays in two important ways:

Nested tables do not have a declared number of elements, while arrays have a predefined number as illustrated in Figure below. The size of a nested table can increase dynamically; however, a maximum limit is imposed. 
Nested tables might not have consecutive subscripts, while arrays are always dense (have consecutive subscripts). Initially, nested tables are dense, but they can become sparse (have nonconsecutive subscripts). You can delete elements from a nested table using the built-in procedure DELETE. The built-in function NEXT lets you iterate over all the subscripts of a nested table, even if the sequence has gaps.

Understanding Varrays

====================

Items of type VARRAY are called varrays. They let you reference individual elements for array operations, or manipulate the collection as a whole. To reference an element, you use standard subscripting syntax (see Figure below). For example, Grade(3) references the third element in varray Grades.
A varray has a maximum size, which you specify in its type definition. Its index has a fixed lower bound of 1 and an extensible upper bound. For example, the current upper bound for varray Grades is 7, but you can increase its upper bound to maximum of 10. A varray can contain a varying number of elements, from zero (when empty) to the maximum specified in its type definition.

Declare a varray of numbers:

TYPE numbers_vat IS VARRAY(10) 

OF NUMBER; 

Declare a varray of Records:

DECLARE TYPE name_rec

  IS RECORD ( first_name VARCHAR2(20), last_name VARCHAR2(25));

   TYPE names IS VARRAY(250) OF name_rec;

BEGIN

   NULL;

END;

/

Initializes a varray using a constructor, which looks like a function with the same name as the collection type:

DECLARE

-- In the varray, we put an upper limit on the number of elements

   TYPE dnames_var IS VARRAY(20) OF VARCHAR2(30);

   dept_names dnames_var;

BEGIN

-- Because dnames is declared as VARRAY(20), we can put up to 10-- elements in the constructor

   dept_names := dnames_var('Shipping','Sales','Finance','Payroll');

END;

/

Choosing Between Nested Tables and Associative Arrays

Nested tables and associative arrays differ in persistence and ease of parameter passing.

A nested table can be stored in a database column; therefore, you can use a nested table to simplify SQL operations in which you join a single-column table with a larger table. An associative array cannot be stored in the database.

An associative array is appropriate for the following:

• A relatively small lookup table, where the collection can be constructed in memory each time a subprogram is invoked or a package is initialized
• Passing collections to and from the database server
  PL/SQL automatically converts between host arrays and associative arrays that use numeric key values. The most efficient way to pass collections to and from the database server is to set up data values in associative arrays, and then use those associative arrays with bulk constructs (the FORALLstatement or BULK COLLECT clause).

Choosing Between Nested Tables and Varrays

Varrays are a good choice when:

• The number of elements is known in advance.
• The elements are usually accessed sequentially.

When stored in the database, varrays keep their ordering and subscripts.

A varray is stored as a single object. If a varray is less than 4 KB, it is stored inside the table of which it is a column; otherwise, it is stored outside the table but in the same tablespace.

You must store or retrieve all elements of a varray at the same time, which is appropriate when operating on all the elements at once. However, this might be impractical for large numbers of elements.

Nested tables are a good choice when:

• Index values are not consecutive.
• There is no set number of index values.
• You must delete or update some elements, but not all elements at once.
• You would create a separate lookup table, with multiple entries for each row of the main table, and access it through join queries.

Nested table data is stored in a separate store table, a system-generated database table. When you access a nested table, the database joins the nested table with its store table. This makes nested tables suitable for queries and updates that only affect some elements of the collection.

You cannot rely on the order and subscripts of a nested table remaining stable as the nested table is stored in and retrieved from the database, because the order and subscripts are not preserved in the database.