Oracle subroutine parameter mode, IN, OUT, NOCOPY

The parameter modes of Oracle subprograms include IN, OUT, NOCOPY, IN and OUT, and OUT and NOCOPY.

The parameter modes of Oracle subprograms include IN, OUT, NOCOPY, IN and OUT, and OUT and NOCOPY.

IN is mainly used to input parameters, which can be variables, constants, and expressions. The value cannot be changed within the subroutine.
The Code is as follows:
DECLARE
N NUMBER: = 10;
PROCEDURE do_something (
N1 in number) IS
BEGIN
Dbms_output.put_line (n1); -- prints 10
-- N1: = 20; -- illegal assignment.
END;
BEGIN
Do_something (n );
Do_something (20 );
END;

The OUT mode is used to return values. variables must be called. The initial values of variables are not passed to formal parameters, as shown in <1>.
The parameter value is copied to the real parameter only when the subroutine is returned (not when the form parameter is changed), as shown in <2>. If an exception occurs before the return, the actual parameter value is not changed.
The Code is as follows:
DECLARE
N NUMBER: = 10;
PROCEDURE do_something (
N1 out number) IS
BEGIN
Dbms_output.put_line ('before assign: '| n1); -- prints none <1>
N1: = 20;
Dbms_output.put_line ('before return: '| n); -- prints 10 <2>
END;
BEGIN
Do_something (n );
Dbms_output.put_line ('after return: '| n); -- prints 20
END;

NOCOPY mode is used to determine whether the OUT mode is implemented by reference (it is just a compiler suggestion and does not always work). By default, the OUT mode parameter is called by passing the value.
IN is mainly used to pass IN parameters. Although n2: = 20 is called, it takes effect only when it is returned, as shown IN <1>.
NOCOPY is a reference and will take effect immediately when the value is assigned, as shown in <2>. If an exception occurs before the return, the actual parameter value will also be changed.
Since the OUT parameter will copy the value returned by the subroutine to the actual parameter, the value of n after the call is 20, as shown in <3>.
The Code is as follows:
DECLARE
N NUMBER: = 10;
PROCEDURE do_something (
N1 in number,
N2 in out number,
N3 in out nocopy number) IS
BEGIN
N2: = 20;
Dbms_output.put_line (n1); -- prints 10 <1>
N3: = 30;
Dbms_output.put_line (n1); -- prints 30 <2>
END;
BEGIN
Do_something (n, n, n );
Dbms_output.put_line (n); -- prints 20 <3>
END;