More sub-programs - Oracle

This is part 12 of a series of articles focusing on database interactions with Oracle PL/SQL. In my previous article, I looked at several examples of explicit cursors. I also introduced the concept of cursors with parameters. In this article we will look into sub-programs. Mainly we will concentrate on procedures and discuss some issues of variable scoping with respect to sub-programs.

TABLE OF CONTENTS:

By: Jagadish Chatarji

Poor Best

Rating:

/ 18

August 09, 2005

print this article

SEARCH DEV SHED

TOOLS YOU CAN USE

How many sub-programs can we write within a single program? The answer is AS MANY AS YOU CAN. You can declare and define any number of sub-programs within a single main program. There is no limit at all. Let us consider the following example:

declare

x number := 30;

y number := 40;

procedure displaySum as

a number := 10;

b number := 20;

begin

dbms_output.put_line('Sum of numbers :' || (a+b+x+y) );

end;

procedure displayProduct as

a number := 10;

b number := 20;

begin

dbms_output.put_line('Product of numbers :' || (a*b*x*y) );

end;

procedure displayAvg as

a number := 10;

b number := 20;

begin

dbms_output.put_line('Avg of numbers :' || ((a+b+x+y)/4) );

end;

begin

displaySum;

displayProduct;

displayAvg;

end;

Even though the above program is bit lengthy, the concept is very simple. I just used three sub-programs, namely ‘displaySum’, ‘displayProduct’ and ‘displayAvg’. Each of those sub-programs has its own declarations of variables and logic. And I am calling all the sub-programs from within the main program.

There is no particular order you can call a sub-program from within the main program. You can call sub-programs in any order without regard to the order (of the sub-programs) you defined.