Formal and Actual parameters in Stored Procedure in PLSQL

In Oracle SQL, you can create stored procedures that accept parameters to perform specific tasks. Below, I’ll illustrate the concepts of formal and actual parameters using a simple example of calculating the area of a rectangle.

Example: Calculating the Area of a Rectangle

1. Defining the Procedure

In this example, we will create a stored procedure named CalculateArea that takes the length and width of a rectangle as input parameters and returns the area.

CREATE OR REPLACE PROCEDURE CalculateArea (
    p_length IN NUMBER,  -- Formal parameter for length (input)
    p_width IN NUMBER,   -- Formal parameter for width (input)
    p_area OUT NUMBER    -- Formal parameter for area (output)
) AS
BEGIN
    p_area := p_length * p_width;  -- Calculate the area
END;

Formal Parameters:

p_length: Input formal parameter for the length of the rectangle.
p_width: Input formal parameter for the width of the rectangle.
p_area: Output formal parameter that will hold the calculated area.

2. Calling the Procedure

To call the CalculateArea procedure, you need to declare a variable that will hold the output value. Then you can execute the procedure with actual values.

DECLARE
    v_area NUMBER;  -- Variable to hold the output area
BEGIN
    CalculateArea(5, 10, v_area);  -- Call the procedure with actual parameters
    DBMS_OUTPUT.PUT_LINE('The area of the rectangle is: ' || v_area);  -- Print the area
END;

Actual Parameters:

5: The actual value for p_length (length of the rectangle).
10: The actual value for p_width (width of the rectangle).
v_area: The variable that receives the output value for p_area.

Summary

Formal Parameters: Defined in the procedure:
p_length, p_width, p_area.

Actual Parameters: Values passed during the procedure call:
5, 10, and the variable v_area.

Result

• When you run the block of code that calls the procedure, it will output:
• The area of the rectangle is: 50
• This demonstrates how to define and use formal and actual parameters in Oracle SQL procedures effectively.