PL/SQL IN OUT parameters – Oracle PL/SQL Tutorial

In Oracle PL/SQL, stored procedures are a powerful feature that allows you to encapsulate a series of SQL and procedural statements into a named block, which can then be executed as a single unit. Parameters are essential components of stored procedures, allowing you to pass values into and out of the procedure. In this context, an IN OUT parameter is a type of parameter that can be used both for input and output.

Here’s an explanation of IN OUT parameters in Oracle PL/SQL:

Input Parameters (IN):

When you declare a parameter as IN, it means that the procedure can receive a value from the calling program, but the procedure cannot modify the value of the parameter. It’s used for passing values into the procedure.

CREATE OR REPLACE PROCEDURE my_procedure (p_input_param IN NUMBER) IS
BEGIN
  -- Procedure logic using the input parameter
END;

Output Parameters (OUT):

When you declare a parameter as OUT, it means that the procedure can modify the value of the parameter, and this modified value will be accessible to the calling program after the procedure execution. It’s used for passing values out of the procedure.

CREATE OR REPLACE PROCEDURE my_procedure (p_output_param OUT NUMBER) IS
BEGIN
  -- Procedure logic modifying the output parameter
END;

IN OUT Parameters:

An IN OUT parameter combines both input and output functionalities. It allows the procedure to receive a value from the calling program, modify the value within the procedure, and then return the modified value to the calling program.

CREATE OR REPLACE PROCEDURE my_procedure (p_in_out_param IN OUT NUMBER) IS
BEGIN
  -- Procedure logic using and modifying the in/out parameter
END;

When calling a procedure with an IN OUT parameter, you need to provide a variable that holds a value for input, and after the procedure execution, this same variable will hold the modified value:

DECLARE
  my_variable NUMBER := 10;
BEGIN
  my_procedure(my_variable);
  -- Now, my_variable holds the modified value after the procedure execution
END;

Using IN OUT parameters can be beneficial when you need to pass values into a procedure, modify those values within the procedure, and then return the modified values to the calling program. It provides a way to pass data bidirectionally between the calling program and the stored procedure.

Learn PL/SQL