Home arrow Oracle arrow 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.

TABLE OF CONTENTS:
  1. Associative Arrays in Oracle PL/SQL: The Best Approach
  2. Understanding the procedural approach in PL/SQL: discussion
  3. Executing the PL/SQL sub-programs from within the main program
  4. The final best approach to working professionally and safely
By: Jagadish Chatarji
Rating: starstarstarstarstar / 16
March 27, 2006

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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



 
 
>>> More Oracle Articles          >>> More By Jagadish Chatarji
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

ORACLE ARTICLES

- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: