Home arrow Oracle arrow Page 3 - Associative Arrays in Oracle PL/SQL: The Best Approach

Executing the PL/SQL sub-programs from within the main program - 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

This is a continuation from the previous section.  Further proceeding we have the following:

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 is the part of the main program.  In fact, the execution starts at the above "begin" and then calls the procedure "add_profit" with two parameters.  From this statement, the execution jumps to the stored procedure "add_profit" and, well, executes it. 

Once it completes the execution of "add_profit," the control returns back to the main program and executes the next statement.  In fact, the control jumps from main program to sub program quite a number of times, depending upon necessity.

Finally, we called the "print_profits" and "print_total_profit" without any parameters (as they don't require them), which prints all that we have added to the associative array.

And thus we complete our discussion on the best approach to work with associative arrays.

Extending the program a bit

Can we still improve the program with a few more procedures? To which my answer is, Why not?  You can still make it more modular and flexible. 

In the above program, I didn't give any approach for deleting any element within the associative array.  Now, let us extend the above with an additional procedure as follows:

procedure delete_profit(year number) as
      begin
            year_profits.delete(year);
      end;

Even though the above procedure is simple, it really gives you a good opportunity to delete the elements within the associative array in a very simple fashion.  Once you add the above procedure to the previous program (within the "declaration" section), modify the body of your program with the following code:

begin
      add_profit(1990,23000);
      add_profit(1991,12000);
      add_profit(1992,34000);
      add_profit(1993,45000);

      print_profits;
      print_total_profit;

      dbms_output.put_line('-----------------');
      delete_profit(1991);
      print_profits;
      print_total_profit;
end;

I added a few more statements to the already existing statements, just to demonstrate the functionality of the new procedure we added to the program.  It simply displays all the elements added first and then displays all elements after deletion, separated by a line.



 
 
>>> 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: