Procedure overloading in PL/SQL

Procedure overloading in PL/SQL refers to the ability to define multiple procedures (or functions) with the same name but with different parameter lists. Each version of the procedure is differentiated by the number of parameters, their types, or the order of their types.

This allows you to perform the same operation with different kinds of input without having to create uniquely named procedures for each case.

Rules for Procedure Overloading:

1. Different number of parameters: Procedures can have the same name if they have a different number of parameters.

2. Different parameter types: Procedures can also be overloaded if the types of their parameters differ.

3. Order of parameter types: The same name can be used if the order of parameters with different types is changed.

Example:

Here’s an example of procedure overloading in PL/SQL:

-- Procedure 1: Takes one parameter (employee_id)
CREATE OR REPLACE PROCEDURE get_employee_info (p_employee_id IN NUMBER) AS
BEGIN
DBMS_OUTPUT.PUT_LINE('Employee ID: ' || p_employee_id);
END;
/

-- Procedure 2: Takes two parameters (employee_id and employee_name)
CREATE OR REPLACE PROCEDURE get_employee_info (p_employee_id IN NUMBER, p_employee_name IN VARCHAR2) AS
BEGIN
DBMS_OUTPUT.PUT_LINE('Employee ID: ' || p_employee_id || ', Name: ' || p_employee_name);
END;
/

-- Procedure 3: Takes two parameters (employee_name and hire_date)
CREATE OR REPLACE PROCEDURE get_employee_info (p_employee_name IN VARCHAR2, p_hire_date IN DATE) AS
BEGIN
DBMS_OUTPUT.PUT_LINE('Employee Name: ' || p_employee_name || ', Hire Date: ' || p_hire_date);
END;
/

Usage:

You can call any of the overloaded procedures, and PL/SQL will automatically choose the right one based on the parameters provided.

BEGIN
-- Calls the first procedure (by passing just employee_id)
get_employee_info(1001);

-- Calls the second procedure (by passing employee_id and employee_name)
get_employee_info(1002, 'John Doe');

-- Calls the third procedure (by passing employee_name and hire_date)
get_employee_info('Jane Smith', SYSDATE);

END;
/

Output:

Employee ID: 1001
Employee ID: 1002, Name: John Doe
Employee Name: Jane Smith, Hire Date: 06-OCT-2024

Key Points:

Procedures with the same name but different parameters help improve code readability.

Overloading makes the procedure more flexible since it can handle different input types or numbers of inputs.

PL/SQL automatically determines which procedure to execute based on the arguments passed.