Associative Arrays in Oracle PL/SQL: a simple example - Oracle

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.