Overloading Package:
PL/SQL permits two or more packaged subprograms to have the similar name. This option is useful when a subprogram has to accept parameters which have variant datatypes.
In the next instance, packaging of a mini calculator is display. Suppose that the operations to be performed are subtraction, addition, division and multiplication. This procedure is a great example of OVERLOADING.
CREATE OR REPLACE PACKAGE MYCALCI AS
PROCEDURE ADDNUM(X IN NUMBER,Y IN NUMBER);
PROCEDURE SUBNUM(X IN NUMBER,Y IN NUMBER);
PROCEDURE MULTNUM(X IN NUMBER,Y IN NUMBER);
PROCEDURE DIVNUM(X IN NUMBER,Y IN NUMBER);
PROCEDURE ADDNUM(X IN NUMBER,Y IN NUMBER,X1 IN NUMBER,Y1 IN NUMBER); #declare procedure#
PROCEDURE SUBNUM(X IN NUMBER,Y IN NUMBER,X1 IN NUMBER,Y1 IN NUMBER) ; #declare procedure#
PROCEDURE MULTNUM(X IN NUMBER,Y IN NUMBER,X1 IN NUMBER,Y1 IN NUMBER); #declare procedure#
PROCEDURE AVGNUM(X IN NUMBER,Y IN NUMBER,X1 IN NUMBER,Y1 IN NUMBER) ; #declare procedure#
END;
CREATE OR REPLACE PACKAGE BODY MYCALCI IS #create a package#
PROCEDURE ADDNUM(X IN NUMBER,Y IN NUMBER) IS
RES NUMBER:=0;
BEGIN RES:=X+Y;
DBMS_OUTPUT.PUT_LINE('THE TOTAL IS '||RES);
END;
PROCEDURE SUBNUM(X IN NUMBER,Y IN NUMBER) IS RES NUMBER:=0;
BEGIN RES:=X-Y;
DBMS_OUTPUT.PUT_LINE('THE DIFFERENCE IS '||RES);
END;
PROCEDURE MULTNUM(X IN NUMBER,Y IN NUMBER) IS RES NUMBER:=0;
BEGIN RES:=X*Y;
DBMS_OUTPUT.PUT_LINE('THE RESULT IS '||RES);
END;
PROCEDURE DIVNUM(X IN NUMBER,Y IN NUMBER) IS
RES NUMBER:=0;
BEGIN
RES:=X/Y;
DBMS_OUTPUT.PUT_LINE('THE RESULT IS '||RES);
END;
PROCEDURE ADDNUM(X IN NUMBER,Y IN NUMBER, X1 IN NUMBER,Y1 IN NUMBER) IS RES NUMBER:=0;--procedure definition
BEGIN RES:=X+Y+X1+Y1;
DBMS_OUTPUT.PUT_LINE('THE TOTAL IS '||RES);
END;
PROCEDURE SUBNUM(X IN NUMBER,Y IN NUMBER, X1 IN NUMBER,Y1 IN NUMBER) IS RES NUMBER:=0;--procedure definition
BEGIN
RES:=X-Y-X1-Y1;
DBMS_OUTPUT.PUT_LINE('THE DIFFERENCE IS '||RES);
END;
PROCEDURE MULTNUM(X IN NUMBER,Y IN NUMBER, X1 IN NUMBER,Y1 IN NUMBER) IS RES NUMBER:=0;--procedure definition
BEGIN RES:=X*Y*X1*Y1;
DBMS_OUTPUT.PUT_LINE('THE RESULT OF FOUR NUMBERS ARE '||RES);
END;
PROCEDURE AVGNUM(X IN NUMBER,Y IN NUMBER, X1 IN NUMBER,Y1 IN NUMBER) IS RES NUMBER:=0;--procedure definition
BEGIN RES:=(X+Y+X1+Y1)/4;
DBMS_OUTPUT.PUT_LINE('THE AVERAGE OF THE NUMBERS ARE '||RES);
END;
END;
/
The output would look like:
SQL> EXEC MYCALCI.SUBNUM(34,2); # input by user#
THE DIFFERENCE IS 32
PL/SQL procedure successfully finished.
SQL> EXEC MYCALCI.SUBNUM(100,1,2,3);# input by user#
THE DIFFERENCE IS 94
PL/SQL procedure successfully finished.
SQL> EXEC MYCALCI.AVGNUM(12,20,30,40); # input by user#
THE AVERAGE OF THE NUMBERS ARE 25.5
PL/SQL procedure successfully finished.
The first procedure accepts addnum accepts two numbers, although the second procedure accepts four numbers.. Thus, each procedure handles the data appropriately.