Definition

Many programming techniques use collection types. To support these techniques in database applications, PL/SQL provides three data types of collections:

Collection Type Number of Elements Indexed by (Subscript Type) Dense or Sparse Where Created Can Be Object Type Attribute
Associative array (or index-by table) Unbounded String or integer Either Only in PL/SQL block No
Nested table Unbounded Integer Starts dense, can become sparse Either in PL/SQL block or at schema level Yes
Variable-size array (varray) Bounded Integer Always dense Either in PL/SQL block or at schema level Yes

A collection is an ordered group of elements, all of the same datatype whereas

Records contain uniquely named fields, which can have different datatypes.

. Each element has a unique subscript that determines its position in the collection.

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

Language structure Mapping:

In other languages In PLSQL
Arrays varrays
Sets and bags nested tables
Hash tables and other unordered tables associative arrays

Syntax

Database PL/SQL Language Reference - Collections

Memory

Specifically, memory for collections is stored in the program global area (PGA), not the system global area (SGA). SGA memory is shared by all sessions connected to Oracle Database, but PGA memory is allocated for each session. Thus, if a program requires 5MB of memory to populate a collection and there are 100 simultaneous connections, that program causes the consumption of 500MB of PGA memory, in addition to the memory allocated to the SGA.

Management

Using Collection Methods :

  • Checking If a Collection Element Exists (EXISTS Method)
  • Counting the Elements in a Collection (COUNT Method)
  • Checking the Maximum Size of a Collection (LIMIT Method)
  • Finding the First or Last Collection Element (FIRST and LAST Methods)
  • Looping Through Collection Elements (PRIOR and NEXT Methods)
  • Increasing the Size of a Collection (EXTEND Method)
  • Decreasing the Size of a Collection (TRIM Method)
  • Deleting Collection Elements (DELETE Method)
  • Applying Methods to Collection Parameters

Create

To create a collection, you first define a collection type, and then declare a variable of that type.

Get

Every reference to an element includes a collection name and a subscript enclosed in parentheses. 

collection_name (subscript)

where subscript is an expression that yields:

  • an integer in most cases,
  • or a VARCHAR2 for associative arrays declared with strings as keys.

Set

One collection can be assigned to another by:

  • an INSERT, UPDATE, FETCH, or SELECT statement,
  • an assignment statement,
  • or a subprogram call.

Assignment statement 

collection_name (subscript) := expression;

Merge (MULTISET )

Several operator permits to merge two collections:

  • MULTISET UNION
  • MULTISET INTERSECT
  • MULTISET INTERSECT DISTINCT
  • MULTISET EXCEPT
  • MULTISET EXCEPT DISTINCT

In SqlPlus: 

set serveroutput on;
DECLARE
    type t_tabOfInteger is table of Integer;
    l_Integers1 t_tabOfInteger := t_tabOfInteger(1,2,3);
    l_Integers2 t_tabOfInteger := t_tabOfInteger(3,4);
BEGIN
    DBMS_OUTPUT.PUT_LINE('l_Integers1 count: ' || l_Integers1.COUNT );
    l_Integers1 := l_Integers1 MULTISET UNION  l_Integers2;
    DBMS_OUTPUT.PUT_LINE('l_Integers1 count after multiset: ' || l_Integers1.COUNT );
END;

l_Integers1 count: 2
l_Integers1 count after multiset: 4

SET

Set is a collection operator that suppress the duplicate of a collection (Same as a distinct for SQL)

Remove

  • Trim n.TRIM(2) remove the last 2 elements

Built-in Exception

Collection Exception Raised when…
COLLECTION_IS_NULL you try to operate on an atomically null collection.
NO_DATA_FOUND a subscript designates an element that was deleted, or a nonexistent element of an associative array.
SUBSCRIPT_BEYOND_COUNT a subscript exceeds the number of elements in a collection.
SUBSCRIPT_OUTSIDE_LIMIT a subscript is outside the allowed range.
VALUE_ERROR a subscript is null or not convertible to the key type. This exception might occur if the key is defined as a PLS_INTEGER range, and the subscript is outside this range.

Reference