In Oracle SQL, when defining functions (and procedures), parameters can be categorized as IN, OUT, or IN/OUT parameters. Here's a breakdown of each type:
- IN Parameters
Definition: IN parameters are used to pass values into the function or procedure. They are read-only, meaning that the function cannot modify the value of an IN parameter.
Usage: You typically use IN parameters to provide input data for the function or procedure.
Example:
CREATE OR REPLACE FUNCTION get_discount(
p_price IN NUMBER, -- IN parameter
p_discount_rate IN NUMBER -- IN parameter
) RETURN NUMBER AS
BEGIN
RETURN p_price * p_discount_rate / 100; -- Calculate discount
END get_discount;
In this example, p_price and p_discount_rate are IN parameters that provide input values for calculating the discount.
- OUT Parameters
Definition: OUT parameters are used to return values from the function or procedure to the calling environment. They do not have to be initialized before they are passed into the function or procedure.
Usage: OUT parameters are typically used when you need to return multiple values from a function or procedure.
Example:
CREATE OR REPLACE PROCEDURE get_employee_details(
p_employee_id IN NUMBER, -- IN parameter
p_employee_name OUT VARCHAR2, -- OUT parameter
p_employee_salary OUT NUMBER -- OUT parameter
) AS
BEGIN
SELECT name, salary
INTO p_employee_name, p_employee_salary
FROM employees
WHERE employee_id = p_employee_id;
END get_employee_details;
In this example, the procedure get_employee_details takes an IN parameter p_employee_id to identify which employee to retrieve, and it uses OUT parameters p_employee_name and p_employee_salary to return the employee's name and salary.
- IN/OUT Parameters
Definition: IN/OUT parameters can be used to both pass input values into a function or procedure and return values back to the calling environment. These parameters can be read from and written to.
Usage: Use IN/OUT parameters when you want to modify the input value and return it to the caller.
Example:
CREATE OR REPLACE PROCEDURE update_salary(
p_employee_id IN NUMBER, -- IN parameter
p_new_salary IN OUT NUMBER -- IN/OUT parameter
) AS
BEGIN
UPDATE employees
SET salary = p_new_salary
WHERE employee_id = p_employee_id;
p_new_salary := p_new_salary * 1.1; -- Increase salary by 10%
END update_salary;
In this example, p_new_salary is an IN/OUT parameter that allows the procedure to both accept a new salary and modify it before returning it to the caller.
Summary of Parameter Types
Using these parameter types effectively allows for more flexible and powerful SQL functions and procedures in Oracle databases.
Top comments (0)