Associative Arrays in Oracle PL/SQL: The Professional Approach

All the examples in this article have been tested only with Oracle 10g version 2.  I didn’t really test the examples in any of the previous Oracle versions.  I am confident enough that all of the examples should give no problems when worked with Oracle version 7.3 or above.  You can drop (or post) me a line if any of them does.  The examples can be directly copied, pasted and tested in your favorite PL/SQL environment.  They should work without any changes.

Counting the number of elements in an associative array using Oracle PL/SQL

Before getting into the professional approach, I have a small concept to introduce to you.  In my previous article (in the last section), I counted the number of elements using my own counter (which is less efficient). 

Now, I shall modify the code, which is a bit more efficient than using our own counter.  Let us start with the following code.

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);
      dbms_output.put_line('Avg sales: ' ||
(tot_sales/year_sales.count) );

end;

Within the above code, I introduced a new member into the associative array called “Count.”  It is already pre-defined for you; it keeps track of the number of elements available within the same array.