PL/SQL Associative Array

Summary: in this tutorial, you will learn about Oracle PL/SQL associative arrays including declaring arrays, populating values, and iterating over their elements.

Introduction to Oracle PL/SQL associative arrays

Associative arrays are single-dimensional, unbounded, sparse collections of homogeneous elements.

First, an associative array is single-dimensional. It means that an associative array has a single column of data in each row, which is similar to a one-dimensional array.

Second, an associative array is unbounded, meaning that it has a predetermined limited number of elements.

Third, an associative array is sparse because its elements are not sequential. In other words, an associative array may have gaps between elements.

Finally, an associative array has elements that have the same data type, or we call them homogenous elements.

Note that associative arrays were known as PL/SQL tables in Oracle 7, and index-by tables in Oracle 8 and 8i. Their names were changed to associative arrays in Oracle 9i release 1.

An associative array can be indexed by numbers or characters.

Declaring an associative array is a two-step process. First, you declare an associative array type. Then, you declare an associative array variable of that type.

Declaring an associative array type

The following shows the syntax for declaring an associative array type:

TYPE associative_array_type 
    IS TABLE OF datatype [NOT NULL]
    INDEX BY index_type;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax:

• The associative_array_type is the name of the associative array type.
• The datatype is the data type of the elements in the array.
• The index_type is the data type of the index used to organize the elements in the array.
• Optionally, you can specify NOT NULL to force every element in the array must have a value.

The following example declares an associative array of characters indexed by characters:

TYPE t_capital_type 
    IS TABLE OF VARCHAR2(100) 
    INDEX BY VARCHAR2(50);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Declaring an associative array variable

After having the associative array type, you need to declare an associative array variable of that type by using this syntax:

associative_array associative_array_type Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

For example, this statement declares an associative array t_capital with the type t_capital_type:

t_capital t_capital_type;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Accessing associative array elements

To access an array element, you use this syntax:

array_name(index)Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Note that index can be a number or a character string.

Assigning associative array elements

To assign a value to an associative array element, you use the assignment operation (:=):

array_name(index) := value;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Oracle PL/SQL associative array example

The following anonymous block shows how to declare an associative array and assign values to its elements:

DECLARE
    -- declare an associative array type
    TYPE t_capital_type 
        IS TABLE OF VARCHAR2(100) 
        INDEX BY VARCHAR2(50);
    -- declare a variable of the t_capital_type
    t_capital t_capital_type;
BEGIN
    
    t_capital('USA')            := 'Washington, D.C.';
    t_capital('United Kingdom') := 'London';
    t_capital('Japan')          := 'Tokyo';
    
END;
/Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Associative array methods

Associative arrays have a number of useful methods for accessing array element indexes and manipulating elements effectively.

To call a method you use the following syntax:

array_name.method_name(parameters);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

This syntax is similar to the syntax of calling a method in C# and Java.

In this tutorial, we introduce you to two useful methods called FIRST and NEXT(n).

The method FIRST returns the first index of the array. If an array is empty, the FIRST method returns NULL.

The method NEXT(n) returns the index that succeeds the index n. If n has no successor, then the NEXT(n) returns NULL.

The FIRST and NEXT(n) methods are useful in iterating over the elements of an array using a WHILE loop:

l_index := array_name.FIRST;

WHILE l_index IS NOT NULL LOOP
    -- access the array element
    -- array_name(l_index)
    l_index := array_name.NEXT(l_index);
END LOOP;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Putting it all together

The following anonymous block illustrates how to declare an associative array, populate its elements, and iterate over the array elements:

DECLARE
    -- declare an associative array type
    TYPE t_capital_type 
        IS TABLE OF VARCHAR2(100) 
        INDEX BY VARCHAR2(50);
    -- declare a variable of the t_capital_type
    t_capital t_capital_type;
    -- local variable
    l_country VARCHAR2(50);
BEGIN
    
    t_capital('USA')            := 'Washington, D.C.';
    t_capital('United Kingdom') := 'London';
    t_capital('Japan')          := 'Tokyo';
    
    l_country := t_capital.FIRST;
    
    WHILE l_country IS NOT NULL LOOP
        dbms_output.put_line('The capital of ' || 
            l_country || 
            ' is ' || 
            t_capital(l_country));
        l_country := t_capital.NEXT(l_country);
    END LOOP;
END;
/Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Here is the output:

The capital of Japan is Tokyo
The capital of USA is Washington, D.C.
The capital of United Kingdom is LondonCode language: plaintext (plaintext)

In this tutorial, you have learned about Oracle PL/SQL associative arrays including declaring arrays, populating values, and iterating over their elements.