Associative Arrays in Oracle PL/SQL: The Best Approach

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 fairly confident that 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 give you problems.  The examples can be directly copied, pasted and tested in your favorite PL/SQL environment.  They should work without any changes.

In this article, I shall be working with the concept of sub-programs within Oracle PL/SQL.  If you are new to sub-programs in PL/SQL, I strongly suggest you go through the articles at the following links:

http://www.devshed.com/c/a/Oracle/Database-Interaction-with-PLSQL-
Introduction-to-Subprograms/

http://www.devshed.com/c/a/Oracle/Database-Interaction-with-PLSQL-
Subprograms-in-Depth/

Why do we need to work with sub-programs?  Are they really necessary for working with associative arrays in Oracle PL/SQL?  The answer for all such types of questions is based on the following issues:

• Efficiency
• Modularity
• Maintainability
• Structured

By implementing sub-programs, we can really work in an efficient way with simple lines.  Since sub-programs are separated from the main program, it is quite modular and quite maintainable.  Apart from all of these good reasons, we are about to implement the classic "structured programming" within PL/SQL. 

Then what about object oriented programming in PL/SQL?  I shall be writing a few more articles on working with OOPS in PL/SQL in future.  So, I don't want to discuss the issues of OOPS at this moment.

Understanding the procedural approach in PL/SQL: an example

Now, I shall introduce you to the procedural approach to working with associative arrays very efficiently.  Before going into the discussion, consider the following code:

declare
  type year_type is table of number index by binary_integer;
  year_profits      year_type;     

  procedure add_profit(year number, amount number) as
  begin
        year_profits(year) := amount;
  end;

  procedure print_profits as
        i     binary_integer;
  begin
        i := year_profits.first;
        while i <= year_profits.last
        loop
              dbms_output.put_line(i || ': '|| year_profits(i));
              i := year_profits.next(i);
        end loop;
  end;

  procedure print_total_profit as
        tot_profits number := 0;
        i           binary_integer;
  begin
        i := year_profits.first;
        while i <= year_profits.last
        loop
              tot_profits := tot_profits + year_profits(i);
              i := year_profits.next(i);
        end loop;
        dbms_output.put_line('Total Profits:' || tot_profits);
  end;

begin
  add_profit(1990,23000);
  add_profit(1991,12000);
  add_profit(1992,34000);
  add_profit(1993,45000);

  print_profits;
  print_total_profit;
end;

The above program looks a bit long, but actually it is very simple to understand.  The next section gives you a complete understanding of the program.