Associative Arrays in Oracle PL/SQL: The Best Approach - Executing the PL/SQL sub-programs from within the main program (Page 3 of 4 )
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.
Next: The final best approach to working professionally and safely >>
More Oracle Articles
More By Jagadish Chatarji
