What is Pass by value and Pass by reference?

The PL/SQL has two different methods for passing parameter values between stored procedures and functions.

1. Pass by value
Actual value is being copied to another memory location for the calling procedure/function to use. i.e. it copies the actual value of the parameter into the formal parameter. So, both called and calling procedures has got 2 different memory locations to store the value.

2. Pass by reference

Only the memory location (address) is passed so that the data is not copied. The calling procedure and the called procedure, both uses the same value stored in a single memory location. So the actual and the formal parameters refer to the same memory location that holds the value.

As we know, there are three different types of parameters in PL/SQL:

We specify any one of the below types, along with the parameters so classify them as:

IN - Parameters passed to the procedure/function
OUT - Parameters that store the values returned (explicitly) from the procedure/function
IN OUT - Can pass values to the procedure and store the returned values

And by default, OUT and IN OUT parameters are passed by value whereas the IN parameters are always passed by reference. IN parameters are designed in such a way to reduce memory consumption and at the same time, the values won’t get overwritten since IN doesn’t allow give write access to the memory location.

And for OUT and IN OUT, when the values are modified by either the called procedure/function or the calling procedure/function, only their local copies get affected and not the original ones.