Home arrow Oracle arrow Page 2 - Associative Arrays in Oracle PL/SQL: The Professional Approach

A professional approach to traversing an associative array using Oracle PL/SQL - Oracle

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.

TABLE OF CONTENTS:
  1. Associative Arrays in Oracle PL/SQL: The Professional Approach
  2. A professional approach to traversing an associative array using Oracle PL/SQL
  3. Bottom to top traverse of an associative array using Oracle PL/SQL
  4. Deleting individual elements in an associative array using Oracle PL/SQL
  5. Deleting a set of elements in an associative array using Oracle PL/SQL
By: Jagadish Chatarji
Rating: starstarstarstarstar / 22
March 20, 2006

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

Let us concentrate on the problems with my previous approaches.  There exist mainly two issues to deal with. 

The first is that in all of the examples in my previous article, we should definitely know (or expect) the “least possible key” and the “highest possible key,” which is quite ridiculous.  

The second is that if I have huge gaps in between “keys,” the loop gets iterated unnecessarily (degrading the performance).  This is another major disadvantage of using my previous methods.

The following example should definitely solve all those issues.  Let us first go through the 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;

      i := year_sales.first;
      while i <= year_sales.last
      loop
            tot_sales := tot_sales + year_sales(i);
            dbms_output.put_line('Sales of ' || i || ': ' ||
year_sales(i));
            i := year_sales.next(i);
      end loop;
      dbms_output.put_line('Total sales: ' || tot_sales);
      dbms_output.put_line('Avg sales: ' ||
(tot_sales/year_sales.count) );

end;

I shall explain the above code in the next section.

A professional approach to traversing an associative array using Oracle PL/SQL: discussion

The explanation in this section is entirely based on the code listed in the previous section.  Let me explain the same part by part.

      type year_type is table of number index by binary_integer;
      year_sales year_type;

The “year_type” is a user-defined data type which can hold a set (or table) of values (typically of type “number”). It is organized with a BINARY_INTEGER index.  The “year_sales” is a variable based on the data type “year_type.”  Now you can store a huge amount of data (typically in the form of pairs) within the single variable “year_type.”   

Further proceeding we have the statements which add some data to the associative array.  Then we have the following:

      i := year_sales.first;

The above statement gives the first “key” available within the associative array.  The next statement is as follows:

      while i <= year_sales.last

The above is simply a loop which always checks and maintains the current “key” so that it would never cross the last “key.”  Further proceeding we have the following:

            tot_sales := tot_sales + year_sales(i);
            dbms_output.put_line('Sales of ' || i || ': ' ||
year_sales(i));

The first statement simply gets the “value” of the pair based on the “key” present in the variable “i” and finally adds up the value with “tot_sales” (which was initialized to zero earlier).  The second statement would simply display the value of “key” and “value” together in a separate line.

            i := year_sales.next(i);

The above statement searches for the next “key” from the current “key” available in variable “i.”  The new “key” would be assigned to the same variable and the loop gets iterated accordingly.

Even though the concept is totally rewritten, one should agree now that all the requirements are met professionally!



 
 
>>> More Oracle Articles          >>> More By Jagadish Chatarji
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

ORACLE ARTICLES

- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: