Associative Arrays in Oracle PL/SQL: The Best Approach

This is the third article in a series focusing on associative arrays. In this article, we shall examine recommended methodologies for working with associative arrays even more professionally than in the previous article.

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.

{mospagebreak title=Understanding the procedural approach in PL/SQL: discussion}

In this section, I shall discuss the code presented in the previous section step by step.  Let us start with the following first:

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

The first statement simply defines our own data type, called “year_type.” Please note that “year_type” is not a variable.  It is a user defined data type.  According to the above statement, “year_type” is a data type which can hold a set (or table) of values (typically of type “number”), organized with a BINARY_INTEGER index.  The second statement declares a new variable, “year_profits,” based on the data type “year_type.”  This is the real variable which can hold the data. 

Another important issue to remember is that those two statements are not part of any procedure (or sub-program).  They have been defined as part of the main program.  That means those two can be used in any sub-program belonging to the same program.  To put it simply, any variable or data type declared in the main program can be used by any sub-program belonging to the same program.

Further proceeding we have the following:

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

The above is a “procedure” (basically a sub-program within the main program) named “add_profit.” It has two parameters, “year” and “amount,” of type “number.”  Any sub-program (or “procedure” in this case) would never execute by itself.  It should be called from the main program (or from even another sub-program) using its name (in this case “add_profit”) along with passing values to the parameters. 

Once we call the “procedure” (with two parameter values), it executes the statements inside its own “begin” and “end.” In this case, we have only one statement between “begin” and “end,” which simply adds a new pair (“key” and “value”) within the associative array.

Understanding the procedural approach in PL/SQL: discussion continued

Further proceeding with the code given in the example, we have the following:

  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;

The above procedure simply goes through every element (or pair) available within the associative array and displays the same back onto the screen.  This is quite similar to what I explained in my previous article. The only difference is that I separated the code into a special procedure named “print_profits.”  The procedure does not have any parameters, and you can simply call it with its name from other parts of your program. 

Another issue to note concerns the variable “i.” It is declared as part of the procedure “print_profits.”  That means it can be used only within the procedure and never outside the procedure.  To put it simply, it is private only to the procedure.  This removes the problem of clashing your variables within a big program!

Further proceeding we have the following:

  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;

Even this procedure is very similar to the above, except that I am trying to sum up all the “values” available in the associative array and finally display them back to the screen.  You might have observed that I declared two private variables now, “tot_profits” and “i,” which are available only for the procedure “print_total_profit.”

The next section explains the execution of the above sub-programs.

{mospagebreak title=Executing the PL/SQL sub-programs from within the main program}

This is a continuation from the previous section.  Further proceeding we have the following:

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 is the part of the main program.  In fact, the execution starts at the above “begin” and then calls the procedure “add_profit” with two parameters.  From this statement, the execution jumps to the stored procedure “add_profit” and, well, executes it. 

Once it completes the execution of “add_profit,” the control returns back to the main program and executes the next statement.  In fact, the control jumps from main program to sub program quite a number of times, depending upon necessity.

Finally, we called the “print_profits” and “print_total_profit” without any parameters (as they don’t require them), which prints all that we have added to the associative array.

And thus we complete our discussion on the best approach to work with associative arrays.

Extending the program a bit

Can we still improve the program with a few more procedures? To which my answer is, Why not?  You can still make it more modular and flexible. 

In the above program, I didn’t give any approach for deleting any element within the associative array.  Now, let us extend the above with an additional procedure as follows:

procedure delete_profit(year number) as
      begin
            year_profits.delete(year);
      end;

Even though the above procedure is simple, it really gives you a good opportunity to delete the elements within the associative array in a very simple fashion.  Once you add the above procedure to the previous program (within the “declaration” section), modify the body of your program with the following code:

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

      print_profits;
      print_total_profit;

      dbms_output.put_line(‘—————–‘);
      delete_profit(1991);
      print_profits;
      print_total_profit;
end;

I added a few more statements to the already existing statements, just to demonstrate the functionality of the new procedure we added to the program.  It simply displays all the elements added first and then displays all elements after deletion, separated by a line.

{mospagebreak title=The final best approach to working professionally and safely with encapsulation}

Till now you worked with sub-programs, which are already very good.  Let us consider the following situations:

  • You lost your program somewhere in the file system
  • The previous programmer took the program with him
  • The previous programmer deleted it and resigned the job

And so on.  There is no way we can stop them.  The problem is with the “file system.”  Till now, we have always been working with separate files.  Let us shift our development to the database server itself!

We shall now use the same procedures we developed earlier and create a “package” within the database (so that it never gets removed without proper permissions).  This “package” would now be re-usable, secured, portable and well maintainable!

If you are new to packages, I request that you read my article at http://www.devshed.com/c/a/Oracle/Database-Interaction-with-PLSQL-
User-defined-Packages/

The next section gives you the entire source code for working with packages.

The final best approach to working professionally and safely with encapsulation: the code

The following would be the code for what we discussed in previous section.

create or replace package year_profit as
  type year_type is table of number index by binary_integer;
  year_profits      year_type;

  procedure add_profit(year number, amount number);
  procedure delete_profit(year number);
  procedure print_profits;
  procedure print_total_profit;
  procedure delete_all;

end year_profit;
/
create or replace package body year_profit as

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

  procedure delete_profit(year number) as
  begin
        year_profits.delete(year);
  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;

  procedure delete_all as
  begin
        year_profits.delete;
  end;
end year_profit;
/

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

  year_profit.print_profits;
  year_profit.print_total_profit;

  dbms_output.put_line(‘—————–‘);
  year_profit.delete_profit(1991);
  year_profit.print_profits;
  year_profit.print_total_profit;
end;

This completes our series on associative arrays in PL/SQL.  Any comments, suggestions, feedback, bugs, errors, enhancements are highly appreciated at jag_chat@yahoo.com

[gp-comments width="770" linklove="off" ]

chat