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