Collection In PL/SQL

An Oracle PL/SQL collection is a single-dimensional array; it consists of one or more elements accessible through an index value. Collections are used in some of the most important performance optimization features of PL/SQL, such as BULK COLLECT.(Collection is a group of element of same type.Each element in the list has a unique position number or label we called as "subscript".).

PL/SQL provides three collection types :

1. PL/SQL Table or Index-by tables or Associative array
2. Nested table
3. Variable-size array or Varray.

Syntax :

1. Collection Type Definition:

TYPE type_name IS 
{ assoc_array_type_def|nested_table_type_def| varray_type_def} ;

2. assoc_array_type_def:

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

3. nested_table_type_def:

TABLE OF element_type [ NOT NULL ]

4. varray_type_def:

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

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

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

6. Collection Variable Declarations:

collection_name  type_name;

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


Here are some guidelines when we need to use which collection:

Use index by tables when:

• Your program needs small lookups.
• The collection can be made at run time in the memory when the package or procedure is initialized.
• The data volume is unknown beforehand.
• The subscript values are flexible (e.g. strings, negative numbers, non-sequential).
• You do not need to store the collection in the database.

Use nested tables when:

• The data needs to be stored in the database
• The number of elements in the collection is not known in advance
• The elements of the collection may need to be retrieved out of sequence
• Updates and deletions affect only some elements, at arbitrary locations
• Your program does not expect to rely on the subscript remaining stable, as their order may change 
• when nested tables are stored in the database.

Use varrays when:

• The data needs to be stored in the database.
• The number of elements of the varray is known in advance.
• The data from the varray is accessed in sequence.
• Updates and deletions happen on the varray as a whole and not on arbitrarily located elements in the varray.

Check Associative Array(Index by Table) Vs Nested Table Vs VARRAY

Examples:
Check three type of collection use in program

DECLARE
   TYPE nested_type IS TABLE OF VARCHAR2(30);
   TYPE varray_type IS VARRAY(5) OF INTEGER;
   TYPE assoc_array_num_type IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
   TYPE assoc_array_str_type IS TABLE OF VARCHAR2(32) INDEX BY PLS_INTEGER;
   TYPE assoc_array_str_type2 IS TABLE OF VARCHAR2(32) INDEX BY VARCHAR2(64);
   v1 nested_type;
   v2 varray_type;
   v3 assoc_array_num_type;
   v4 assoc_array_str_type;
   v5 assoc_array_str_type2;
BEGIN
-- an arbitrary number of strings can be inserted v1
   v1 := nested_type('Shipping','Sales','Finance','Payroll'); 
   v2 := varray_type(1, 2, 3, 4, 5); -- Up to 5 integers
   v3(99) := 10; -- Just start assigning to elements
   v3(7) := 100; -- Subscripts can be any integer values
   v4(42) := 'Smith'; -- Just start assigning to elements
   v4(54) := 'Jones'; -- Subscripts can be any integer values
   v5('Canada') := 'North America'; -- Just start assigning to elements
   v5('Greece') := 'Europe';        -- Subscripts can be string values
END;
/

Check All The Collection Methods

Check Collection Exceptions

Difference Between Collection and Record

Interview Questions