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

This is part 13 of a series of articles focusing on database interactions with Oracle PL/SQL. In my previous article, we concentrated on procedures and discussed some issues of variable scoping with respect to sub-programs. In this article we proceed with some tips on using procedures together with the introduction to functions.

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). 

{mospagebreak title=Sub-programs interacting with an Oracle database}

My previous article, and even until now, I only focused on general programs using sub-programs. I didn’t interact with database information at all. Now, let us see how to interact with a database by using a simple example:

declare

            procedure dispEmp as

                        cursor c_emp is

                                    select ename, sal from emp;

            begin

                        for r_emp in c_emp

                        loop

                                    dbms_output.put_line(r_emp.ename || ‘,’ || r_emp.sal);  

                        end loop;

            end;

BEGIN

            dbms_output.put_line(‘—————–‘);

            dbms_output.put_line(‘EMPLOYEES’);

            dbms_output.put_line(‘—————–‘);

            dispEmp;

END;

I hope the above program is self explanatory. I just used the concept of CURSOR (discussed in my previous articles) within the sub-program to interact with the database. And I am executing the sub-program from the main program. Let us further extend the above program by adding one more sub-program as follows:

declare

            procedure dispEmp as

                        cursor c_emp is

                                    select ename, sal from emp;

            begin

                        for r_emp in c_emp

                        loop

                                    dbms_output.put_line(r_emp.ename || ‘,’ || r_emp.sal);  

                        end loop;

            end;

            procedure dispDept as

                        cursor c_dept is

                                    select deptno,dname from dept;

            begin

                        for r_dept in c_dept

                        loop

                                    dbms_output.put_line(r_dept.deptno || ‘,’ || r_dept.dname);

                        end loop;

            end;

BEGIN

            dbms_output.put_line(‘—————–‘);

            dbms_output.put_line(‘EMPLOYEES’);

            dbms_output.put_line(‘—————–‘);

            dispEmp;

            dbms_output.put_line(‘—————–‘);

            dbms_output.put_line(‘DEPARTMENTS’);

            dbms_output.put_line(‘—————–‘);

            dispDept;

END;

Actually there exists nothing new in the above program. We already covered each and every statement earlier. The only difference is the logic. So, let’s start at BEGIN (capital BEGIN). I displayed a heading ‘EMPLOYEES’ (over-lined and under-lined as well). The next statement is “dispEmp”. The control jumps to ‘dispEmp’. 

The sub-program ‘dispEmp’ just displays the whole information for employees using CURSOR (discussed in my previous articles). After the completion of sub-program ‘dispEmp’, the control returns back to the next statement of the calling statement at the main program. Here the calling statement is the statement which calls the sub-program (which is ‘dispEmp’ in main program). Again it displays a heading ‘DEPARTMENTS’ (over-lined and under-lined as well). The next statement is ‘dispDept’. Now, the control jumps to ‘dispDept’. The sub-program ‘dispDept’ just displays the whole information for departments using CURSOR.

I gave this example to help you understand only the flow of control in between the main program and sub-programs.

{mospagebreak title=Procedures with parameters}

Parameters (or arguments) are very common in almost any type of programming language. If you know SQL (obviously), you will see several SQL functions like SUM, MAX, TO_UPPER and so on. All of them accept parameters (or arguments). The values you send to those functions are nothing but the parameters.

The value you send as a parameter to a function should match the data type it can accept. For example, the SUM function accepts only column as a parameter. More specificly, it accepts only a numeric column. So every parameter has to be defined with its own data type and width.

Coming to our scenario, even a sub-program can be declared and defined with parameters as well. But, we need to care about the data type, width and other stuff. The declaration of a parameter will be very similar to that of a variable declaration. And these are nearly similar to ‘cursors with parameters’ discussed in part 11 of this series. Consider the following example:

declare

            procedure dispEmp(p_deptno dept.deptno%type) as

                        cursor c_emp is

                                    select ename, sal from emp

                                    where deptno = p_deptno;

            begin

                        for r_emp in c_emp

                        loop

                                    dbms_output.put_line(r_emp.ename || ‘,’ || r_emp.sal);  

                        end loop;

            end;

