Associative array

PL/SQL makes available associative arrays that we can use to store key-data pairs.

Here is an example where we create an associative array based on the pair { pls_integer, varchar2(20) } where the integer acts as a key:

declare
  type aa_names_t is table of varchar2(20) index by pls_integer; -- 1.
  l_countries aa_names_t; -- 2.
  l_key pls_integer; -- 3.
begin
  l_countries(714) := 'Belgium'; -- 4.
  l_countries(2) := 'Belize';
  l_countries(98) := 'Brazil';
  l_countries(12) := 'Burkina Faso';

  l_key := l_countries.first(); -- 5.
  while(l_key is not null) -- 6.
  loop
    dbms_output.put_line(l_countries(l_key));
    l_key := l_countries.next(l_key); -- 7.
  end loop;
end;

1. Instead of an explicit varchar, we could have used as type the one of an existing table row, like this: countries.country_name%type
2. Declaration of a local variable of the just created type.
3. Variable used to loop on the associative array's keys.
4. In this way we insert a new element in the associative array.
5. Get the key of the first row.
6. Loop until there is an element available.
7. Get the next key.

Chapter 12 of Oracle PL/SQL Programming, fifth edition, by Steven Feuerstein is about collections.