Database Interaction with PL/SQL, User-defined Packages - Overloading sub-programs in a PACKAGE
(Page 3 of 5 )
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.
Next: TYPE declarations in package specification >>
More Oracle Articles
More By Jagadish Chatarji
/ 17 
