Database Interaction with PL/SQL: Sub-programs in Depth - Sub-programs interacting with an Oracle database
(Page 2 of 5 )
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.
Next: Procedures with parameters >>
More Oracle Articles
More By Jagadish Chatarji
/ 10 
