Examples for PL/SQL Package Body

Example 1: Let Create a Package for Calculating Rectangle Area and Perimeter

Step 1: Package Specification for Rectangle Area and Perimeter Calculation

Query:

CREATE OR REPLACE PACKAGE RectanglePackage AS
    PROCEDURE CalculateAreaAndPerimeter (length IN NUMBER, width IN NUMBER, area OUT NUMBER, perimeter OUT NUMBER);
END RectanglePackage;

Output:

Step 2: Package Body for Rectangle Area and Perimeter Calculation.

Query:

CREATE OR REPLACE PACKAGE BODY RectanglePackage AS
    PROCEDURE CalculateAreaAndPerimeter (length IN NUMBER, width IN NUMBER, area OUT NUMBER, perimeter OUT NUMBER) IS
    BEGIN
        area := length * width;
        perimeter := 2 * (length + width);
    END CalculateAreaAndPerimeter;
END RectanglePackage;
/

Output:

Explanation:

1. The “RectanglePackage“ is the name of the package. It declared the procedure “CalculateAreaAndPerimeter” with the parameters such as length, width, area and perimeter. Here, length and width are IN parameters which is used to gives the inputs, area and perimeter are the OUT parameters which is used to the return the calculate the results.
2. The body of the package implements “CalculateAreaAndPerimeter” procedure. It is used to calculate the area and parameter depends upon the length and width inputs.

Step 3: Now Calculate Rectangle Area and Perimeter Using the Package.

Query:

DECLARE
    l_length NUMBER := 5;
    l_width NUMBER := 3;
    l_area NUMBER;
    l_perimeter NUMBER;
BEGIN
    RectanglePackage.CalculateAreaAndPerimeter(l_length, l_width, l_area, l_perimeter);
    DBMS_OUTPUT.PUT_LINE('The area of the Rectangle is ' || l_area);
    DBMS_OUTPUT.PUT_LINE('The Perimeter of the Rectangle is ' || l_perimeter);
END;
/

Explanation:

• In “Declare” section, declare the two variables such as “l_length” and “l_width” and initialize with the values 5 and 3 respectively. And remaining two variables such as “l_area” and “l_perimeter” are declared without initialization.
• In Procedure call, The “RectanglePackage.CalculateAreaAndPerimeter” procedure is called along with the input parameters such as “l_length” and “l_width” and output parameters such as “l_area” and “l_perimeter”.
• After completion of the procedure call, the values of area and perimeter of the rectangle is printed with the help of the “DBMS_OUTPUT.PUT_LINE“.

Output:

Explanation:

• The formula of area of the rectangle is “length * breadth”. Here, the value of the length is 5 and the value of the breadth is 3. Now, we can calculate the area of the rectangle i.e. 5 * 3 = 15.
• The formula of perimeter of the rectangle is “2 * ( length + breadth )“. Here, the value of the length is 5 and the value of the breadth is 3. Now, we can calculate the perimeter of the rectangle i.e. 2*( 5 + 3 ) = 16.
• The values are printed with the help of the “DBMS_OUTPUT.PUT_LINE“.

Example 2: Temperature Conversion Package in PL/SQL

Step 1: Package Specification for Temperature Conversion.

Query:

CREATE OR REPLACE PACKAGE TemperatureConversion AS
    FUNCTION CelsiusToFahrenheit (celsius IN NUMBER) RETURN NUMBER;
    FUNCTION FahrenheitToCelsius(fahrenheit IN NUMBER) RETURN NUMBER;
END TemperatureConversion;
/

Output:

Step 2: Package Body for Temperature Conversion Functions.

Query:

CREATE OR REPLACE PACKAGE BODY TemperatureConversion AS
    FUNCTION CelsiusToFahrenheit (celsius IN NUMBER) RETURN NUMBER IS
    BEGIN
        RETURN (celsius * 9/5) + 32;
    END CelsiusToFahrenheit;

    FUNCTION FahrenheitToCelsius(fahrenheit IN NUMBER) RETURN NUMBER IS
    BEGIN
        RETURN (fahrenheit - 32) * 5/9;
    END FahrenheitToCelsius;
END TemperatureConversion;
/

Output:

Explanation:

1. The “TemperatureConversion” contained the two functions such as “CelsiusToFahrenheit” and “FahrenheitToCelsius”.
2. The body of the package implement the function to perform the conversions of the temperature.

Step 3: Use the package in the PL/SQL code for converting the temperature.

Query:

DECLARE
    celsius_temp NUMBER := 20;
    fahrenheit_temp NUMBER;
    converted_celsius_temp NUMBER;
BEGIN
    fahrenheit_temp := TemperatureConversion.CelsiusToFahrenheit(celsius_temp);
    DBMS_OUTPUT.PUT_LINE('Temperature in Fahrenheit: ' || fahrenheit_temp);

    converted_celsius_temp := TemperatureConversion.FahrenheitToCelsius(fahrenheit_temp);
    DBMS_OUTPUT.PUT_LINE('Converted back to Celsius: ' || converted_celsius_temp);
END;
/

Output:

Explanation: In the above PL/SQL code is converted the 20 degrees Celsius temperature to Fahrenheit and then it will convert to the Celsius. The output convert the 20 degree Celsius temperature to 68 degree Fahrenheit temperature and back to the 20 degree Celsius. If we execute the above code, it will convert the temperature of 20 degrees Celsius to Fahrenheit and then it convert back to the Celsius and print the both values.

A PL/SQL package body serves as an important component in developing modular and maintainable code within the Oracle database environment. It complements the package specification by providing the implementation details for procedures, functions, and other constructs declared in the package.

The package body organizes and modularizes code, hides data, and manages dependencies effectively. In this article, We will explore What is a Package Body, How to create a Package body, How to compile a Package Body along with real-life examples, and so on.