Procedure and Function Overloading

 Overloading allows multiple procedures or functions with the same name but different parameter lists within the same package.

Procedure Overloading:

• A package can contain multiple procedures with the same name but different parameter types or numbers.

• Example:

CREATE OR REPLACE PACKAGE my_pkg AS  

    PROCEDURE add_numbers(a NUMBER, b NUMBER);  

    PROCEDURE add_numbers(a NUMBER, b NUMBER, c NUMBER);  

END my_pkg;

/

CREATE OR REPLACE PACKAGE BODY my_pkg AS  

    PROCEDURE add_numbers(a NUMBER, b NUMBER) IS  

    BEGIN  

        DBMS_OUTPUT.PUT_LINE('Sum: ' || (a + b));  

    END add_numbers;  

    PROCEDURE add_numbers(a NUMBER, b NUMBER, c NUMBER) IS  

    BEGIN  

        DBMS_OUTPUT.PUT_LINE('Sum: ' || (a + b + c));  

    END add_numbers;  

END my_pkg;

/

Calling overloaded procedures:

EXEC my_pkg.add_numbers(10, 20);

EXEC my_pkg.add_numbers(10, 20, 30);

Function Overloading:

• Similar to procedure overloading, but used for functions that return different types or take different parameter combinations.

• Example:

CREATE OR REPLACE PACKAGE my_pkg AS  

    FUNCTION get_data(id NUMBER) RETURN VARCHAR2;  

    FUNCTION get_data(name VARCHAR2) RETURN VARCHAR2;  

END my_pkg;

/

CREATE OR REPLACE PACKAGE BODY my_pkg AS  

    FUNCTION get_data(id NUMBER) RETURN VARCHAR2 IS  

    BEGIN  

        RETURN 'ID: ' || id;  

    END get_data;  

    FUNCTION get_data(name VARCHAR2) RETURN VARCHAR2 IS  

    BEGIN  

        RETURN 'Name: ' || name;  

    END get_data;  

END my_pkg;

/

Calling overloaded functions:

SELECT my_pkg.get_data(101) FROM dual;  

SELECT my_pkg.get_data('John') FROM dual;