Overloading
The PL/SQL overloads the subprogram names. That is, you can use similar name for few different subprograms as long as their formal parameters differ in the number, order, or datatype family.
Assume that you want to initialize the first n rows in two index-by tables that were declared as shown below:
DECLARE
TYPE DateTabTyp IS TABLE OF DATE INDEX BY BINARY_INTEGER;
TYPE RealTabTyp IS TABLE OF REAL INDEX BY BINARY_INTEGER;
hiredate_tab DateTabTyp;
sal_tab RealTabTyp;
You may write the procedure below to initialize the index-by table named hiredate_tab:
PROCEDURE initialize (tab OUT DateTabTyp, n INTEGER) IS
BEGIN
FOR i IN 1..n LOOP
tab(i) := SYSDATE;
END LOOP;
END initialize;
And, you may write the next procedure to initialize the index-by table named sal_tab as:
PROCEDURE initialize (tab OUT RealTabTyp, n INTEGER) IS
BEGIN
FOR i IN 1..n LOOP
tab(i) := 0.0;
END LOOP;
END initialize;
As the processing in these two procedures are similar, it is logical to give them the similar name.
You can position the two overloaded initialize procedures in similar subprogram, block, or package. The PL/SQL determines that which of the two procedures is being called by checking their formal parameters.
Consider the illustration below. If you call initialize with a DateTabTyp parameter, the PL/SQL uses the first version of initialize. But, if you call the initialize with a RealTabTyp parameter, then the PL/SQL uses the second version.
DECLARE
TYPE DateTabTyp IS TABLE OF DATE INDEX BY BINARY_INTEGER;
TYPE RealTabTyp IS TABLE OF REAL INDEX BY BINARY_INTEGER;
hiredate_tab DateTabTyp;
comm_tab RealTabTyp;
indx BINARY_INTEGER;
BEGIN
indx := 50;
initialize(hiredate_tab, indx); -- calls first version
initialize(comm_tab, indx); -- calls second version