Oracle PL/SQL Collections

It is strange that relatively few database programmers really know or use collections in PL/SQL. They tend to program more in SQL-like way and not thinking much about performance or readability of their programs.

With collections you can improve performances by cache data that are queried repeatedly in a single program or you can process data more quickly not using relational tables or global temporary tables.

PL/SQL collections are cumbersome and at least confusing (in compare to collections in other languages like Java for instance).

There are three different types of collections. I will show all types with examples.

Associative arrays

These are single-dimensional, unbounded, sparse (do not need to be filled up sequentially) collections of homogeneous elements. In the next example I declare such array, populate it with some data and iterate through the collection.

SET SERVEROUTPUT ON

DECLARE
    TYPE names_list_t IS TABLE OF VARCHAR2(255)
        INDEX BY PLS_INTEGER;
    people names_list_t;

    l_row PLS_INTEGER; -- Same type as index.

BEGIN

    people(1)         := 'Bob';
    people(33)        := 'Bruce';
    people(43)        := 'Rocky';
    people(-12)        := 'Grozni';
    people(1555555)   := 'Ivan'; 
  
    l_row := people.FIRST;

    WHILE (l_row IS NOT NULL)
    LOOP
        DBMS_OUTPUT.put_line(people(l_row));
        l_row := people.NEXT(l_row);
    END LOOP;
END;

This type of collection is the most efficient, but if you want to store your collection within a database table, you cannot use an associative array (you need to use one of other two types). If you need sparse collections, you only practical option is an associative array. This is also true if your PL/SQL application requires negative index subscript (like -12 in example).

Nested table

These are also single-dimensional, unbounded (unrestricted) collections of homogeneous elements. They are initially dense but can become sparse through deletions. Nested tables are multisets, which means that there is no inherent order to the elements in a nested table. This can be problem if we need to preserve order of elements. We can use keys and indexes, but there is another type of collection VARRAY that will preserver order.

In following example we will first declare a nested table type as a schema-level type. Then we will declare couple of nested tables based on that type, create their union and display result of union.

CREATE OR REPLACE TYPE car_names_list_t IS TABLE OF VARCHAR2(100);

DECLARE
    great_cars          car_names_list_t := car_names_list_t();
    not_so_great_cars   car_names_list_t := car_names_list_t();
    
    all_this_cars       car_names_list_t := car_names_list_t();    
    
BEGIN
    great_cars.EXTEND(3);    
    great_cars(1) := 'Golf';
    great_cars(2) := 'Impreza';
    great_cars(3) := 'Focus';
    
    not_so_great_cars.EXTEND(2);
    not_so_great_cars(1) := 'Zastava';
    not_so_great_cars(2) := 'Dacia';
    
    all_this_cars := great_cars MULTISET UNION not_so_great_cars;
    
    FOR l_row IN all_this_cars.FIRST .. all_this_cars.LAST
    LOOP
        DBMS_OUTPUT.put_line(all_this_cars(l_row));
    END LOOP;
    
END; 

EXTENDS method is used for "making more room" in nested tables. So, when using nested tables we need explicitly resize our collection. MULTISET UNION (there are others like MULTISET EXCEPT) is used for high-level set operations. In this case (using some kind of operation on sets), we don't need to use EXTENDS (obliviously).

Nested tables are useful if you need to perform high-level set operations on your collections, but this is only true if you use older Oracle database (<=10g). Also this kind of collections are only choice if you intent to store large amounts of persistent data in column collection (this is because database will behind scene create real table to hold data).

VARRAY

Like the other two collection type, VARRAYs (variable-sized arrays) are also single-dimensional collections of homogeneous elements. However, the are always limited (bounded) and never sparse. When you declare a type of VARRAY, you must also specify the maximum number of elements it can contain. Important difference to nested tables is that when you store VARRAY as database column, its elements preserve the order.

In following example we will see demonstration simple usage of VARRAYs.

SET SERVEROUTPUT ON

DECLARE
    TYPE anime_movies_t IS VARRAY (3) OF VARCHAR2(100);
        
    anime_movies anime_movies_t := anime_movies_t();

BEGIN
    --extendet only to first
    anime_movies.EXTEND(1);
    anime_movies(1) := 'Akira';
    --here we extend to full length
    anime_movies.EXTEND(2);
    anime_movies(2) := 'Castle in the sky';
    anime_movies(3) := 'My neighbour totorro';
    
    --loop, or do something
END;

Ok, now. This example speak for it self, just note that you also need to use EXTEND to make room for elements.
You will probably use this type of collection when you need to preserve the order of the elements stored in the database collection column, and also when you have relatively small amount of data in collection. Also with VARRAY you also do not need to worry about deletion occurring in the middle of the data set; your data has intrinsic upper bound; or you need to retrieve entire collection simultaneously.

My tip for the end is that, if you are using collections in PL/SQL, you probably should create your set of procedures and functions (as package) that will encapsulate
managing collections, and maybe will hide also what kind of collection you are using...but this is probably not good idea for most of the situations (but collection package _is_ a good idea).