Associative Arrays in Oracle PL/SQL: traversing consecutively using a FOR loop - Oracle

The example in the previous section simply contains three pairs of values.  If I have tons of pairs, how would I retrieve the values?  This section addresses that issue.

Let us consider the following example first:

declare
      type year_type is table of number index by binary_integer;
      year_sales year_type;
      tot_sales   number := 0;
      i           number;
begin
      year_sales(1990) := 34000;
      year_sales(1991) := 45000;
      year_sales(1992) := 43000;
      year_sales(1993) := 13000;
      year_sales(1994) := 53000;

      for i in 1990..1994
      loop
            tot_sales := tot_sales + year_sales(i);
            dbms_output.put_line('Sales of ' || i || ': ' ||
year_sales(i));
      end loop;
      dbms_output.put_line('Total sales: ' || tot_sales);

end;

Within the above example, I added a few more pairs of data and used a FOR loop to traverse through every pair within the “Associative Array.”  According to the above example, we should know the “least key” and “highest key.” 

Here, the loop variable acts as the main “key” holder and repeats through all the “keys” with a default increment of 1. The “keys” must be consecutive to work with the above example.

Associative Arrays in Oracle PL/SQL: traversing and jumping within FOR loop

No programmer would ever be satisfied with the FOR loop I demonstrated within the previous section.  The example in the previous section works, but only if you work with “consecutive numbering” or “consecutive keys,” which is not the situation every time in databases!

Sometimes the “keys” may not be in any order.  And sometimes, a few “keys” may be missing!  How do we handle such situations?  I address these issues in this section.  Let us modify the previous example, so that it reaches our expectations.

declare
      type year_type is table of number index by binary_integer;
      year_sales year_type;
      tot_sales   number := 0;
      i           number;
begin
      year_sales(1990) := 34000;
      year_sales(1991) := 45000;
      year_sales(1992) := 43000;
      year_sales(1996) := 13000;
      year_sales(1998) := 53000;

      for i in 1990..2000
      loop
            if year_sales.exists(i) then
                  tot_sales := tot_sales + year_sales(i);
                  dbms_output.put_line('Sales of ' || i || ': '
|| year_sales(i));
            end if;
      end loop;
      dbms_output.put_line('Total sales: ' || tot_sales);

end;

This time I worked more intelligently.  I started from the “least key” and went through the “highest key.”  I am testing whether the “key” really exists in my “Associative Array” or not, using an IF condition as follows:

            if year_sales.exists(i) then

The keyword “exists” is a pre-defined member to work with any “Associative Array.”  It simply tests whether the “key” exists or not and sends out a “Boolean” value.  Now, our “keys” could be in any order and we can even miss several “keys” in between.