Home arrow Oracle arrow Page 2 - Database Interaction with PL/SQL: Sub-programs in Depth

Sub-programs interacting with an Oracle database - Oracle

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.

TABLE OF CONTENTS:
  1. Database Interaction with PL/SQL: Sub-programs in Depth
  2. Sub-programs interacting with an Oracle database
  3. Procedures with parameters
  4. Using %ROWTYPE in parameter declarations
  5. Introduction to FUNCTION
By: Jagadish Chatarji
Rating: starstarstarstarstar / 12
August 16, 2005

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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.



 
 
>>> More Oracle Articles          >>> More By Jagadish Chatarji
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

ORACLE ARTICLES

- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: