IN OUT and INOUT Parameters in PL/SQL

PL/SQL is Oracle’s procedural extension to SQL. It makes interactive database management possible, allowing creation of procedures, functions, and packages that can drive powerf

ul operations on a database. The functionality of PL/SQL is enhanced by the use of parameter passing in procedures and functions. To program effectively in PL/SQL requires an understanding of how the parameters-in, out, and in-out-work. In the following article , we shall discuss these parameter modes in detail, including syntax and usage with examples to make the understanding more practical.

Introduction to PL/SQL Parameter Modes

In PL/SQL, parameters are used to pass values to and from procedures and functions. There are three primary parameter modes:

• IN: These parameters are used to pass values into a procedure or function. They are read-only within the procedure.
• OUT: These parameters allow values to be returned from a procedure or function to the caller. They are write-only.
• IN OUT: These parameters can accept values and return them after modification. They are both read and write.

Using these parameter modes effectively can enhance the flexibility and reusability of your PL/SQL code.

Understanding IN parameters

IN parameters are used most often, of all kinds of PL/SQL parameters. Declaring a parameter as IN means that when defining it, you pass to the parameter a value that may only be read within the procedure or function. You cannot change the value of an IN parameter within the subprogram.

Syntax

CREATE OR REPLACE PROCEDURE procedure_name (param_name IN data_type)
IS
BEGIN
  -- Procedure logic
END;

Example

Let’s consider a simple example where we create a procedure that calculates the area of a rectangle based on the length and width provided as IN parameters.

CREATE OR REPLACE PROCEDURE calculate_area (length IN NUMBER, width IN NUMBER) 
IS
  area NUMBER;
BEGIN
  area := length * width;
  DBMS_OUTPUT.PUT_LINE('The area of the rectangle is: ' || area);
END;

Table: Characteristics of IN Parameters

Characteristic	Description
Value Passed	Read-only inside the procedure.
Modification	Cannot be modified within the procedure.
Default Mode	If no mode is specified, parameters are considered IN by default.

Calling the Procedure

To call this procedure, you can execute the following block:

BEGIN
  calculate_area(5, 10);  -- Output: The area of the rectangle is: 50
END;

Explore OUT Parameters

OUT parameters allow you to send values out of a procedure or function for use in the calling environment. You need not assign an initial value to an OUT parameter when you call a procedure. They are write-only; that is, you cannot read the value of an OUT parameter before the procedure sets it.

Syntax

CREATE OR REPLACE PROCEDURE procedure_name (param_name OUT data_type)
IS
BEGIN
  -- Procedure logic
END;

Example

Let’s create a procedure that returns the name and salary of an employee based on their employee ID using OUT parameters.

CREATE OR REPLACE PROCEDURE get_employee_details (p_emp_id IN NUMBER, p_name OUT VARCHAR2, p_salary OUT NUMBER)
IS
BEGIN
  SELECT name, salary INTO p_name, p_salary
  FROM employees
  WHERE emp_id = p_emp_id;
END;

Table: Characteristics of OUT Parameters

Characteristic	Description
Value Passed	Write-only; initial value is not required.
Modification	Can be modified and returned to the caller.
Reading Value	Cannot read the value before it is assigned within the procedure.

Calling the Procedure

To call this procedure and retrieve the employee details, you would use the following block:

DECLARE
  emp_name VARCHAR2(100);
  emp_salary NUMBER;
BEGIN
  get_employee_details(101, emp_name, emp_salary);
  DBMS_OUTPUT.PUT_LINE('Employee Name: ' || emp_name);
  DBMS_OUTPUT.PUT_LINE('Employee Salary: ' || emp_salary);
END;

How to Use IN OUT Parameters in PL/SQL

IN OUT parameters are versatile in that they can be assigned a value when entering the procedure and return an altered value when exiting the procedure. Thus, they become something that can be read from and written to, allowing for dynamic data manipulation in the development.

Syntax

CREATE OR REPLACE PROCEDURE procedure_name (param_name IN OUT data_type)
IS
BEGIN
  -- Procedure logic
END;

Example

Consider a procedure that adjusts an employee’s salary based on a given percentage increase. The employee ID and the new salary are passed as parameters.

CREATE OR REPLACE PROCEDURE adjust_employee_salary (p_emp_id IN NUMBER, p_increment IN OUT NUMBER)
IS
BEGIN
  -- Retrieve current salary
  SELECT salary INTO p_increment
  FROM employees
  WHERE emp_id = p_emp_id;

  -- Increase salary by the increment percentage
  p_increment := p_increment * (1 + (p_increment / 100));

  UPDATE employees
  SET salary = p_increment
  WHERE emp_id = p_emp_id;

  DBMS_OUTPUT.PUT_LINE('Salary updated for employee ID: ' || p_emp_id);
