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

Associative Arrays in Oracle PL/SQL: The Best Approach

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

All the examples in this article have been tested only with Oracle 10g version 2.  I didn't really test the examples in any of the previous Oracle versions.  I am fairly confident that the examples should give no problems when worked with Oracle version 7.3 or above.  You can drop (or post) me a line if any of them does give you problems.  The examples can be directly copied, pasted and tested in your favorite PL/SQL environment.  They should work without any changes.

In this article, I shall be working with the concept of sub-programs within Oracle PL/SQL.  If you are new to sub-programs in PL/SQL, I strongly suggest you go through the articles at the following links:

http://www.devshed.com/c/a/Oracle/Database-Interaction-with-PLSQL-
Introduction-to-Subprograms/

http://www.devshed.com/c/a/Oracle/Database-Interaction-with-PLSQL-
Subprograms-in-Depth/

Why do we need to work with sub-programs?  Are they really necessary for working with associative arrays in Oracle PL/SQL?  The answer for all such types of questions is based on the following issues:

  • Efficiency
  • Modularity
  • Maintainability
  • Structured

By implementing sub-programs, we can really work in an efficient way with simple lines.  Since sub-programs are separated from the main program, it is quite modular and quite maintainable.  Apart from all of these good reasons, we are about to implement the classic "structured programming" within PL/SQL. 

Then what about object oriented programming in PL/SQL?  I shall be writing a few more articles on working with OOPS in PL/SQL in future.  So, I don't want to discuss the issues of OOPS at this moment.

Understanding the procedural approach in PL/SQL: an example

Now, I shall introduce you to the procedural approach to working with associative arrays very efficiently.  Before going into the discussion, consider the following code:

declare
  type year_type is table of number index by binary_integer;
  year_profits      year_type;     

  procedure add_profit(year number, amount number) as
  begin
        year_profits(year) := amount;
  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;

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 program looks a bit long, but actually it is very simple to understand.  The next section gives you a complete understanding of the program.



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