Pl/sql Associative array

When you want to search for the value of a particular element rather than the entire array, the Pl/sql associative array is useful for arrays of name-value pairs of types. Applying 11g Documents "It's like a simple version of a SQL table, you can extract values based on the value of the primary key." In Pl/sql, the efficiency is highest for any array when used in fewer records, such as simple lookup tables. If you find that you're loading thousands of records into an array, then you're probably doing something wrong.

The following is an example of a typical name-value pair:

Note that I can simply access an element by name without having to loop. The next example shows how to loop through an associative array. This may appear to be contrary to the intent of the associative array, but it is possible to have this requirement:

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

Finally, a more complex example of using a common record:

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 ': = ' Neil Armstrong ';
Apollo_commanders (' apollo ': = ' Pete Conrad ';
Apollo_commanders (' apollo ': = ' James Lovell ';
Apollo_commanders (' apollo ': = ' Alan Shepard ';
Apollo_commanders (' apollo ': = ' David Scott ';
Apollo_commanders (' apollo ': = ' John W. Young ';
Apollo_commanders (' apollo ': = ' 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, Commander:neil Armstrong
–mission:apollo, Commander:pete Conrad
–mission:apollo, Commander:james Lovell
–mission:apollo, Commander:alan Shepard
–mission:apollo, Commander:david Scott.
–mission:apollo, Commander:john W. Young
–mission:apollo, Commander:eugene A. Cernan