Associative Arrays in Oracle PL/SQL: The Best Approach - The final best approach to working professionally and safely (Page 4 of 4 )
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
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
