Collection in Oracle PL/SQL

What is a PL/SQL collection?

A collections is an ordered group of elements, all of the same types. It is like arrays in other programming languages. 

The main purpose of using collection is to improve application performance. By using collections, we can 'cache' static data that needs to be accessed frequently and need to be modified. This results in reduced calls to a database.

Types of collections:

There are 3 types in collection.

1)             Index-by tables or Associative arrays

2)             Nested tables

3)            Varrays

Index –by tables or Associative arrays:

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.

Syntax:

TYPE type_name IS TABLE OF element_type [NOT NULL] INDEX BY subscript_type;

Example:

Nested tables:

PL/SQL nested tables are like one-dimensional arrays and can be considered as one-column database tables. It is extension of the index-by tables.

You can create multi-dimensional arrays by creating nested tables whose elements are also nested tables.

Syntax:

TYPE type_name IS TABLE OF element_type [NOT NULL];

Example:

The main difference between Associative array and Nested Table :

·         Nested tables can be stored in a database column but associative arrays  cannot.

·         Some collection methods cannot be used in Associative arrays.

·         You can compare 2 Nested tables by using = or <> operator but associative arrays are not.

Varrays:

Varrays are similar to Nested tables but should have specified upper bound.

Syntax:

TYPE Varray_Type IS VARRAY(size) OF Element_Type;

Example:

The main difference between VARRAY and Nested Table :

·         Varray should have specified upper bound, for Nested tables  there is no specified upper bound .

·         The elements from Nested tables can be deleted using DELETE method but Varrays are not .

·         Oracle stores the rows of a nested table in no particular order but Varrays are stored in particular order .

Collection Methods:

A collection method is a built-in function or procedure that operates on collections and is called using dot notation.

Syntax :

collection_name.method_name[(parameters)]

The collection methods are :

EXISTS

COUNT

LIMIT

FIRST and LAST

PRIOR and NEXT

EXTEND

TRIM

DELETE