Wednesday, April 17, 2013

Overloading Oracle Functions and Stored Procedures || polymorphism in PL/SQL


polymorphism:
=========
           Polymorphism is a feature of object-oriented programming, is the ability to create a variable, a function, or an object that has more than one form.

        In Oracle procedural programming also supports polymorphishm in the form of program unit overloading inside a package, member function type etc.

Function overloading:
==============
        Two or more functions can share the same name as long as their parameter declarations are different.In this situation, the functions that share the same name are said to be overloaded, and the process is referred to as function overloading.

Example-1:  Consider the below package

SQL>Create or replace package techsnibpack5
Is
Function add
(a number,b number)
Return number;
Function add
(a number,b number,c number)
Return number;
Function add
(a number,b number,c number,d number)
Return number;
End;
/
Package Created

SQL>Create or replace package body techsnibpack5
Is
Function add
(a number,b number)
Return number
Is
c number;
Begin
c:=a+b;
Return c;
End add;

Function add
(a number,b number,c number)
Return number
Is
d number;
Begin
d:=a+b+c;
Return d;
End add;

Function add
(a number,b number,c number,d number)
Return number
Is
e number;
Begin
e:=a+b+c+d;
Return e;
End add;
End;
/
Package body created

Execution Process :
============

SQL> var x number
SQL> exec :x:=techsnibpack5.add(10,20);
PL/SQL Successfully completed

SQL> print x

X
---------------------------------------------
30

SQL> exec :x:=techsnibpack5.add(10,20,30);
PL/SQL Successfully completed

SQL> print x

X
---------------------------------------------
60

SQL> exec :x:=techsnibpack5.add(10,20,30,40);
PL/SQL Successfully completed

SQL> print x

X
---------------------------------------------
100


we consider the following two tables i.e emp, dept to write SQL queries in the coming PL/SQL programs.

Double click on the image to view the full image with clarity

Example-2:write a package to find the total salary of an employee, one employee number passed as in parameter, find the annual salary of an employee then ename passed as in parameter and count the number of employees when depart number passed as in parameter.

SQL>Create or replace package techsnibpack6
Is
Function result
(f_empno in emp.empno%type)
Return number;
Function result
(f_ename in emp.ename%type)
Return number;
Function result
(f_deptno in emp.deptno%type)
Return number;
End;
/
Package Created

SQL>Create or replace package body techsnibpack6
Is
Function result
(f_empno in emp.empno%type)
Return number
Is
c number;
Begin
Select sal+nvl(comm,0) into c from emp where empno=f_empno;
Return c;
End result;

Function result
(f_ename in emp.ename%type)
Return number
Is
c number;
Begin
Select sal*12 into c from emp where ename=f_ename;
Return c;
End result;

Function result
(f_deptno in emp.deptno%type)
Return number
Is
c number;
Begin
Select count(*) into c from emp where deptno=f_deptno;
Return c;
End result;
End;
/
Package body created


Execution Process :
============

SQL> var x number
SQL> exec :x:=techsnibpack6.result(7789);
We get some error because 1st and 3rd function parameters are of same datatype.
SQL> exec :x:=techsnibpack6.result(‘KING’);
PL/SQL Successfully completed

SQL> print x

X
---------------------------------------------
307600

SQL> exec :x:=techsnibpack6.result(f_empno=>7788);
PL/SQL Successfully completed

SQL> print x

X
---------------------------------------------
3000

SQL> exec :x:=techsnibpack6.result(f_deptno=>30);
PL/SQL Successfully completed

SQL> print x

X
---------------------------------------------
4


Procedure Overloading:
===============
        The same procedure name is repeated with parameters of different datatypes and parameters in different positions, varying number of parameters is called overloading of procedures.You can use the same name for several different subprograms as long as their formal parameters differ in number, order, or datatype family.

For an example of an overloaded procedure in a package, see the below example

Example-1:write a package to bring the employee details one employee number passed as an in parameter and bring the department details when department number passed as in parameter.

SQL>Create or replace package techsnibpack7
Is
Procedure p1
(p_empno emp.empno%type,
p_ename out emp.ename%type,
p_sal out emp.sal%type,
p_deptno out emp.deptno%type);
Procedure p1
(p_deptno dept.deptno%type,
p_dname out dept.dname%type,
p_loc out dept.loc%type);
End;
/
Package Created

SQL>Create or replace package body techsnibpack7
Is
Procedure p1
(p_empno emp.empno%type,
p_ename out emp.ename%type,
p_sal out emp.sal%type,
p_deptno out emp.deptno%type)
Is
Begin
Select ename,sal,deptno into p_ename,p_sal,p_deptno from emp where empno=p_empno;
End p1;
Procedure p1
(p_deptno dept.deptno%type,
p_dname out dept.dname%type,
p_loc out dept.loc%type)
Is
Begin
Select dname,loc into p_dname,p_loc from dept where deptno=p_deptno;
End p1;
End;
/
Package body created



Example-2:write a package to display employee details and department number passed as an in parameter and display department details when department name passed as in parameter.

SQL>Create or replace package techsnibpack8
Is
Procedure p1
(p_deptno in emp.deptno%type);
Procedure p1
(P_dname in dept.dname%type);
End;
/
Package Created

SQL>Create or replace package body techsnibpack8
Is
Procedure p1
(p_deptno in emp.deptno%type)
Is
Cursor c is select * from emp where deptno=p_deptno;
a emp%rowtype;
Begin
Open c;
Loop
Fetch c into a;
if c%found then
dbms_output.put_line(a.empno);
dbms_output.put_line(a.ename);
dbms_output.put_line(a.sal);
else
exit;
End if;
End loop;
Close c;
End p1;
Procedure p1
(P_dname out dept.dname%type)
Is
b dept%rowtype;
Begin
Select * into b from dept where dname=p_dname;
dbms_output.put_line(b.deptno);
dbms_output.put_line(b.dname);
dbms_output.put_line(b.loc);
End p1;
End;
/
Package body created

Execution Process :
============

SQL> exec techsnibpack8.p1(10);

7782 CLARK 2450 7839 KING 5000 7934 MILLER 2000

PL/SQL Procedure successfully completed


SQL> exec techsnibpack8.p1(‘SALES’);

30 SALES CHICAGO

PL/SQL Procedure successfully completed




--> Syntax to see the list of all sub programs

SQL> select * from user_source;

--> Syntax to see the source code of a particular program

SQL> select text from user_source where name=’<sub program name>’;

--> Syntax to drop the package

SQL> drop package <package_name>;



1 comment:

  1. Great Explanation and more useful for anyone.Thanks for sharing...
    oracle course in chennai

    ReplyDelete

back to top