Associative Arrays in Oracle PL/SQL: Introduction - Associative Arrays in Oracle PL/SQL: counting number of elements using FOR loop (Page 4 of 4 )
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
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
