Associative Arrays in Oracle PL/SQL: The Best Approach (Page 1 of 4 )
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.
Next: Understanding the procedural approach in PL/SQL: discussion >>
More Oracle Articles
More By Jagadish Chatarji
