Stored procedures are pre-compiled modules containing SQL statements and logic stored on the database server.
They can be invoked multiple times with different inputs, improving performance and code reuse.
Parameters play a crucial role in passing data to and from stored procedures.
Types of Parameters
- IN parameter: Used to pass data from the calling program to the stored procedure. The value is assigned to the parameter before the procedure is executed.
- OUT parameter: Used to return data from the stored procedure to the calling program. The procedure assigns a value to the parameter before it finishes executing.
- IN OUT parameter: Combines the functionality of both IN and OUT parameters. It allows passing data to the procedure and receiving data back after execution.
Usage of Parameters
Parameters are declared within the stored procedure definition using the syntax:
CREATE OR REPLACE PROCEDURE procedure_name (
parameter_name1 IN parameter_type1,
parameter_name2 OUT parameter_type2,
...
)
...- parameter_type specifies the data type of the parameter.
- IN, OUT, or IN OUT keywords indicate the parameter mode.
In the procedure body, parameters can be accessed using the parameter_name within SQL statements and logic.
Benefits of using parameters
- Increased security: Prevents SQL injection attacks by separating data and code.
- Improved performance: Avoids recompiling the entire procedure for different input values.
- Enhanced code clarity: Makes the procedure logic easier to understand and maintain.
- Flexibility: Allows passing different data sets to the same procedure.
Examples of using parameters in stored procedures
Calculating a sum
CREATE OR REPLACE PROCEDURE calculate_sum (
number1 IN NUMBER,
number2 IN NUMBER,
result OUT NUMBER
)
BEGIN
result := number1 + number2;
END;