PL/SQL collections - Associative Arrays, Nested Tables & VARRAYs.

Here's a simple example of each of the three types of PL/SQL collections: Associative Arrays, Nested Tables, and VARRAYs.

1. Associative Array Example (Index-By Table)

An associative array uses an index (can be integer or string) to access its elements. It's perfect for key-value pairs.

DECLARE
TYPE emp_table IS TABLE OF VARCHAR2(50) INDEX BY PLS_INTEGER; -- Associative Array Declaration
employees emp_table;
BEGIN
-- Assign values to the associative array
employees(101) := 'John Doe';
employees(102) := 'Jane Smith';
employees(103) := 'Sam Wilson';

-- Retrieve and print values
DBMS_OUTPUT.PUT_LINE('Employee 101: ' || employees(101));
DBMS_OUTPUT.PUT_LINE('Employee 102: ' || employees(102));
DBMS_OUTPUT.PUT_LINE('Employee 103: ' || employees(103));

END;
/

Output:

Employee 101: John Doe
Employee 102: Jane Smith
Employee 103: Sam Wilson

1. Nested Table Example

A nested table is an unordered collection of elements that can be extended dynamically. It's suitable for large sets of data.

DECLARE
TYPE salary_table IS TABLE OF NUMBER; -- Nested Table Declaration
employee_salaries salary_table := salary_table(); -- Initialize the nested table
BEGIN
-- Adding elements to the nested table
employee_salaries.EXTEND(3); -- Reserve space for 3 elements
employee_salaries(1) := 50000;
employee_salaries(2) := 60000;
employee_salaries(3) := 70000;

-- Print the salaries
FOR i IN 1..employee_salaries.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE('Salary for Employee ' || i || ': ' || employee_salaries(i));
END LOOP;

-- Remove an element from the nested table
employee_salaries.DELETE(2); -- Delete salary of Employee 2

-- Check if element exists after deletion
IF employee_salaries.EXISTS(2) THEN
    DBMS_OUTPUT.PUT_LINE('Employee 2 Salary still exists.');
ELSE
    DBMS_OUTPUT.PUT_LINE('Employee 2 Salary deleted.');
END IF;

END;
/

Output:

Salary for Employee 1: 50000
Salary for Employee 2: 60000
Salary for Employee 3: 70000
Employee 2 Salary deleted.

1. VARRAY Example (Variable-Size Array)

A VARRAY has a fixed maximum size and holds elements in order. It's useful when you know the upper limit of your data size.

DECLARE
TYPE employee_varray IS VARRAY(5) OF VARCHAR2(50); -- VARRAY with max size of 5
employee_names employee_varray := employee_varray('John', 'Jane'); -- Initialize with 2 values
BEGIN
-- Add more elements to the VARRAY
employee_names.EXTEND(1); -- Extend by 1 element
employee_names(3) := 'Sam';

-- Print all the names in the VARRAY
FOR i IN 1..employee_names.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE('Employee ' || i || ': ' || employee_names(i));
END LOOP;

END;
/

Output:

Employee 1: John
Employee 2: Jane
Employee 3: Sam

Key Takeaways:

1. Associative Arrays are key-value pairs and can be sparse (have gaps between indexes).

2. Nested Tables are unordered collections that can grow dynamically and may contain gaps.

3. VARRAYs are ordered collections with a fixed maximum size and do not allow gaps between elements.

Each type is suitable for different scenarios, depending on your need for fixed-size vs dynamic data, ordered vs unordered elements, and whether you require gaps in your dataset.