Associative Arrays in Oracle PL/SQL: Introduction - Associative Arrays in Oracle PL/SQL: traversing consecutively using a FOR loop (Page 3 of 4 )
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.
Next: Associative Arrays in Oracle PL/SQL: counting number of elements using FOR loop >>
More Oracle Articles
More By Jagadish Chatarji
