PL/SQL collections

Let's focus on PL/SQL collections and relevant terminologies associated with collections in PL/SQL.

In Oracle PL/SQL, collections are complex data types that allow you to handle multiple values in a single variable. Collections can be used to store, process, and manage data in a variety of ways, making them essential in PL/SQL programming. Here are the main concepts, terminologies, and types of collections in PL/SQL.

---

PL/SQL Collections

PL/SQL collections are similar to arrays in other programming languages but offer more flexibility. There are three main types of collections in Oracle 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 is a collection of key-value pairs where the key is an index (either integer or string), and the value is the data being stored. It is also called an index-by table.

Terminology:

Index: The key used to uniquely identify elements in the associative array. The index can be of type INTEGER or VARCHAR.

Element: The value stored in the collection. Each element is associated with an index.

Usage:

Typically used for fast lookups where the keys are non-contiguous (i.e., you can have gaps between indices).

Example:

DECLARE
TYPE assoc_array IS TABLE OF VARCHAR2(100) INDEX BY PLS_INTEGER;
my_array assoc_array;
BEGIN
my_array(1) := 'First';
my_array(2) := 'Second';
DBMS_OUTPUT.PUT_LINE(my_array(1)); -- Output: First
END;

---

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. It is similar to an array in other programming languages, but it can grow dynamically and is stored in memory.

Terminology:

Element: The data stored in the nested table, which can be any data type.

Collection Type: The datatype definition for the nested table. It’s defined with the TABLE OF keyword.

Usage:

Nested tables are often used to store sets of data and are useful when you need dynamic resizing.

Example:

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

---

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. Varrays are typically used when you know the size of the collection in advance, and they provide better memory efficiency for smaller, fixed-size collections.

Terminology:

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

Element: The data stored in the varray, which can be of any datatype.

Usage:

Varrays are most useful when the number of elements is small and fixed. They can be stored in database tables as well.

Example:

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

---

Common PL/SQL Collection Terminologies

Below are some important terminologies and concepts that are associated with collections in PL/SQL.

1. Collection Types

Declared Types: In PL/SQL, collections are often declared as custom types using the TYPE keyword. This allows you to define collections of specific data types (e.g., TABLE OF, VARRAY).

1. Bulk Operations:

PL/SQL collections are commonly used with bulk operations like BULK COLLECT and FORALL, which allow for efficient processing of multiple rows of data in a single operation.

BULK COLLECT: A PL/SQL feature that allows for fetching multiple rows from a SQL query into a collection in one go, significantly improving performance.

FORALL: A PL/SQL feature that allows for efficient processing of DML (Data Manipulation Language) operations on a collection, reducing context switching between PL/SQL and SQL engines.

1. Initializing Collections

Initialization: Collections can be initialized either explicitly or implicitly.

Explicit Initialization: When you declare and initialize a collection in the same statement.

Implicit Initialization: When you declare a collection without initializing it, and then assign values to it later in the code.

1. Collection Methods:

PL/SQL collections provide built-in methods that allow you to manipulate collections effectively. Some of the key methods include:

COUNT: Returns the number of elements in a collection.

EXTEND: Adds one or more elements to a collection.

TRIM: Removes one or more elements from the end of a collection.

DELETE: Deletes elements from a collection.

1. Null Elements:

A collection can have NULL elements, which are placeholders for data that might not be available. Handling NULL values appropriately is essential for ensuring correct collection operations.

1. Nested Collection Types:

You can create complex collection structures by using collections within collections. For example, a nested table can contain varrays, and an associative array can contain nested tables.

1. Scope of Collections:

Local Collections: Defined within a PL/SQL block or procedure, their scope is limited to the block.

Global Collections: Defined at the package level, making them accessible to all procedures/functions in the package.

---

PL/SQL Collection Example: Bulk Collect with Nested Table

DECLARE
TYPE nested_table_type IS TABLE OF VARCHAR2(100);
TYPE assoc_array IS TABLE OF nested_table_type INDEX BY PLS_INTEGER;

my_array assoc_array;
my_nested_table nested_table_type;
BEGIN
-- Assign values to the nested table
my_nested_table := nested_table_type('Apple', 'Banana');

-- Assign the nested table to the associative array
my_array(1) := my_nested_table;

DBMS_OUTPUT.PUT_LINE(my_array(1)(1)); -- Output: Apple
END;

---

Summary of PL/SQL Collections Terminologies

---

Conclusion

PL/SQL collections provide an efficient way to handle and manipulate multiple values in a single variable. By understanding the different types of collections (Associative Arrays, Nested Tables, and Varrays) and their associated terminologies, you can leverage them effectively for a variety of use cases, including bulk data operations, dynamic collection handling, and more complex data processing scenarios in PL/SQL.