Functions and procedures in packages can be overloaded. The following package TEST has two procedures called print_number, which behave differently depending on parameters they are called with.
create or replace package TEST is procedure print_number(p_number in integer); procedure print_number(p_number in varchar2); end TEST; / create or replace package body TEST is procedure print_number(p_number in integer) is begin dbms_output.put_line('Digit: ' || p_number); end; procedure print_number(p_number in varchar2) is begin dbms_output.put_line('String: ' || p_number); end; end TEST; /
We call both procedures. The first with integer parameter, the second with varchar2.
set serveroutput on; -- call the first procedure exec test.print_number(3); -- call the second procedure exec test.print_number('three');
The output of the above script is:
SQL> Digit: 3 PL/SQL procedure successfully completed String: three PL/SQL procedure successfully completed
# Restrictions on Overloading
Only local or packaged subprograms, or type methods, can be overloaded. Therefore, you cannot overload standalone subprograms. Also, you cannot overload two subprograms if their formal parameters differ only in name or parameter mode
# Define a Package header and body with a function.
In this example we define a package header and a package body wit a function.
After that we are calling a function from the package that return a return value.
CREATE OR REPLACE PACKAGE SkyPkg AS FUNCTION GetSkyColour(vPlanet IN VARCHAR2) RETURN VARCHAR2; END; /
CREATE OR REPLACE PACKAGE BODY SkyPkg AS FUNCTION GetSkyColour(vPlanet IN VARCHAR2) RETURN VARCHAR2 AS vColour VARCHAR2(100) := NULL; BEGIN IF vPlanet = 'Earth' THEN vColour := 'Blue'; ELSIF vPlanet = 'Mars' THEN vColour := 'Red'; END IF; RETURN vColour; END; END; /
Calling the function from the package body:
DECLARE vColour VARCHAR2(100); BEGIN vColour := SkyPkg.GetSkyColour(vPlanet => 'Earth'); DBMS_OUTPUT.PUT_LINE(vColour); END; /
# Package Usage
Packages in PLSQL are a collection of procedures, functions, variables, exceptions, constants, and data structures. Generally the resources in a package are related to each other and accomplish similar tasks.
Why Use Packages
- Better Performance/ Funtionality
Parts of a Package
Specification - Sometimes called a package header. Contains variable and type declarations and the signatures of the functions and procedures that are in the package which are public to be called from outside the package.
Package Body - Contains the code and private declarations.
The package specification must be compiled before the package body, otherwise the package body compilation will report an error.