Sub-programs interacting with an Oracle database - Oracle

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.