END;

Table: Characteristics of IN OUT Parameters

Characteristic	Description
Value Passed	Can be read and modified inside the procedure.
Initialization	Requires an initial value when calling the procedure.
Output Value	Returns a modified value to the caller.

Calling the Procedure

To call the adjust_employee_salary procedure, you can use the following block:

DECLARE
  current_salary NUMBER := 10;  -- Initial percentage increment
BEGIN
  adjust_employee_salary(101, current_salary);
  DBMS_OUTPUT.PUT_LINE('New Salary after adjustment: ' || current_salary);
END;

Example: Using Parameters in PL/SQL Procedures

To sum up our understanding of IN, OUT, and IN OUT parameters, let’s build an integrated example using all three types in one procedure in PL/SQL.

Let’s assume that we are designing a system to automatically determine bonuses for employees. We’ll develop a procedure that calculates the bonus amount using the current salary of the employee and returns the new salary and the bonus amount by making use of the OUT parameters.

Procedure Definition

CREATE OR REPLACE PROCEDURE calculate_bonus (
  p_emp_id IN NUMBER, 
  p_percentage IN OUT NUMBER, 
  p_new_salary OUT NUMBER, 
  p_bonus OUT NUMBER
)
IS
  v_current_salary NUMBER;
BEGIN
  -- Retrieve current salary
  SELECT salary INTO v_current_salary
  FROM employees
  WHERE emp_id = p_emp_id;

  -- Calculate bonus
  p_bonus := v_current_salary * (p_percentage / 100);
  
  -- Update new salary
  p_new_salary := v_current_salary + p_bonus;
  
  -- Update the employee's salary in the database
  UPDATE employees
  SET salary = p_new_salary
  WHERE emp_id = p_emp_id;

  DBMS_OUTPUT.PUT_LINE('Bonus calculated for employee ID: ' || p_emp_id);
END;

Explanation of the Procedure

• IN Parameter: p_emp_id is used to specify the employee for whom the bonus is being calculated.
• IN OUT Parameter: p_percentage allows the caller to pass a percentage and receive the updated value.
• OUT Parameters: p_new_salary and p_bonus return the new salary and calculated bonus to the caller.

Calling the Procedure

To call this procedure, use the following PL/SQL block:

DECLARE
  emp_bonus NUMBER;
  emp_new_salary NUMBER;
  increment_percentage NUMBER := 10;  -- Initial percentage
BEGIN
  calculate_bonus(101, increment_percentage, emp_new_salary, emp_bonus);
  DBMS_OUTPUT.PUT_LINE('New Salary: ' || emp_new_salary);
  DBMS_OUTPUT.PUT_LINE('Bonus Amount: ' || emp_bonus);
END;

Output

When you run this block, you will receive the following output if the employee ID 101 exists in the database:

Bonus calculated for employee ID: 101
New Salary: 55000
Bonus Amount: 5000

Common Parameter Mode Use Cases

Knowing how to use parameters like IN, OUT, and IN OUT may help out in a number of scenarios, such as:

• Data retrieval: Use IN parameters in SELECT statements for record filtering; use OUT parameters for returning results.
• Data Manipulation: Use IN OUT parameters in those calculations requiring the changes both inside and outside the procedure.
• Complex Business Logic: Design modular and reusable code blocks that abstract complex logic using procedures appropriately parameterised.

Advantages of IN OUT and INOUT Parameters in PL/SQL

The usage of IN OUT and IN OUT parameters in PL/SQL procedures and functions results in numerous advantages which enhance the flexibility, efficiency, and readability of code. The major advantages of using such kinds of parameters are as follows:

1. Bidirectional Data Transfer

IN OUT and IN OUT parameters allow a two-way transfer of data from the calling environment into the PL/SQL block, so that the procedure or function does not only take input, like normal IN parameters, but also returns the modified data back to the caller for more dynamic and interactive processing.

2. Optimal Memory Utilisation

Because IN OUT parameters allow the changes in variables that already exist, they can be used to reduce memory usage. Instead of making copies of data that need to be processed, developers can pass the same variable into a procedure or function, thus minimising overhead due to memory allocation.

3. Enhancing Readability of Code

