PL/SQL Nested Tables

Summary: in this tutorial, you have learned about the PL/SQL nested tables in Oracle and how to manipulate their elements effectively.

Introduction to PL/SQL nested tables

Nested tables are single-dimensional, unbounded collections of homogeneous elements.

• First, a nested table is single-dimensional, meaning that each row has a single column of data like a one-dimension array.
• Second, a nested table is unbounded. It means that the number of elements of a nested table is predetermined.
• Third, homogeneous elements mean that all elements of a nested table have the same data type.

Noted that a nested table is initially dense. However, it can become sparse through the removal of elements.

Declaring a nested table variable

Declaring a nested table is a two-step process.

First, declare the nested table type using this syntax:

TYPE nested_table_type 
    IS TABLE OF element_datatype [NOT NULL];
Code language: SQL (Structured Query Language) (sql)

Then, declare the nested table variable based on a nested table type:

nested_table_variable nested_table_type;
Code language: SQL (Structured Query Language) (sql)

It is possible to create a nested table type located in the database:

CREATE [OR REPLACE] TYPE nested_table_type
    IS TABLE OF element_datatype [NOT NULL];
Code language: SQL (Structured Query Language) (sql)

If you want to drop a type, use the following DROP TYPE statement:

DROP TYPE type_name [FORCE];        
Code language: SQL (Structured Query Language) (sql)

Initializing a nested table

When you declare a nested table variable, it is initialized to NULL.

To initialize a nested table, you can use a constructor function. The constructor function has the same name as the type:

nested_table_variable := nested_table_type();
Code language: SQL (Structured Query Language) (sql)

You can also declare a nested table and initialize it in one step using the following syntax:

nested_table_variable nested_table_type := nested_table_type();
Code language: SQL (Structured Query Language) (sql)

Add elements to a nested table

To add an element to a nested table, you first use the EXTEND method:

nested_table_variable.EXTEND;
Code language: SQL (Structured Query Language) (sql)

Then, use the assignment operator (:=) to add an element to the nested table:

nested_table_variable := element;
Code language: SQL (Structured Query Language) (sql)

If you want to add multiple elements, you use the EXTEND(n) method, where n is the number of elements that you want to add:

nested_table_variable.EXTEND(n);

nested_table_variable := element_1;
nested_table_variable := element_2;
..
nested_table_variable := element_n;
Code language: SQL (Structured Query Language) (sql)

Accessing elements by their indexes

To access an element at a specified index, you use the following syntax:

nested_table_variable(index);
Code language: SQL (Structured Query Language) (sql)

Iterate over the elements of a nested table

Nested tables have the FIRST and LAST methods that return the first and last indexes of elements respectively.

Therefore, you can use these methods to iterate over the elements of a nested table using a FOR loop:

FOR l_index IN nested_table_variable.FIRST..nested_table_variable.LAST
LOOP
    -- access element

END LOOP;
Code language: SQL (Structured Query Language) (sql)

Putting it all together

We’ll use the customers table from the sample database for the demonstration:

The following example illustrates how to use a cursor to get the first 10 customer names, add the customer names to a nested table, and iterate over the elements:

DECLARE
    -- declare a cursor that return customer name
    CURSOR c_customer IS 
        SELECT name 
        FROM customers
        ORDER BY name 
        FETCH FIRST 10 ROWS ONLY;
    -- declare a nested table type   
    TYPE t_customer_name_type 
        IS TABLE OF customers.name%TYPE;
    
    -- declare and initialize a nested table variable
    t_customer_names t_customer_name_type := t_customer_name_type(); 
    
BEGIN
    -- populate customer names from a cursor
    FOR r_customer IN c_customer 
    LOOP
        t_customer_names.EXTEND;
        t_customer_names(t_customer_names.LAST) := r_customer.name;
    END LOOP;
    
    -- display customer names
    FOR l_index IN t_customer_names.FIRST..t_customer_names.LAST 
    LOOP
        dbms_output.put_line(t_customer_names(l_index));
    END LOOP;
END;
Code language: SQL (Structured Query Language) (sql)

Let’s examine the example in detail.

First, declare a cursor that returns the first 10 alphabetically sorted customer names.

CURSOR c_customer IS 
        SELECT name 
        FROM customers
        ORDER BY name 
        FETCH FIRST 10 ROWS ONLY;
Code language: SQL (Structured Query Language) (sql)

Next, declare a nested table type:

TYPE t_customer_name_type 
   IS TABLE OF customers.name%TYPE;
Code language: SQL (Structured Query Language) (sql)

Then, declare a nested table variable and initialize it using the nested table constructor:

t_customer_names t_customer_name_type := t_customer_name_type(); 
Code language: SQL (Structured Query Language) (sql)

After that, fetch customer names from the cursor and add them to the nested table:

FOR r_customer IN c_customer LOOP
    t_customer_names.EXTEND;
    t_customer_names(t_customer_names.LAST) := r_customer.name;
END LOOP;
Code language: SQL (Structured Query Language) (sql)

Finally, iterate over the elements of the nested table and display each:

FOR l_index IN t_customer_names.FIRST..t_customer_names.LAST 
LOOP
    dbms_output.put_line(t_customer_names(l_index));
END LOOP;
Code language: SQL (Structured Query Language) (sql)

Here is the output:

3M
ADP
AECOM
AES
AIG
AT&T
AbbVie
Abbott Laboratories
Advance Auto Parts
Aetna
Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned about the PL/SQL nested tables in Oracle and how to manipulate their elements effectively.