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.