Associative Arrays in PL/SQL

An associative array, also known as an index-by table, is a PL/SQL collection type that stores key-value pairs. It is similar to a dictionary or hash table in other programming languages. Associative arrays are powerful because they can grow or shrink dynamically, and you can access their elements using keys (either integers or strings).

Characteristics of Associative Arrays:

1. Dynamic Size:

• Unlike other collection types like nested tables or VARRAYs, associative arrays do not have a predefined size limit.
• They can grow or shrink as needed, making them very flexible.
• Elements are created when values are assigned to a key, and they are automatically removed when deleted.

2. Sparse Indexing:

• Associative arrays can have non-continuous keys.
• This means you can have elements at index 1 and 1000, without needing to store values at indexes 2 to 999.
• This flexibility helps in saving memory and simplifies certain operations.

3. Key-Value Pairs:

• You can use either PLS_INTEGER (numeric) or VARCHAR2 (string) as the key type.
• This allows you to create arrays where each element is referenced by a meaningful identifier rather than just a numeric position.
• For example, you could use strings like "employee_id" or "color_name" as keys.

4. Ease of Use:

• Associative arrays are easy to use for storing temporary data. You can loop through them, check if a key exists, and perform various operations without complex data structures.

Syntax:

To declare an associative array, you define a new type and then create variables of that type. Here is how you can do it:

1. Declaration:

TYPE array_type IS TABLE OF datatype INDEX BY PLS_INTEGER;
-- or
TYPE array_type IS TABLE OF datatype INDEX BY VARCHAR2(size);

variable_name array_type;

datatype: This is the data type of the elements you want to store (e.g., NUMBER, VARCHAR2, DATE).

INDEX BY PLS_INTEGER or INDEX BY VARCHAR2(size): Specifies the type of the key (index).

Example of Associative Array in PL/SQL:

Let’s create an associative array to store and display colors with integer keys.

Example 1: Using PLS_INTEGER Index

DECLARE
    -- Define an associative array type with integer indexing
    TYPE color_array IS TABLE OF VARCHAR2(30) INDEX BY PLS_INTEGER;
    v_colors color_array;
BEGIN
    -- Assign values to the associative array
    v_colors(1) := 'Red';
    v_colors(2) := 'Green';
    v_colors(3) := 'Blue';

    -- Access and print specific values
    DBMS_OUTPUT.PUT_LINE('Color at Index 1: ' || v_colors(1)); 
-- Output: Red
    DBMS_OUTPUT.PUT_LINE('Color at Index 2: ' || v_colors(2)); 
-- Output: Green

    -- Use the COUNT method to show the number of elements
    DBMS_OUTPUT.PUT_LINE('Total Colors: ' || v_colors.COUNT); 
-- Output: 3

    -- Loop through the associative array using the index range
    FOR i IN v_colors.FIRST .. v_colors.LAST LOOP
        IF v_colors.EXISTS(i) THEN
            DBMS_OUTPUT.PUT_LINE
            ('Color at Index ' || i || ': ' || v_colors(i));
        END IF;
    END LOOP;
END;
/

Explanation:

1. Declaration:

• The type color_array is defined to store VARCHAR2(30) values, and each value is indexed by an integer (PLS_INTEGER).
• v_colors is then declared as a variable of this type.

2. Assigning Values:

• Values are assigned using indices. v_colors(1) stores "Red", v_colors(2) stores "Green", and so on.

3. Accessing Elements:

• You can directly access any element by specifying its index, e.g., v_colors(1) retrieves "Red".

4. Using Collection Methods:

• .FIRST and .LAST: Retrieve the first and last index values that exist in the array.
• .COUNT: Returns the total number of elements in the array.
• .EXISTS(index): Checks if a particular index exists.

5. Looping Through the Array:

By using .FIRST and .LAST, you can create a loop that processes each element, ensuring you don’t miss any and handle sparse arrays properly.

Use Case Example: Mapping Colors to Codes - Example 2: Using VARCHAR2 Index

You can also use strings as keys in associative arrays, which is particularly useful when you need to map names or identifiers.

DECLARE
    -- Define an associative array type with string indexing
    TYPE color_code_array IS TABLE OF VARCHAR2(10) INDEX BY VARCHAR2(20);
    v_color_codes color_code_array;
BEGIN
    -- Assign values to the associative array
    v_color_codes('Red') := 'FF0000';
    v_color_codes('Green') := '00FF00';
    v_color_codes('Blue') := '0000FF';

    -- Retrieve and print a specific color code
    DBMS_OUTPUT.PUT_LINE('Color code for Green: ' || v_color_codes('Green')); -- Output: 00FF00

    -- Print all color codes
    FOR color IN (SELECT COLUMN_VALUE FROM TABLE(v_color_codes.keys)) LOOP
        DBMS_OUTPUT.PUT_LINE('Color: ' || color.COLUMN_VALUE || ' - Code: ' || v_color_codes(color.COLUMN_VALUE));
    END LOOP;
END;
/

Summary:

• Associative arrays are flexible collections used in PL/SQL that store key-value pairs.
• They can be indexed using PLS_INTEGER (numbers) or VARCHAR2 (strings), making them versatile.
• They are dynamic, allowing for efficient memory usage without pre-defining size limits.
• You can perform operations such as adding, deleting, or checking if a key exists easily.

Associative arrays are ideal for temporary data processing, lookup tables, and scenarios where you need to map or transform data efficiently within PL/SQL code.