PL/SQL Collections: Associative Arrays, Nested Tables, and Varrays

PL/SQL offers three types of collections for managing groups of data: Associative Arrays, Nested Tables, and Varrays. These collections are versatile, with distinct features and use cases.

1. Associative Arrays

Definition:
An associative array is a collection of key-value pairs where each key is unique and points to a corresponding value.

• Keys can be integer-based or character-based.
• Values can be scalar data types or record data types.
• Associative arrays are temporary and cannot be used with SQL statements like INSERT or SELECT INTO.

Common Methods:

• EXISTS: Checks if a key exists.
• COUNT: Returns the number of elements.
• FIRST and LAST: Returns the first and last keys.
• PRIOR and NEXT: Navigate between keys.
• DELETE: Removes elements.

Example:

DECLARE
    TYPE AssocArray IS TABLE OF VARCHAR2(50) INDEX BY PLS_INTEGER;
    v_emps AssocArray;
BEGIN
    v_emps(101) := 'Alice';
    v_emps(102) := 'Bob';
    v_emps(103) := 'Charlie';

    -- Access an element
    DBMS_OUTPUT.PUT_LINE(v_emps(102)); -- Output: Bob

    -- Loop through the array
    FOR i IN v_emps.FIRST .. v_emps.LAST LOOP
        DBMS_OUTPUT.PUT_LINE('Key: ' || i || ', Value: ' || v_emps(i));
    END LOOP;
END;
/

2. Nested Tables

Definition:
A nested table is a collection of elements stored as a single column in a database table. Nested tables are unbounded and can grow dynamically.

Key Features:

• Can be stored in the database.
• Act as one-dimensional arrays in PL/SQL.
• Useful for managing sets of data within a table.

Example:

DECLARE
    TYPE NumberTable IS TABLE OF NUMBER;
    v_numbers NumberTable := NumberTable(1, 2, 3, 4);
BEGIN
    -- Add a new element
    v_numbers.EXTEND;
    v_numbers(5) := 5;

    -- Loop through nested table
    FOR i IN v_numbers.FIRST .. v_numbers.LAST LOOP
        DBMS_OUTPUT.PUT_LINE('Element: ' || v_numbers(i));
    END LOOP;
END;
/

3. Varrays (Variable-Size Arrays)

Definition:
A varray is a collection of elements with a fixed maximum size. Varrays are ideal for scenarios where the number of elements is relatively small and known in advance.

Key Features:

• Elements are stored sequentially.
• Can be stored in database tables as a column type.

Example:

DECLARE
    TYPE VarrayType IS VARRAY(5) OF VARCHAR2(50);
    v_names VarrayType := VarrayType('John', 'Doe', 'Jane');
BEGIN
    -- Access elements
    DBMS_OUTPUT.PUT_LINE(v_names(1)); -- Output: John

    -- Add an element
    v_names.EXTEND;
    v_names(4) := 'Smith';

    -- Loop through varray
    FOR i IN 1 .. v_names.COUNT LOOP
        DBMS_OUTPUT.PUT_LINE('Name: ' || v_names(i));
    END LOOP;
END;
/

Comparison of PL/SQL Collections

Best Practices

1. Use Associative Arrays:
   • For temporary data that doesn’t need to persist in the database.
   • For quick lookups based on unique keys.
2. Use Nested Tables:
   • For dynamically growing datasets that may need to be stored in the database.
3. Use Varrays:
   • When the maximum size is known and small.

Conclusion

PL/SQL collections—associative arrays, nested tables, and varrays—offer powerful ways to manage data groups. Understanding their unique features and use cases ensures efficient and effective implementation in your PL/SQL programs.

Would you like detailed examples or advanced use cases for these collection types? 😊