Home arrow Oracle arrow Page 3 - Database Interaction with PL/SQL, User-defined Packages

Overloading sub-programs in a PACKAGE - Oracle

This article is part of a series focusing on database interactions with Oracle PL/SQL. In my previous article, we examined named notation, default values of parameters, stored procedures, stored functions and finally took our first look at package and package body. In this article, we will focus completely on package and package body. Before reading this article I suggest you to go through my last three articles in this series thoroughly.

TABLE OF CONTENTS:
  1. Database Interaction with PL/SQL, User-defined Packages
  2. Database interaction using a PACKAGE
  3. Overloading sub-programs in a PACKAGE
  4. TYPE declarations in package specification
  5. How the above package works
By: Jagadish Chatarji
Rating: starstarstarstarstar / 29
September 12, 2005

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

Those who are familiar with Object Oriented Programming (OOP) can easily understand the concept of overloading.  Briefly, having the same name for different sub-programs with different parameters can be called overloading sub-programs.  The following example gives you an illustration:

create or replace package SamplePkg as
    procedure dispEmp;
    procedure dispEmp(p_deptno dept.deptno%type);
end SamplePkg;
/

create or replace package body SamplePkg as
     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 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;

end SamplePkg;
/

Within the above package, we declared two sub-programs as having the same name.  But you should observe that the first sub-program doesnít have any parameters, whereas the second sub-program does.  If you execute with the following statement:

Execute SamplePkg.dispEmp;

It would automatically call the first sub-program (because you didnít provide any parameters).  If you execute with the following statement:

Execute SamplePkg.dispEmp(10);

It would automatically choose the second sub-program (because we provided a parameter).

So, the selection of the respective sub-program will be automatically chosen by PL/SQL runtime based on the parameters we send.  In that way, we can write as many sub-programs as possible with the same name, but with some differences in parameters.



 
 
>>> 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: