Database Interaction with PL/SQL: Sub-programs in Depth

Please note that all the examples in this series have been tested only with Oracle 10g. I didn’t really test them with all the previous versions of Oracle. I suggest you to refer the documentation of respective version you are using, if any of the programs failed to execute.

Sub-programs calling other sub-programs

This is a very frequently used technique in any structured programming language. In my previous article, I stated that a sub-program needs to be executed from the main program. A sub-program can also be executed from another sub-program. In that way, they can be executed any number of times. 

So, in reality, we may not know when a sub-program gets executed. It may be executed from our main program, another sub-program, or even from an external source, in the case of stored procedures (discussed later).

Let’s go through a simple (but slightly confusing) example:

declare

            procedure dispMsg as

            begin

                        dbms_output.put_line('from dispMsg');

            end;

            procedure printMsg as

            begin

                        dbms_output.put_line('from printMsg');

                        dispMsg;

                        dbms_output.put_line('from printMsg2');

            end;

BEGIN

            dispMsg;

            printMsg;

            dbms_output.put_line('Back to main');

END;

The output of the above program would be as follows:

from dispMsg
from printMsg
from dispMsg

Now let us discuss the above program in detail. The program starts its execution at BEGIN (of course after ‘declare’). The first statement after BEGIN is ‘dispMsg’. That means I am immediately executing the sub-program ‘dispMsg’ (which displays the first message as ‘from dispMsg’). 

The next statement within the main program is ‘printMsg’. Now the flow of control jumps to ‘printMsg’. It executes the first statement within the ‘printMsg’ and displays the message ‘from printMsg’. Next it executes the second statement within the ‘printMsg’, which is ‘dispMsg’. The control again jumps to ‘dispMsg’ and executes it (which displays the message ‘from dispMsg’). After finishing ‘dispMsg’ the control returns back to the third statement of ‘printMsg’ (which displays the message ‘from printMsg2’). And finally, the control returns back the last statement of the main program (which displays ‘back to main’).

The most important issue to remember is that the control always returns back to the next statement of the calling statement (of sub-program).