OVERLOADING PROCEDURES AND FUNCTIONS

OVERLOADING PROCEDURES AND FUNCTIONS

A Package is an Oracle object that can hold a number of other objects like procedure and functions. More than one procedure and function with the same name but with different parameters can be defined within a package or within a PL/SQL declaration block.

Multiple procedures that are declared with the same name are called Overloaded Procedures. Similarly, multiple functions that are declared with the same name are called Overloaded Functions.
1)
create or replace package p1
as
function f1(x number) return number;
function f1(x number,y number) return number;
end;

create or replace package body p1
as
function f1(x number) return number
as y number;
begin
y:=x+30;
return y;
end f1;
function f1(x number, y number) return number
as z number;
begin
z:=x+y;
return z;
end f1;
end;
To Execute the Package and Package Body
declare
x number;
begin
x:=p1.f1(10);
dbms_output.put_line(x);
end;
----------------------
declare
x number;
begin
x:=p1.f1(10,30);
dbms_output.put_line(x);
end;

2)
create or replace package p1
as
function f1(x number) return boolean;
function f1(x number,y number) return boolean;
end;

create or replace package body p1
as
function f1(x number) return boolean
as
begin
if x >30 then
return true;
else
return false;
end if;
end f1;
function f1(x number,y number) return boolean
as
z number;
begin
z:=x+y;
if z>100 then
return true;
else
return false;
end if;
end f1;
end;

To execute the Program
declare
x boolean;
begin
if p1.f1(60) then
dbms_output.put_line('true');
end if;
end;

declare
x boolean;
begin
if p1.f1(10) then
dbms_output.put_line('true');
end if;
end;

declare
x boolean;
begin
if p1.f1(10) then
dbms_output.put_line('true');
else
dbms_output.put_line('false') ;
end if ;
end ;

declare
x boolean;
begin
if p1.f1(10,200) then
dbms_output.put_line('true');
end if;
end;

-------------------------------------------------------------------------------------
create or replace package pack_over
as
procedure emp_proc (mempno number) ;
procedure emp_proc (mempno number, mdeptno number) ;
end ;
--------------------------------------------------------------

create or replace package body pack_over
as
procedure emp_proc (mempno number) as
mename emp.ename%type ;
begin
select ename into mename from emp where empno=mempno ;
dbms_output.put_line('Employee name is '||mename) ;
exception
when no_data_found then
dbms_output.put_line('No data found') ;
end emp_proc;
procedure emp_proc (mempno number,mdeptno number) as
mename emp.ename%type ;
msal emp.sal%type;
begin
select ename,sal into mename,msal from emp where empno=mempno and deptno=mdeptno ;
dbms_output.put_line('Employee name is '||mename||' salary is '||msal) ;
exception
when no_data_found then
dbms_output.put_line('No data found') ;
end emp_proc;
end;