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-dimension array.

Second, an associative array is unbounded, meaning that it has a predetermined limits 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 which have the same data type, or we call them homogenous elements.

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. And 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: SQL (Structured Query Language) (sql)

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: SQL (Structured Query Language) (sql)

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: SQL (Structured Query Language) (sql)

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

t_capital t_capital_type;
Code language: SQL (Structured Query Language) (sql)

Accessing associative array elements

To access an array element, you use this syntax:

array_name(index)
Code language: SQL (Structured Query Language) (sql)

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: SQL (Structured Query Language) (sql)

Oracle PL/SQL associative array example

The following anonymous block shows how to declare an associative array and assigns 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: SQL (Structured Query Language) (sql)

Associative array methods

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

To call a method you use the following syntax:

array_name.method_name(parameters);
Code language: SQL (Structured Query Language) (sql)

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: SQL (Structured Query Language) (sql)

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: SQL (Structured Query Language) (sql)

Here is the output:

The capital of Japan is Tokyo
The capital of USA is Washington, D.C.
The capital of United Kingdom is London
Code language: SQL (Structured Query Language) (sql)

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