Consider this an add-on to my long PL/SQL series on DevShed. If you are new to PL/SQL, I strongly suggest you to go through my huge “Database interaction with PL/SQL” series on this website.

All of 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 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 you run into trouble with any of them. The examples can be directly copied, pasted and tested in your favorite PL/SQL environment. They should work without any change.

**Associative Arrays in ****Oracle PL****/SQL: the beginning**

The “Associative Arrays” are also known as “Index-By” tables in PL/SQL. We all know that a variable can hold only one value (as the variable occupies one memory location based on the data type chosen). An “Associative Array” can also be considered a single variable, but with more than one memory location.

An “Associative Array” can hold a huge amount of information in several memory locations, identified by some “index.” This “index” could simply be an integer or string, or something else.

In general, an “Associative Array” stores pairs of data (either sequentially or non- sequentially). Each pair of data would generally contain a “key” and a “value.” If stored sequentially, the “key” would be nothing but a consecutive number holding the “value.” While storing information into an “Associative Array,” the user needs to specify both “key” and “value.”

This concept looks very similar to the concept of arrays in C/C++. In C/C++ an array is a continuous memory of several locations starting with zero as index. But, here the “index” could be anything (there is no strict rule for working with the “index”).

A typical “Associative Array” in PL/SQL could be declared like this:

TYPE year_type IS TABLE OF number INDEX BY BINARY_INTEGER;

The above single statement contains a lot of meaning. First of all, we are simply defining our own data type, “year_type.” Make sure that “year_type” is not a variable. It is a user defined data type. According to the above statement, “year_type” is a data type which can hold a set (or table) of values (typically of type “number”), organized with a BINARY_INTEGER index. The BINARY_INTEGER in this scenario simply acts as a location number or position of the memory location within the table (or simply called an index).

The next section gives you a better understanding with a simple example.

{mospagebreak title=Associative Arrays in Oracle PL/SQL: a simple example}

I already explained Associative Arrays in the previous section. Now, we shall work more practically. Let us consider the following example:

declare

type year_type is table of number index by binary_integer;

year_sales year_type;

tot_sales number;

begin

year_sales(1990) := 34000;

year_sales(1991) := 45000;

year_sales(1992) := 43000;

tot_sales := year_sales(1990) + year_sales(1991) +

year_sales(1992);

dbms_output.put_line(‘Total sales: ‘ || tot_sales);

end;

I shall explain the above example part by part. Let us start with the following statements first:

type year_type is table of number index by binary_integer;

year_sales year_type;

Repeating from the previous section, the “year_type” is a user-defined data type which can hold a set (or table) of values (typically of type “number”), 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.” The pair of data should now contain a “key” (of type BINARY_INTEGER) and a “value” (of type NUMBER). Further proceeding we have the following statements:

year_sales(1990) := 34000;

year_sales(1991) := 45000;

year_sales(1992) := 43000;

From the above three statements, you can simply consider that 1990, 1991 and 1992 are “keys” (of type BINARY_INTEGER). Similarly, we have 34000, 45000 and 43000 which are simply “values” (of type NUMBER). So, we added three pairs of data to a single variable, “year_sales.” That’s the trick. Further proceeding we have the following:

tot_sales := year_sales(1990) + year_sales(1991) +

year_sales(1992);

dbms_output.put_line(‘Total sales: ‘ || tot_sales);

The first statement simply retrieves the “values” available at “keys” 1990, 1991 and 1992 (which are nothing but 34000, 45000 and 43000) and finally adds them into a new simple variable, “tot_sales” (of type NUMBER). The second statement simply displays the result.

The above example is the simplest on earth. Let’s try something a bit harder.

{mospagebreak title=Associative Arrays in Oracle PL/SQL: traversing consecutively using a FOR loop}

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.

{mospagebreak title=Associative Arrays in Oracle PL/SQL: counting number of elements using FOR loop}

Let us extend our discussion further to count the number of elements available in the “Associative Array.” Let us work with the following example:

declare

type year_type is table of number index by binary_integer;

year_sales year_type;

tot_sales number := 0;

i number;

c number := 0;

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));

c := c + 1;

end if;

end loop;

dbms_output.put_line(‘Total sales: ‘ || tot_sales);

dbms_output.put_line(‘Avg sales: ‘ || (tot_sales/c) );

end;

I modified the example to include “average sales.” The concept of “Average” works based on the “number of items” available. How do we keep track of the “number of elements” within the “Associative Array”?

I simply defined my own counter variable, “c,” to count the number of elements available in the “Associative Array.” It really worked.

**Summary**

Are you satisfied with the techniques I implemented here? A beginner may say YES. But a more experienced programmer would never agree (at least to 100%) to any of the above methods. I should also agree with them. Why and what is the problem in working with the above methods?

In all of the previous scenarios, we should definitely know (or expect) the “least possible key” and the “highest possible key,” which is ridiculous. And that isn’t the only problem. If I have huge gaps in between “keys,” the loop gets iterated unnecessarily (degrading the performance). This is another major disadvantage of using the above method.

My next article in this series will address all of these issues, and we shall further extend the series to a more structural, efficient and modular way of working with “Associative Arrays.” Don’t forget to keep an eye on this website for the next article (or simply sign up for the newsletter). Any comments, suggestions, feedback, bugs, errors, enhancements are highly appreciated at **jag_chat@yahoo.com**