Difference between Actual and Formal Parameters in PL/SQL

What is PL/SQL?

PL/SQL is a structured language that is a combination of both SQL and procedural language. Code is written in block format, and this block is passed to the Oracle engine, where it runs and produces the output.

With the help of PL/SQL, we can solve many SQL queries at once using the block. In PL/SQL, there is its own exception handling block, and it can be run on any system or hardware where the Oracle engine is available. It is the basically an extended version of SQL, which is mostly used to create applications. The basic unit of PL/SQL is a block where the block is capable of doing any specific task.

The purpose of using the PL/SQL is that it reduces the traffic from the Oracle engine and increases the speed by passing multiple SQL statements at a time. It is better than SQL because SQL does not provide the features of error checking, but PL.SQL provides.PL/SQL block can be used many times once it is defined. It has many identifiers like variables, constants, procedures etc.

Parameters in PL/SQL

The parameter is a set of variables that are passed into a function or procedure or returned by the procedure. It is also known as an argument to the function. The number of parameters and the use of these parameters in a procedure depend on the programmer.

There are three ways to use the Parameters in PL/SQL:

1. IN Mode

This is the default mode where a constant value is passed into the function or sub-program from the place from where it is being called.

2. OUT Mode

In this mode, a value or parameter is passed from the sub-program to the calling environment.

3. IN OUT Mode

It is the combination of the above modes. There are values passed from the calling place or environment to the subprogram as well as from the subprogram to the calling environment.

Types of Parameters in PL/SQL

Actual parameter

The values or expressions which are entered through a function call are known as actual parameters. There is no need to define the data type of these parameters, and they are referenced from the subprogram.

Example:

// These are the actual paramters
SQL> CREATE OR REPLACE FUNCTION FXN1(X1 NUMBER,  Y1 NUMBER) 
RETURN NUMBER IS
S NUMBER;
BEGIN
S:=X-Y;
RETURN(S);
END;
/

Output:

FUNCTION CREATED.
SQL>|

Formal parameters

Formal parameters are those variables or expressions that are used in the subprogram, and it is necessary to define the data type of these variables. They are scoped to the definition of the subprogram, and outside of this subprogram.

Example:

SQL> DECLARE
X NUMBER:=33;
Y NUMBER:=36;
R NUMBER;
BEGIN
R:=FXN1(N1, N2);
DBMS_OUTOUT.PUT_LINE('sum of the numbers thrugh the functions is: '||R);
END;
/

Output:

sum of the numbers thrugh the functions is: 69
SQL>|

Difference between Actual and Formal Parameters

Actual Parameters	Formal Parameters
Actual parameters are those expressions or variables which are referenced from the function call, which is called, or the subprogram call, which is being called.	Formal parameters are those expressions that are referenced from the list of the parameters of the subprogram.
There is no requirement to define the data type in actual parameters.	It is mandatory to specify the data type of the variable in formal parameters.
There can be any constant value or any names in the actual parameter.	Formal parameters are like local variables to the subprogram or the function in which they are written in the function header.
Actual parameters are those variables that are written in the function call and used when the function call is made.	Formal parameters are those variables that are written in the definition of the function with its correct data type.
Actual parameters are present in the calling function.	Formal parameters are present in the function which is being called.
Parameters that are inserted in the function call are known as actual parameters.	Parameters that are inserted in the function definition are called formal parameters.