PL/SQL Collections | Best Explanation

Here's the best explanation of PL/SQL Collections that covers all the essential aspects, terminologies, usage scenarios, and examples to give you a comprehensive understanding.

---

PL/SQL Collections: The Ultimate Guide

In Oracle PL/SQL, collections are powerful, complex data types that allow developers to work with multiple data items as a single entity. These are extremely useful when handling large sets of data or when performing operations that involve multiple rows or values at once.

PL/SQL collections can be thought of as "containers" that hold multiple items of the same type. They offer flexibility and efficiency when working with large datasets, particularly when combined with Bulk Collect and FORALL operations for performance optimization.

Types of PL/SQL Collections

There are three main types of collections in PL/SQL:

1. Associative Arrays (Index-By Tables)

2. Nested Tables

3. Varrays (Variable-Size Arrays)

---

1. Associative Arrays (Index-By Tables)

Definition: An Associative Array (also called Index-By Table) is a collection of key-value pairs where each key is an index (either a number or a string) and the value is the data. It is similar to a map or dictionary in other programming languages.

Key Characteristics:

Index can be integer or string.

Flexible in size and can be sparse (i.e., non-contiguous indices).

The index does not need to be sequential.

Use Case: Associative arrays are ideal when you need to look up data by a key (like a dictionary) and do not require a contiguous range of indices.

Example:

DECLARE
TYPE assoc_array IS TABLE OF VARCHAR2(100) INDEX BY PLS_INTEGER;
my_array assoc_array;
BEGIN
my_array(1) := 'First Element';
my_array(5) := 'Second Element';

DBMS_OUTPUT.PUT_LINE(my_array(1)); -- Output: First Element
DBMS_OUTPUT.PUT_LINE(my_array(5)); -- Output: Second Element
END;

Terminology:

Index: Key used to identify each element in the associative array.

Element: The data stored in the collection.

---

1. Nested Tables

Definition: A Nested Table is an ordered collection of elements, where each element can be a scalar value, an object, or another collection. Nested tables can grow dynamically and are stored in memory.

Key Characteristics:

Fixed data type for all elements.

Can be stored in database tables as columns.

You can dynamically add and remove elements.

Use Case: Nested tables are useful when you need to handle an unordered collection of elements that may need to grow and shrink during execution.

Example:

DECLARE
TYPE nested_table_type IS TABLE OF VARCHAR2(100);
my_table nested_table_type := nested_table_type('Apple', 'Banana', 'Cherry');
BEGIN
DBMS_OUTPUT.PUT_LINE(my_table(1)); -- Output: Apple
DBMS_OUTPUT.PUT_LINE(my_table(2)); -- Output: Banana
DBMS_OUTPUT.PUT_LINE(my_table(3)); -- Output: Cherry
END;

Terminology:

Element: The data value stored in the nested table.

Collection Type: Defines the type of the elements (e.g., TABLE OF VARCHAR2(100)).

---

1. Varrays (Variable-Size Arrays)

Definition: A Varray (short for Variable-Size Array) is an ordered collection of elements that has a predefined maximum size. It allows elements to be accessed by an index, but unlike nested tables, it has a fixed size that cannot exceed its defined limit.

Key Characteristics:

Limited size (predefined during declaration).

Faster access than nested tables (especially for smaller collections).

Can be stored in database columns, just like nested tables.

Use Case: Varrays are optimal when you need to work with a fixed-size collection of elements that can be stored and retrieved quickly.

Example:

DECLARE
TYPE varray_type IS VARRAY(3) OF VARCHAR2(100);
my_varray varray_type := varray_type('One', 'Two', 'Three');
BEGIN
DBMS_OUTPUT.PUT_LINE(my_varray(1)); -- Output: One
DBMS_OUTPUT.PUT_LINE(my_varray(2)); -- Output: Two
DBMS_OUTPUT.PUT_LINE(my_varray(3)); -- Output: Three
END;

Terminology:

Maximum Size: The maximum number of elements that can be stored in the varray.

Element: The data value stored in the varray.

---

PL/SQL Collection Operations

PL/SQL collections come with built-in methods that allow developers to manipulate the collection data effectively. These operations help with adding, deleting, and retrieving data efficiently.

Important Collection Methods:

1. COUNT:

Returns the number of elements in the collection.

Useful for iteration or checking if the collection is empty.

Example:

DECLARE
TYPE my_table IS TABLE OF VARCHAR2(100);
my_array my_table := my_table('A', 'B', 'C');
BEGIN
DBMS_OUTPUT.PUT_LINE(my_array.COUNT); -- Output: 3
END;

1. EXTEND:

Adds one or more elements to the collection.

Can be used to append or extend a collection dynamically.

Example:

DECLARE
TYPE my_table IS TABLE OF VARCHAR2(100);
my_array my_table := my_table('A');
BEGIN
my_array.EXTEND; -- Adds a new element to the collection
my_array(2) := 'B';
DBMS_OUTPUT.PUT_LINE(my_array(2)); -- Output: B
END;

1. TRIM:

Removes one or more elements from the collection (from the end).

Example:

DECLARE
TYPE my_table IS TABLE OF VARCHAR2(100);
my_array my_table := my_table('A', 'B', 'C');
BEGIN
my_array.TRIM(1); -- Removes the last element ('C')
DBMS_OUTPUT.PUT_LINE(my_array.COUNT); -- Output: 2
END;

1. DELETE:

Deletes all elements or a specific range of elements from the collection.

Example:

DECLARE
TYPE my_table IS TABLE OF VARCHAR2(100);
my_array my_table := my_table('A', 'B', 'C');
BEGIN
my_array.DELETE; -- Deletes all elements
DBMS_OUTPUT.PUT_LINE(my_array.COUNT); -- Output: 0
END;

---

Bulk Processing with Collections

PL/SQL allows you to efficiently process large volumes of data using bulk operations. The two main bulk operations are BULK COLLECT and FORALL.

1. BULK COLLECT

BULK COLLECT is used to retrieve multiple rows of data from a SQL query into a collection in a single operation, thus improving performance by reducing context switching between SQL and PL/SQL engines.

Example:

DECLARE
TYPE my_table IS TABLE OF VARCHAR2(100);
my_array my_table;
BEGIN
SELECT column_name BULK COLLECT INTO my_array
FROM my_table;

FOR i IN 1..my_array.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(my_array(i));
END LOOP;
END;

1. FORALL

FORALL is used to perform bulk DML operations (like INSERT, UPDATE, DELETE) on a collection. This drastically reduces the number of context switches between PL/SQL and SQL engines.

Example:

DECLARE
TYPE my_table IS TABLE OF VARCHAR2(100);
my_array my_table := my_table('John', 'Jane', 'Jim');
BEGIN
FORALL i IN 1..my_array.COUNT
INSERT INTO my_table (name) VALUES (my_array(i));
END;

---

Best Practices for Working with Collections

1. Choose the Right Collection Type:

Use associative arrays for fast lookups by key.

Use nested tables when the collection size is dynamic and when you need to store them in tables.

Use varrays when you have a fixed, small collection size and need efficient access.

1. Avoid Unnecessary Extensions:

Avoid excessive calls to EXTEND and TRIM in loops for performance reasons. Instead, pre-allocate space if possible.

1. Use Bulk Processing for Performance:

Always use BULK COLLECT and FORALL when dealing with large datasets to avoid context switching and improve performance.

1. Memory Management:

Collections are stored in memory, so be mindful of the size when working with large datasets to avoid memory overload.

---

Conclusion

PL/SQL collections provide a highly efficient and flexible way