Associative Arrays in Oracle PL/SQL: The Professional Approach

This is the second article in a series focusing on associative arrays. In this article, we shall examine recommended methodologies for working with associative arrays efficiently.

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.

{mospagebreak title=A professional approach to traversing an associative array using Oracle PL/SQL}

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!

{mospagebreak title=Bottom to top traverse of an associative array using Oracle PL/SQL}

In the last sections, we have already seen several pre-defined members associated with associative arrays.  Now, let us look into the same scenario with a small difference in traversing.

The last two sections concentrated on a top-down approach to traversing an associative array.  Now, we shall focus on a bottom-top approach to traversing the same.  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.last;
      while i >= year_sales.first
      loop
            tot_sales := tot_sales + year_sales(i);
            dbms_output.put_line(‘Sales of ‘ || i || ‘: ‘ ||
year_sales(i));
            i := year_sales.prior(i);
      end loop;
      dbms_output.put_line(‘Total sales: ‘ || tot_sales);
      dbms_output.put_line(‘Avg sales: ‘ ||
(tot_sales/year_sales.count) );

end;

Shown below are the only modifications I made to the above code.  I replaced the following:

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;

with this:

      i := year_sales.last;
      while i >= year_sales.
first
      loop
            tot_sales := tot_sales + year_sales(i);
            dbms_output.put_line(‘Sales of ‘ || i || ‘: ‘ ||
year_sales(i));
            i := year_sales.prior(i);
      end loop;

Please observe the color highlighting in the code fragments above.  The rest is the same, and I leave it for you to understand.

{mospagebreak title=Deleting individual elements in an associative array using Oracle PL/SQL}

Until now, we simply added pairs of data to an associative array.  Now, we shall examine how to delete pairs of data from an existing associative array. 

Let us consider the following code:

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

      i := year_profits.first;
      while i <= year_profits.last
      loop
            if year_profits(i) < 0 then
                  year_profits.delete(i);
            end if;
            i := year_profits.next(i);
      end loop;

      i := year_profits.first;
      while i <= year_profits.last
      loop
            tot_profits := tot_profits + year_profits(i);
            i := year_profits.next(i);
      end loop;
      dbms_output.put_line(‘Total profits: ‘ || tot_profits);
      dbms_output.put_line(‘No. of years in profit: ‘ ||
year_profits.count);

end;

The highlighted part checks for any negative numbers within the values available in the associative array.  If anything is found, it gets deleted automatically using the DELETE member (which is also pre-defined).

In the next section, we shall examine how to delete more than one element at a time.

{mospagebreak title=Deleting a set of elements in an associative array using Oracle PL/SQL}

In the previous section, I deleted only individual elements.  In this section, I shall introduce you to deleting a series of elements (pairs) in an associate array.

Let us consider the following code:

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

      year_profits.delete(1991,1995);

      i := year_profits.first;
      while i <= year_profits.last
      loop
            tot_profits := tot_profits + year_profits(i);
            i := year_profits.next(i);
      end loop;

      dbms_output.put_line(‘Total profits: ‘ || tot_profits);
      dbms_output.put_line(‘No. of years excluding years between
1991 and 1995: ‘ || year_profits.count);

end;

Within the above code, you can understand that it removes all the elements (or pairs) from the associative array, which are in between the keys 1991 and 1995 (both inclusive).

Summary

Until now, I focused on using only BINARY_INTEGER based “keys” for the pairs that are going to be stored in the associative array.  In fact, you can also try using all of these methods with VARCHAR2.

When you work with a VARCHAR2 based indexed, it works very similar to a hash table.  If we embed the whole code in a beautiful package, I am quite sure that it would help you a lot.  We can further extend this with CURSORS, REF CURSORS, OBJECT TYPES, VARRAYS and so on.  Even though I concentrated only on  associative arrays, several of these methods should work with other types of PL/SQL tables as well.  Go ahead and try it. 

In the next article in this series, I shall mix all these methods in a modular fashion and finally implement it in a reusable package.  So be sure to check this website frequently! 

Any comments, suggestions, feedback, bugs, errors, enhancements are highly appreciated at jag_chat@yahoo.com

[gp-comments width="770" linklove="off" ]

chat sex hikayeleri