Argument Modes in PL/SQL

Argument modes are basically used to describe the behavior of the formal parameters. There are three types of argument modes which are used in the sub-program, which are as follows -

1. IN Mode
2. OUT Mode
3. IN OUT Mode

Arguments are the values that are passed to the PL/SQL blocks, subprograms, or functions. Arguments allow you to manipulate data in a dynamic manner. Here are some examples of how to use arguments in the PL/SQL language:

IN Mode

It is the default argument mode in subprogram. This mode passes a constant value from the calling environment into the subprogram.

Example: The following example illustrates the working of IN Mode argument -

Query

SQL> CREATE OR REPLACE PROCEDURE PR1(X IN NUMBER, Y IN NUMBER)
S NUMBER;
BEGIN
S:=X+Y;
DBMS_OUTPUT.PUT_LINE('SUM IS : '||S);
END PR1;

Output

Procedure created.

Query

SQL> DECLARE
N1 NUMBER:=10;
N2 NUMBER:=20;
BEGIN
PR1(N1, N2);
END;

Output

SUM IS : 30
PL/SQL procedure successfully completed.
SQL>

OUT Mode

This mode passes a value from the subprogram to the calling environment and this mode is also used for sending the value to the end user and generally, it is used for writing purposes.

Example: The following example illustrates the working of OUT Mode argument -

Query

SQL> CREATE OR REPLACE PROCEDURE PR2(2 OUT NUMBER) AS
X NUMBER:=11;
Y NUMBER:=22;
BEGIN
Z:=X+Y;
END;

Output

Procedure created.

Query

SQL> DECLARE
R NUMBER;
BEGIN
PR2(R);
DBMS_OUTPUT.PUT_LINE('RESULT IS: '||R);
END;

Output

RESULT IS : 33
PL/SQL procedure successfully completed.
SQL>

IN OUT Mode

This mode is a mixture of both IN n=and OUT mode. Just like IN mode it passes a value from the calling environment in subprogram and like a OUT mode it possibly pass different value from the subprogram back to the calling environment using the same parameter.

Example: The following example illustrates the working of OUT Mode argument -

Query

SQL> CREATE OR REPLACE PROCEDURE PR3(2 OUT NUMBER) AS
A NUMBER:=11;
BEGIN
B:=A+B;
END;

Output

Procedure created.

Query

SQL> DECLARE
R NUMBER:=22;
BEGIN
PR3(R);
DBMS_OUTPUT.PUT_LINE('RESULT IS: '||R);
END;

Output

RESULT IS : 33
PL/SQL procedure successfully completed.
SQL>

Points to remember while working on with Arguments Modes

1. To increase maintainability and reuse, divide your code into modular components (procedures, functions, and packages).
2. When naming variables, procedures, functions, and packages, be sure to use sensible and consistent conventions.
3. Log or appropriately handle exceptions instead than using empty exception blocks.
4. When possible, avoid utilizing global variables.
5. Utilize the right indexes, stay away from pointless joins, and reduce database round-trips to optimize SQL queries.
6. Write your code to describe its function and purpose.
7. Maintain the documentation for the code.
8. To make sure your PL/SQL code behaves as expected, write unit tests.
9. Implement the proper privileges and access controls for database objects.
10. To increase efficiency, avoid making unnecessary commits inside of loops.
11. Keep a tab on the SQL execution plans and database resources.