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

#sql #development #devops #fullstack

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

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

3. 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:

Associative Arrays are key-value pairs and can be sparse (have gaps between indexes).
Nested Tables are unordered collections that can grow dynamically and may contain gaps.
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.

DEV Community

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