Database Interaction with PL/SQL: Introduction to Sub-programs

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.

Actually, I need to discuss the concept of REF CURSOR at this moment, but the REF CURSOR is mainly used in sub-programs (or even packages). So, I thought to discuss the sub-programs and packages before going to REF CURSOR.

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.

Introduction to sub-programs

Almost every programming language supports sub-programs. They are a part of structured programming. A sub-program is a part of a main program separated completely from the main program. We can consider it as separate block of statements (with its own declarations and programming statements), but still under the control of the main program.

The above explanation may match a bit with anonymous blocks (or nested blocks) of PL/SQL programs (discussed in my previous set of articles), but sub-programs are totally different from nested blocks.  Nested blocks would definitely execute within the main block. But a sub-program need not do so. How is this possible?

It’s really quite simple. As I explained earlier, it is a separate block, separately named, and the main issue is that it is separately called by the main block. The main block calls the sub-program by its name to execute its set of statements. This may be a part of the conditions as well.  That means the main program may or may not call sub-program based on certain conditions.

Why is this concept necessary? Repeatable tasks (or the tasks which are being executed quite frequently) are generally separated as sub-programs to facilitate the user to use them as many times as possible.  This also improves the clarity (or readability) of the main program, which is being divided into several meaningful tasks, where each task (sub-program) may have its own set of programming statements (including local declarations).

This concept is mainly taken from the initiative of modularization. It is the process by which you break up large blocks of code into smaller pieces called modules (or sub-programs) which can be called by other modules. With the concept of modularization, our code becomes more reusable, more manageable, more readable and finally more reliable.  This is not only for sub-programs; it is being implemented in the form of packages (to be dealt with in my upcoming articles) as well.

There are mainly two kinds of sub-programs: procedures and functions.  Both are very similar in functionality and syntactical issues. The one difference is that a function returns a value, but a procedure doesn’t.  We will look into the concept of functions in-depth later.

{mospagebreak title=Coding our first sub-program}

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.

{mospagebreak title=Variables and scope in sub-programs}

Every block in a PL/SQL program can have its own declaration of variables. This includes the sub-programs as well. 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) );

            end;

           

BEGIN

            dbms_output.put_line(‘Sum is ‘ || (x+y) );

            displaySum;

END;

In the above example, ‘x’ and ‘y’ are the variables declared for the main program (which are outside the procedure declaration). Next, ‘a’ and ‘b’ are the variables declared within the procedure. These variables are used only within the ‘begin’ and ‘end’ of procedure.

Can we use the variables of the main block inside the sub-program?  The answer is YES. We can use the variables of the main program inside the sub-program. But we can’t do the opposite. That means the variables declared within the procedure are local to it and cannot be accessed beyond its boundaries. 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;

           

BEGIN

            dbms_output.put_line(‘Sum is ‘ || (x+y) );

            displaySum;

END;

The only difference between the above two programs is the DBMS_OUTPUT statement within the procedure. In the above program, I am using variables of both the sub-program and the main program within the sub-program, which is perfectly all right.

{mospagebreak title=Variable scope bit in depth}

Before going into bit depth, let us consider the following program.

declare

            a          number := 300;

            b          number := 400;

           

            procedure displaySum as

                        a          number := 10;

                        b          number := 20;

            begin

                        dbms_output.put_line(‘Sum of numbers :’ || (a+b+a+b) );

            end;

           

BEGIN

            dbms_output.put_line(‘Sum is ‘ || (a+b) );

            displaySum;

END;

If you carefully observe the above program, we have the same variables declared in both the main program and the sub-program. If you watch the output of the above program, you will observe that the procedure displays the result as 60, which is a contradiction. I would like to have ‘a’ and ‘b’ of the sub-program and ‘a’ and ‘b’ of the main program added. But, it didn’t happen like that.

That means, the preference of the sub-program in the above program could not access the variables in the main program (as the variables are the same as its own local variables). How do we solve the issue?  This is where the concept of ‘Labeling blocks’ comes in once again. Let us modify the above program.

<<main>>

declare

            a          number := 300;

            b          number := 400;

           

            procedure displaySum as

                        a          number := 10;

                        b          number := 20;

            begin

                        dbms_output.put_line(‘Sum of numbers :’ || (a+b+ main.a+ main.b ) );

            end;

           

BEGIN

            dbms_output.put_line(‘Sum is ‘ || (a+b) );

            displaySum;

END;

If you clearly observe the above program, you can understand that the main program is labeled as “main” and we are able to access the variables of the main program within the sub-program using the convention of “main.variablename”.  Try to observe carefully the DBMS_OUTPUT statement within the sub-program.

{mospagebreak title=More sub-programs}

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.

[gp-comments width="770" linklove="off" ]

chat sex hikayeleri Ensest hikaye