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

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

Let's explore each of these in detail.

PLSQL 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 PLSQL 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 PLSQL 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 PLSQL 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, FIRST, LAST, PRIOR 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 follwoing results :
FIRST TEST SCORES
  Brian:95
  John:90
Winston:60
SECOND TEST SCORES
  Bill:61
  Winston:60
THIRD TEST SCORES

Looking for Oracle PL/SQL training? Learn PL/SQL from the experts and sky-rocket your career.

PLSQL Collections Explained (Part 1 - associative arrays)

PLSQL Assciative Arrays

Oracle Tips & Tricks to SKYROCKET Your Career!