HomeOracle Page 4 - Associative Arrays in Oracle PL/SQL: Introduction
Associative Arrays in Oracle PL/SQL: counting number of elements using FOR loop - Oracle
In this series of articles, we shall concentrate on working with associative arrays in Oracle PL/SQL. Even though I start with simple examples in the first article of this series, I shall introduce you (in my upcoming articles) to the power of much more advanced techniques using associative arrays with Oracle PL/SQL.
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) );
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.
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 email@example.com