Open In App

Argument Modes in PL/SQL

Improve
Improve
Like Article
Like
Save
Share
Report

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:

Representation of how modes interact with calling environment

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.

FAQs on Argument modes in PL/SQL

Q.1: Can the parameters of a method or function have different argument modes?

Answer:

Yes, several argument modes are permissible for parameters in a method or function. For instance, the same subprogram can contain both IN and OUT parameters.

Q.2: In PL/SQL, how can I declare parameters with argument modes?

Answer:

The syntax for declaring parameters with argument modes in a method or function is “parameter_name parameter_mode data_type”.

Q.3: Can I use argument modes with default values for parameters?

Answer:

In PL/SQL, default values for parameters with argument modes cannot be provided. Only parameters without argument modes are permitted to have default values.


Last Updated : 19 Sep, 2023
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads