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

Understanding the procedural approach in PL/SQL: discussion - 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.

  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



In this section, I shall discuss the code presented in the previous section step by step.  Let us start with the following first:

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

The first statement simply defines our own data type, called "year_type." Please note that "year_type" is not a variable.  It is a user defined data type.  According to the above statement, "year_type" is a data type which can hold a set (or table) of values (typically of type "number"), organized with a BINARY_INTEGER index.  The second statement declares a new variable, "year_profits," based on the data type "year_type."  This is the real variable which can hold the data. 

Another important issue to remember is that those two statements are not part of any procedure (or sub-program).  They have been defined as part of the main program.  That means those two can be used in any sub-program belonging to the same program.  To put it simply, any variable or data type declared in the main program can be used by any sub-program belonging to the same program.

Further proceeding we have the following:

procedure add_profit(year number, amount number) as
            year_profits(year) := amount;

The above is a "procedure" (basically a sub-program within the main program) named "add_profit." It has two parameters, "year" and "amount," of type "number."  Any sub-program (or "procedure" in this case) would never execute by itself.  It should be called from the main program (or from even another sub-program) using its name (in this case "add_profit") along with passing values to the parameters. 

Once we call the "procedure" (with two parameter values), it executes the statements inside its own "begin" and "end." In this case, we have only one statement between "begin" and "end," which simply adds a new pair ("key" and "value") within the associative array.

Understanding the procedural approach in PL/SQL: discussion continued

Further proceeding with the code given in the example, we have the following:

  procedure print_profits as
        i     binary_integer;
        i := year_profits.first;
        while i <= year_profits.last
              dbms_output.put_line(i || ': '|| year_profits(i));
              i := year_profits.next(i);
        end loop;

The above procedure simply goes through every element (or pair) available within the associative array and displays the same back onto the screen.  This is quite similar to what I explained in my previous article. The only difference is that I separated the code into a special procedure named "print_profits."  The procedure does not have any parameters, and you can simply call it with its name from other parts of your program. 

Another issue to note concerns the variable "i." It is declared as part of the procedure "print_profits."  That means it can be used only within the procedure and never outside the procedure.  To put it simply, it is private only to the procedure.  This removes the problem of clashing your variables within a big program!

Further proceeding we have the following:

  procedure print_total_profit as
        tot_profits number := 0;
        i           binary_integer;
        i := year_profits.first;
        while i <= year_profits.last
              tot_profits := tot_profits + year_profits(i);
              i := year_profits.next(i);
        end loop;
        dbms_output.put_line('Total Profits:' || tot_profits);

Even this procedure is very similar to the above, except that I am trying to sum up all the "values" available in the associative array and finally display them back to the screen.  You might have observed that I declared two private variables now, "tot_profits" and "i," which are available only for the procedure "print_total_profit."

The next section explains the execution of the above sub-programs.

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

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


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