PL/SQL : associative arrays

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