BEGIN

            dbms_output.put_line(‘—————–‘);

            dbms_output.put_line(‘EMPLOYEES’);

            dbms_output.put_line(‘—————–‘);

            dispEmp(10);

END;

For the sake of clarity, let us rewrite the above with only the important statements:

declare

            procedure dispEmp(p_deptno dept.deptno%type) as

.

.

.

            begin

                        .

                        .

            end;

BEGIN

            .

.

.

            dispEmp(10);

END;

I hope I need not explain any more. A value ‘10’ is being passed from th  main program to the sub-program using the statement ‘dispEmp(10)’.  This value is caught in ‘p_deptno’ and finally used in the SELECT statement of the CURSOR declaration.

{mospagebreak title=Using %ROWTYPE in parameter declarations}

In the previous section, I used the parameter with a simple data type (or with %TYPE). We can also use parameters with %ROWTYPE.  Let us consider the following example:

declare

            cursor c_emp is

                        select * from emp;

            procedure dispEmp(p_emp emp%rowtype) as

            begin

                        dbms_output.put_line(‘No : ‘ || p_emp.empno);

                        dbms_output.put_line(‘Name : ‘ || p_emp.ename);

                        dbms_output.put_line(‘Salary : ‘ || p_emp.sal);

                        dbms_output.put_line(‘Deptno : ‘ || p_emp.deptno);

                        dbms_output.put_line(‘———————–‘);

            end;

BEGIN

            for r_emp in c_emp

            loop

                        dispEmp(r_emp);

            end loop;

END;

If you clearly observe the above program, I am using the procedure ‘dispEmp’ to display the information of an employee line by line. I am using CURSOR outside the procedure. That means the cursor is accessible to both the main program and procedure ‘dispEmp’ as well.  But my intention for ‘dispEmp’ is just to display the information of a given employee (actually passed through parameter).

In our main program, for each iteration of the FOR loop, I am passing an entire row of an employee to the parameter ‘p_emp’ of the procedure ‘dispEmp’. The variable ‘p_emp’ is declared as of type ‘emp%rowtype’.  So, it can hold an entire row from the table ‘emp’ (which is the same as CURSOR).

The output of the above program will be something like printing labels for all employees.

{mospagebreak title=Introduction to FUNCTION}

In my previous article, I introduced the concept of FUNCTION, but not in depth. A FUNCTION is also a type of sub-program. It is very similar to PROCEDURE. A PROCEDURE doesn’t return any value back to the main program. But a FUNCTION does return back. A PROCEDURE is used for one way communication from the main program. That means you can only send values (in the form of parameters) from the main program to the PROCEDURE. But in turn, the PROCEDURE doesn’t send anything back the main program.

A FUNCTION is used for two way communication. It can accept values through parameters from the main program and also send information back to the main program. The syntax of a FUNCTION is very similar to that of a PROCEDURE except that we need to define its RETURN data type as well. The following is a demonstration.

declare

            x          number;

            function getSum(a number, b number) return number is

                        c          number;

            begin

                        c := a + b;

                        return c;

            end;

BEGIN

            x := getSum(10,20);

            dbms_output.put_line(‘Sum = ‘ || x);     

END;

From the above program, ‘x’ is a variable declared in the main program, ‘c’ is a variable declared within a function (it can be used only locally within a function), and ‘a’ and ‘b’ are parameters of the function (even these can be used only locally within function).

The first statement in the main program is as follows:

            x := getSum(10,20);

The above statement actually calls the function ‘getSum’ with two values, 10 and 20. Those two values gets passed (or assigned) to ‘a’ and ‘b’ of the function ‘getSum’ respectively. Within the function, I am calculating ‘c’ and finally returning it back to the main program using the ‘return’ statement. The data type of a value being returned back to the main program (in this case, the data type of ‘c’) should be defined as the ‘return’ data type within the function declaration. The rest is same.

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

chat sex hikayeleri Ensest hikaye