Associative Arrays in Oracle PL/SQL: The Professional Approach (Page 1 of 5 )
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.
Next: A professional approach to traversing an associative array using Oracle PL/SQL >>
More Oracle Articles
More By Jagadish Chatarji
