Coding our first sub-program - Oracle

The previous section dealt with only theory. Now let us implement the above theory. Before implementing the sub-program, we need to know how to implement it. Every sub program (procedure or function) has to specify itself, whether it is procedure or function, followed by its own name. First of all consider the following very simple program:

declare

            procedure displayMsg as

            begin

                        dbms_output.put_line('Displaying the message from displayMsg');

            end;

BEGIN

            dbms_output.put_line('msg before calling sub-program');

            displayMsg;

            dbms_output.put_line('msg after calling sub-program');

END;

From the above program, we can see that the sub-program is a procedure and it is named as “displayMsg”. It has its own set of statements (in this case only one DBMS_OUTPUT statement) enclosed within “begin” and “end”.

In the above program, there exists a set of statements between capital BEGIN and capital END. It is just for clarity. Those are the statements of the main program. The program starts its execution as usual at the “declare” statement. And from there it jumps to “BEGIN” (capital) and continues its execution from there onwards. Try to observe that the procedure will not be executed automatically (like anonymous or nested blocks). 

Within the statements of the main program (between capital BEGIN and capital END), the first statement displays some message. After that it calls the sub-program directly with its name (in this case it is “displayMsg”). So, now the flow of execution suddenly jumps to the ‘begin' statement of the sub-program.  It executes all the statements within that ‘begin’ and ‘end’ (in this case it is only one DBMS_OUTPUT statement). 

Once it finishes the execution of the procedure, the control returns back to the next statement of main program i.e., the next statement involved with calling the procedure, which is the final DBMS_OUTPUT statement.

I hope the explanation is very clear in all the aspects.