Home arrow Oracle arrow Page 2 - Associative Arrays in Oracle PL/SQL: Introduction

Associative Arrays in Oracle PL/SQL: a simple example - 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.

TABLE OF CONTENTS:
  1. Associative Arrays in Oracle PL/SQL: Introduction
  2. Associative Arrays in Oracle PL/SQL: a simple example
  3. Associative Arrays in Oracle PL/SQL: traversing consecutively using a FOR loop
  4. Associative Arrays in Oracle PL/SQL: counting number of elements using FOR loop
By: Jagadish Chatarji
Rating: starstarstarstarstar / 119
March 13, 2006

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

I already explained Associative Arrays in the previous section.  Now, we shall work more practically.  Let us consider the following example:

declare
      type year_type is table of number index by binary_integer;
      year_sales year_type;
      tot_sales   number;
begin
      year_sales(1990) := 34000;
      year_sales(1991) := 45000;
      year_sales(1992) := 43000;

      tot_sales := year_sales(1990) + year_sales(1991) +
year_sales(1992);
      dbms_output.put_line('Total sales: ' || tot_sales);
end;

I shall explain the above example part by part.  Let us start with the following statements first:

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

Repeating from the previous section, the “year_type” is a user-defined data type which can hold a set (or table) of values (typically of type “number”), 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.”  The pair of data should now contain a “key” (of type BINARY_INTEGER) and a “value” (of type NUMBER). Further proceeding we have the following statements:

      year_sales(1990) := 34000;
      year_sales(1991) := 45000;
      year_sales(1992) := 43000;

From the above three statements, you can simply consider that 1990, 1991 and 1992 are “keys” (of type BINARY_INTEGER).  Similarly, we have 34000, 45000 and 43000 which are simply “values” (of type NUMBER). So, we added three pairs of data to a single variable, “year_sales.”  That’s the trick.  Further proceeding we have the following:

      tot_sales := year_sales(1990) + year_sales(1991) +
year_sales(1992);
      dbms_output.put_line('Total sales: ' || tot_sales);

The first statement simply retrieves the “values” available at “keys” 1990, 1991 and 1992 (which are nothing but 34000, 45000 and 43000) and finally adds them into a new simple variable, “tot_sales” (of type NUMBER).  The second statement simply displays the result. 

The above example is the simplest on earth.  Let’s try something a bit harder.



 
 
>>> 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: