HomeOracle Page 4 - Associative Arrays in Oracle PL/SQL: The Best Approach
The final best approach to working professionally and safely with encapsulation - Oracle
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.
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!
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);