HomeOracle Page 2 - Database Interaction with PL/SQL: Introduction to Sub-programs
Coding our first sub-program - 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.
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:
procedure displayMsg as
dbms_output.put_line('Displaying the message from displayMsg');
dbms_output.put_line('msg before calling sub-program');
dbms_output.put_line('msg after calling sub-program');
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.