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.
Top comments (0)