With IN OUT and IN OUT parameters, it clearly indicates what parameters are intended for input, output, or both, which would enhance code readability and maintainability. The explicit declaration would improve the developer’s understanding of the data flowing into a PL/SQL block because they can track the logic and intent of the code easily.

4. Promotes Complex Logic

IN OUT parameters are particularly useful in involving complicated business logic that may need intermediate results or statuses to be held. It enables procedures and functions to return several updates or results through the same parameter, easing the design and implementation of such intricate operations.

5. Simpler Error Handling

Error handling can be simplified with the help of IN OUT parameters, since procedures or functions might return status messages or error codes directly back through the same variable that was passed to them. That helps make communication of results or errors back to the calling environment more straightforward.

6. Fewer Calls to Procedures

IN OUT parameters can minimise the number of procedure calls which may be needed. While often, a program will require a few procedure calls to yield the desired results or modify data, one procedure call can do several things at once: improving performance and saving overhead caused by context switching.

7. Performance

Since IN OUT parameters eliminate the need for copying data back and forth, they result in better performance in scenarios involving large data structures. This is particularly useful in processing large data sets or iterative operations.

8. Design Flexibility

IN OUT and IN OUT parameters result in flexibility at both the procedure and function design levels. Developers are, therefore, challenged to write more generalist routines that are capable of handling diverse input and output situations. This would translate to more code re-usable components.

9. Logic Encapsulation

With IN OUT parameters, the developers encapsulate logic and changes in data within one procedure or function; this simplifies working with related operations together inside one module rather than in many dependencies among different code modules.

10. HANDLING DATA VALIDATION

For validating data, IN OUT parameters can be applied. Procedures can accept input through these parameters, validate the input, and send corrected or default values so as to ensure only valid data is processed later on in the application.

Disadvantages of IN OUT and INOUT Parameters in PL/SQL

IN OUT and IN OUT parameters of PL/SQL hold many benefits but not without some disadvantages that must be weighed by developers in the process of programming. Some of the major negative characteristics pertaining to the use of IN OUT and IN OUT parameters can be found here.

1. Complexity

Use of IN OUT parameters will make the procedure and function complex to some extent. They permit the data in both directions, and because of this cause, it becomes very difficult to identify what is its state and what changes have passed through the parameters as the PL/SQL block executes, which makes things confusing and introduces bugs sometimes.

2. Possibilities for Unintended Changes

Because IN OUT parameters permit both input and output, there is a risk that the procedure or function might inadvertently change the original variable in the calling environment. This could give unpredictable behaviour, especially if the calling code depends on the assumption that the variable has been left unchanged.

3. Readability Problem

The existence of IN OUT parameters makes the readability of the code poor. Developers will have to be paying close attention to how these parameters are used and modified throughout the code, which makes the understanding of the logic harder for them, especially those who are new to the co debase.

4. Debugging Challenges

The procedural development procedures and functions utilising the IN OUT parameters might require additional debugging. In case of unexpected output, it’s hard to tell whether it is caused by a problem in how the parameter is being modified inside the PL/SQL block or because of the problem in how it was initially passed from the calling environment.

5. State Management Overhead

State of IN OUT parameters may introduce extra overhead in managing the state. Its proper initialisation and maintenance become the developer’s responsibility, making the coding process more cumbersome and error-prone.

6. Loss of Encapsulation

Since IN OUT parameters reduce the encapsulation of a procedure or function, it fails to clearly define the inputs or outputs since it may mix both so the interfaces may become fuzzy for other developers to understand how to correctly use the procedure.

7. Limitation in Nested Procedures

With nested procedures, IN OUT parameters behave in a convoluted manner. Side effects might be generated through alteration of an IN OUT parameter by a nested procedure, affecting data flow and logic that doesn’t necessarily appear intuitive to the caller.

8. Ability for Performance Problems

There may be performance impacts when using IN OUT parameters inappropriately or over liberally. Large data structures contained within the parameters can mean that changes could sometimes cause unnecessary copying and increased processing overheads, which could reduce the performance benefit.

9. Dependency on Caller Context

IN OUT parameters can be sensitive to the context of the caller, which makes it less easy to reuse the procedures and functions. Developers have to be careful about how these parameters interact with the calling code, which may add up to complexity while reusing codes.

10. Limited Flexibility for Future Changes

Such a design of procedures and functions with heavy use of IN OUT parameters may severely constrain flexibility for eventual changes. If the requirements of the IN OUT parameter change, changing its behaviour may result in quite severe refactoring of both the procedure and its callers.