The final best approach to working professionally and safely with encapsulation - Oracle

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