HomeOracle 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.
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:
It would automatically call the first sub-program (because you didnít provide any parameters). If you execute with the following statement:
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.