Overloading sub-programs in a PACKAGE - Oracle

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.