10.7 How do I…Overload procedures and functions in packages?Problem
I want to create procedures and functions with different parameter lists, depending on their use. I know that within a package I can declare the same procedure or function many times, as long as the parameter lists are different with each declaration. How do I overload procedures and functions in packages?
Technique
PL/SQL enables more than one procedure or function in a package to have the same name but with different parameter lists. This feature is useful in situations in which you want the same subprogram to accept different parameters. Each combination of subprogram name and parameter list must be unique within the package. The datatypes of the parameters make them unique if the subprogram names are the same. Variable names have no impact on the uniqueness of the parameter lists. Subprograms can be overloaded within a package or even in the declarative section of a PL/SQL block.
Steps
1. Run SQL*Plus and connect as the WAITE user account. Run CHP10_25.SQL, which contains a package specification that declares overloaded procedures. The code and results are shown in Figure 10.25.
Line 1 presents the keywords required to create a package specification. Lines 2 through 4 define the same procedure with three different parameter lists. When the package body is created, each of the procedure declarations listed in the package specification must be created. The datatypes of the parameters in the overloaded procedures must be unique, so that the PL/SQL runtime engine can match it with the procedure headers to determine which procedure definition needs to be executed. After the package specification is created, each of the procedures must be created in the package body, or an error will occur.
2. Run CHP10_26.SQL in SQL*Plus. The code created in the file creates the package body for the package developed in the previous step. The code and results are shown in Figure 10.26.
Line 1 provides the keywords necessary to create or replace the package body. Lines 2 though 5 create the first specification of the procedure. Lines 6 through 9 create the second specification of the overloaded procedure, and lines 10 through 13 create the third specification. Using the DBMS_OUTPUT package, each of the three overloaded procedures displays a message indicating which specification was executed. When the package is created, the user can call the package procedures with any of the parameter lists specified. The instance of the procedure used depends on the parameter list provided.
3. Run CHP10_27.SQL in SQL*Plus. The PL/SQL module in the file calls the overloaded procedure with the three different parameter lists. Figure 10.27 shows the results of the three procedure calls in SQL*Plus.
Line 2 calls the overloaded procedure passing a VARCHAR2 parameter, executing the third version of the procedure from the package. Line 3 calls the procedure passing a NUMBER parameter to the function, executing the first version. Line 4 passes a VARCHAR2 and a NUMBER parameter to the procedure, executing the second version of the procedure.
When the procedure is called with dissimilar parameter lists, a different procedure body is executed each time, depending on the parameters passed. The DBMS_OUTPUT.PUT_LINE statement in each procedure version identifies the one used.
How It Works
Step 1 presents a package specification declaring three procedures with the same name but different parameter lists. Step 2 presents the package body containing the overloaded procedures. Each procedure defined in the package specification must be created in the package body, or an error will occur. The DBMS_OUTPUT.PUT_LINE procedure displays a message from each procedure to identify the specification executed. Step 3 calls the procedures with different parameters, executing different instances.
Comments
Overloading procedures and functions can simplify your applications by expanding the capabilities of a module. Most built-in PL/SQL functions are good examples of overloading. The built-in function TO_CHAR is overloaded, for example, because it can accept a variety of parameters with different datatypes performing the same function.