A professional approach to traversing an associative array using Oracle PL/SQL - Oracle

Let us concentrate on the problems with my previous approaches.  There exist mainly two issues to deal with. 

The first is that in all of the examples in my previous article, we should definitely know (or expect) the “least possible key” and the “highest possible key,” which is quite ridiculous.  

The second is that if I have huge gaps in between “keys,” the loop gets iterated unnecessarily (degrading the performance).  This is another major disadvantage of using my previous methods.

The following example should definitely solve all those issues.  Let us first go through the 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;

      i := year_sales.first;
      while i <= year_sales.last
      loop
            tot_sales := tot_sales + year_sales(i);
            dbms_output.put_line('Sales of ' || i || ': ' ||
year_sales(i));
            i := year_sales.next(i);
      end loop;
      dbms_output.put_line('Total sales: ' || tot_sales);
      dbms_output.put_line('Avg sales: ' ||
(tot_sales/year_sales.count) );

end;

I shall explain the above code in the next section.

A professional approach to traversing an associative array using Oracle PL/SQL: discussion

The explanation in this section is entirely based on the code listed in the previous section.  Let me explain the same part by part.

      type year_type is table of number index by binary_integer;
      year_sales year_type;

The “year_type” is a user-defined data type which can hold a set (or table) of values (typically of type “number”). It is organized with a BINARY_INTEGER index.  The “year_sales” is a variable based on the data type “year_type.”  Now you can store a huge amount of data (typically in the form of pairs) within the single variable “year_type.”   

Further proceeding we have the statements which add some data to the associative array.  Then we have the following:

      i := year_sales.first;

The above statement gives the first “key” available within the associative array.  The next statement is as follows:

      while i <= year_sales.last

The above is simply a loop which always checks and maintains the current “key” so that it would never cross the last “key.”  Further proceeding we have the following:

            tot_sales := tot_sales + year_sales(i);
            dbms_output.put_line('Sales of ' || i || ': ' ||
year_sales(i));

The first statement simply gets the “value” of the pair based on the “key” present in the variable “i” and finally adds up the value with “tot_sales” (which was initialized to zero earlier).  The second statement would simply display the value of “key” and “value” together in a separate line.

            i := year_sales.next(i);

The above statement searches for the next “key” from the current “key” available in variable “i.”  The new “key” would be assigned to the same variable and the loop gets iterated accordingly.

Even though the concept is totally rewritten, one should agree now that all the requirements are met professionally!