PL/SQL Collections (Part 1 - associative arrays)

Once you start using PL/SQL you soon realise that there is a need for more complex data types such as the array, table or set types that exist in other languages and that is where collections come in. Oracle PL/SQL has 3 types of collections: 

  1. associative arrays (also known as index-by tables)
  2. VARRAY (a varying size array)
  3. nested tables

Let's explore each of these in detail.

PL/SQL Assciative Arrays

Associative arrays are the equivalent of hash tables or unordered tables in other languages. They are suitable for smallish look-up tables and for passing collections of data backwards and forwards to the database.

They are implemented as pairs - the index value and the data value - and it is important to note that the index value can only be of type STRING or INTEGER.

Other properties of the associative array type of PL/SQL collections are that they are effectively unbounded (the maximum number of elements is the maximum value of a variable of type PLS_INTEGER) and they can be densely populated or sparsely populated (i.e there can be gaps in a sequence).

Let's look at an example.

First we declare the type, then we declare a variable of that type.

DECLARE

first_names TABLE OF VARCHAR2(20) INDEX BY PLS_INTEGER;
-- PLSQL collections can't be declared using the above format

TYPE varchar_tab IS TABLE OF VARCHAR2(20) INDEX BY PLS_INTEGER;
--first you have to declare a type

first_names varchar_tab; -- then you declare a variable of that type

BEGIN

    first_names(-5)  := 'Winston';
    first_names(-10) := 'John';
    first_names(200) := 'Brian';

    DBMS_OUTPUT.PUT_LINE(first_names(-5));

END;

In the above example, the index is an integer, and we're storing strings but we could switch this around and index by string and store integers.

DECLARE
TYPE int_tab IS TABLE OF PLS_INTEGER INDEX BY VARCHAR2(20);
-- index by strings of 20 characters and store integers

test_scores int_tab;

BEGIN

    test_scores('Winston') := 60;
    test_scores('John')    := 90;
    test_scores('Brian')   := 95;

    DBMS_OUTPUT.PUT_LINE(test_scores('Brian'));

END;


Oracle Tips & Tricks to SKYROCKET Your Career!

If you're not already a subscriber you're  missing out on a myriad of tips and techniques to help you become a better, faster, smarter developer.Subscribe now and ignite your career.


Thus far we've used constants as the index but what if we don't know what the value of the index is for any elements in our PLSQL collections? What do we do then?

Simple we just use the methods FIRST, LAST and NEXT and PRIOR on our PL/SQL collections.  As their names imply, these functions return the value of the first element in the collection, the last element in the collection, the next element in the collection, respectively. 

This means we can re-write our previous example as follows:

DECLARE
    TYPE int_tab IS TABLE OF PLS_INTEGER INDEX BY VARCHAR2(20);

    test_scores int_tab;

    ix VARHCAR2;

BEGIN

    test_scores('Winston') := 60;
    test_scores('John')    := 90;
    test_scores('Brian')   := 95;

    DBMS_OUTPUT.PUT_LINE('Scores');
    ix := test_scores.first;

    WHILE ix <= test_scores.last LOOP   
       DBMS_OUTPUT.PUT_LINE(
ix||':'||test_scores(ix));
       ix := test_scores.NEXT(ix);
    END LOOP;

END;

This will produce the results (in this case) in alphabetical order:
    Brian:95
    John:90
    Winston:60

A few other points to note:

  • with the associative array type of PL/SQL collections, if we attempt to read from an uninitialised element of the array then an "ORA-01403: no data found" exception is automatically raised by Oracle
  • for an uninitialised associative array, FIRSTLASTPRIOR and NEXT will all return NULL
  • we can also delete elements from an associative array or the whole array using the DELETE method

DECLARE
    TYPE int_tab IS TABLE OF PLS_INTEGER INDEX BY VARCHAR2(20);

    first_test_scores  int_tab;
    second_test_scores int_tab;
    third_test_scores  int_tab;

    ix VARHCAR2;

BEGIN

    first_test_scores('Winston') := 60;
    first_test_scores('John')    := 90;
    first_test_scores('Brian')   := 95;
    first_test_scores('Brett')   := 54;
    first_test_scores.DELETE('Brett');

    second_test_scores('Winston') := 16;
    second_test_scores('John')    := 28;
    second_test_scores('Brian')   := 45;
    second_test_scores('Brett')   := 68;
    second_test_scores('Bill')    := 61;
    second_test_scores.DELETE('Brett','John'); 
    -- delete brett's, brian's and john's scores

    third_test_scores('Winston') := 71;
    third_test_scores('John')    := 58;
    third_test_scores.DELETE; -- deletes ALL elements

    DBMS_OUTPUT.PUT_LINE('FIRST TEST SCORES');
    ix := first_test_scores.first;

    WHILE ix <= first_test_scores.last LOOP   
       DBMS_OUTPUT.PUT_LINE(
ix||':'||first_test_scores(ix));
       ix := first_test_scores.NEXT(ix);
    END LOOP;

    DBMS_OUTPUT.PUT_LINE('SECOND TEST SCORES');
    ix := second_test_scores.first;

    WHILE ix <= second_test_scores.last LOOP
       DBMS_OUTPUT.PUT_LINE(ix||':'||second_test_scores(ix));
       ix := second_test_scores.NEXT(ix);
    END LOOP;

    DBMS_OUTPUT.PUT_LINE('THIRD TEST SCORES');
    ix := third_test_scores.first;

    WHILE ix <= third_test_scores.last LOOP
       DBMS_OUTPUT.PUT_LINE(ix||':'||third_test_scores(ix));
       ix := third_test_scores.NEXT(ix);
    END LOOP;

END;

This will produce the following results :
    FIRST TEST SCORES
    Brian:95
    John:90
    Winston:60
    SECOND TEST SCORES
    Bill:61
    Winston:60
    THIRD TEST SCORES


Return to PL/SQL - Oracle integration           Next: PL/SQL collection varrays


Learn Oracle PL/SQL quickly and easily with a high-quality training course from real-world expertsSee here for more details.

Return from plsql collections to Oracle PL/SQL overview
Return to home page