Associative Arrays: Hashtables for PL/SQL

I have been developing software in Java most of my career, but recently my job has required me to take on more projects using Oracle's PL/SQL. Being a Java programmer, I am very well versed in the various collections that are available from the Java API. One collection I find myself using a lot is the Hashtable and recently during a PL/SQL project, I had an algorithm that would be served nicely by such a data structure.

That's when I found out about Associative Arrays in PL/SQL. An associative array is a key-value pair that we can use in very much the same fashion we would use the Hashtable type in Java. In this post I'll show you how to declare a PL/SQL associative array, assign values to the array, then how to access those values. Then I'll go over how to iterate over the PL/SQL associative array.

Declaring, Assigning, and Accessing Associative Arrays

DECLARE

  -- Declare our array type to be keyed on 5 byte varchar and hold values of 256 byte varchar.
  TYPE zip_city_type IS TABLE OF VARCHAR2(256) INDEX BY VARCHAR2(5);
  -- Declare our actual associative array using our new data type.
  zip_city_array zip_city_type;

BEGIN

  -- Let's add some values to our array.
  zip_city_array('80201') := 'DENVER, CO';  -- Adds entry for zip code 80201
  zip_city_array('48201') := 'DETROIT, MI';

  -- Print out the values from our associative array.
  dbms_output.put_line( 'Zip code 80201 is in ' || zip_city_array('80201') );
  dbms_output.put_line( 'Zip code 48201 is in ' || zip_city_array('48201') );

END;

Iterating Through an Associative Array

You will come across situations where you need to iterate through all of the elements in your associative array and you want to do so efficiently. Let's take a look at how we would accomplish iterating through the array we declared above.

DECLARE

  -- Declare our array type to be keyed on 5 byte varchar and hold values of 256 byte varchar.
  TYPE zip_city_type IS TABLE OF VARCHAR2(256) INDEX BY VARCHAR2(5);
  -- Declare our actual associative array using our new data type.
  zip_city_array zip_city_type;
  zip VARCHAR2(5);

BEGIN

  -- Let's add some values to our array.
  zip_city_array('80201') := 'DENVER, CO';  -- Adds entry for zip code 80201
  zip_city_array('48201') := 'DETROIT, MI';

  -- Assign the first key in our array to zip.
  zip := zip_city_array.FIRST;  
 
  -- Loop through the array while our key is null.
  WHILE zip IS NOT NULL LOOP
     dbms_output.put_line( zip || ' = ' || zip_city_array(zip) );
     zip := zip_city_array.NEXT( zip );
  END LOOP;  

END;