PL/SQL Associative Arrays

I’ve known of PL/SQL Associative Arrays for quite a while (doc for 11.1, 10.2, 10.1, 9.2), but never really used them before. Wow, I had no idea what I was missing! They’re particularly useful for name-value pair type arrays where you want to look up the value of a particular element without looping over the entire array. To quote the 11g documentation “It is like a simple version of a SQL table where you can retrieve values based on the primary key”.  As with any array in PL/SQL, they are most efficient when used with a small number of rows, such as simple lookup tables.  If you find yourself loading tens of thousands of rows into an array, you’re probably doing something wrong.

I always learn best from examples, but I didn’t really find a lot of sample code for Associative Arrays, which is why I wanted to include a few here based on some data from the Apollo Space Program. Lets start with a classic name-value pair example:

declare
    type assoc_arr is table of varchar2(255) index by varchar2(255);
    apollo_commanders assoc_arr;
begin
    apollo_commanders('Apollo 11') := 'Neil Armstrong';
    apollo_commanders('Apollo 12') := 'Pete Conrad';
    apollo_commanders('Apollo 13') := 'James Lovell';
    apollo_commanders('Apollo 14') := 'Alan Shepard';
    apollo_commanders('Apollo 15') := 'David Scott';
    apollo_commanders('Apollo 16') := 'John W. Young';
    apollo_commanders('Apollo 17') := 'Eugene A. Cernan';

    dbms_output.put_line(apollo_commanders('Apollo 11'));
    dbms_output.put_line(apollo_commanders('Apollo 14'));
end;
/

-- Results:
-- Neil Armstrong
-- Alan Shepard

Notice that I can simply access an element by name, without looping. The next example demonstrates how to loop over an Associative Array. This may seem like it’s contrary to the purpose of Associative Arrays, but I had a need for this and had a hard time finding examples of it:

declare
    type assoc_arr is table of varchar2(255) index by varchar2(255);
    apollo_commanders assoc_arr;
    l_current_mission   varchar2(255);
begin
    apollo_commanders('Apollo 11') := 'Neil Armstrong';
    apollo_commanders('Apollo 12') := 'Pete Conrad';
    apollo_commanders('Apollo 13') := 'James Lovell';
    apollo_commanders('Apollo 14') := 'Alan Shepard';
    apollo_commanders('Apollo 15') := 'David Scott';
    apollo_commanders('Apollo 16') := 'John W. Young';
    apollo_commanders('Apollo 17') := 'Eugene A. Cernan';

    l_current_mission := apollo_commanders.first;
    loop
        exit when l_current_mission is null;
        dbms_output.put_line('Mission: '||l_current_mission||', Commander: '||apollo_commanders(l_current_mission));
        l_current_mission := apollo_commanders.next(l_current_mission);
    end loop;
end;
/

-- Results:
-- Mission: Apollo 11, Commander: Neil Armstrong
-- Mission: Apollo 12, Commander: Pete Conrad
-- Mission: Apollo 13, Commander: James Lovell
-- Mission: Apollo 14, Commander: Alan Shepard
-- Mission: Apollo 15, Commander: David Scott
-- Mission: Apollo 16, Commander: John W. Young
-- Mission: Apollo 17, Commander: Eugene A. Cernan

Finally, a more complex example using a record that I’ve used a lot on a recent project:

declare
    type apollo_rec is record(
        commander   varchar2(100),
        launch      date);
    type apollo_type_arr is table of apollo_rec index by varchar2(100);
    apollo_arr apollo_type_arr;
begin
    apollo_arr('Apollo 11').commander := 'Neil Armstrong';
    apollo_arr('Apollo 11').launch := 	to_date('July 16, 1969','Month dd, yyyy');
    apollo_arr('Apollo 12').commander := 'Pete Conrad';
    apollo_arr('Apollo 12').launch := 	to_date('November 14, 1969','Month dd, yyyy');
    apollo_arr('Apollo 13').commander := 'James Lovell';
    apollo_arr('Apollo 13').launch := 	to_date('April 11, 1970','Month dd, yyyy');
    apollo_arr('Apollo 14').commander := 'Alan Shepard';
    apollo_arr('Apollo 14').launch := 	to_date('January 31, 1971','Month dd, yyyy');  

    dbms_output.put_line(apollo_arr('Apollo 11').commander);
    dbms_output.put_line(apollo_arr('Apollo 11').launch);
end;
/

-- Results:
-- Neil Armstrong
-- 16-JUL-69