An explanation of the parameter patterns of Oracle processes and functions

One, in, out, in out mode

In Oracle, procedures and functions can have parameters, and the type of the parameter can be specified as in, out, and out of three modes.

Three specific descriptions of the parameters, as shown in:

(1) in mode

In mode is a reference pass. When a procedure is called, the actual argument passes the value as a reference to the form parameter of the stored procedure, and the formal parameter is read-only in the process, that is, the value of the actual parameter can only be read through the formal parameter. When the procedure has finished executing, the value of the actual parameter will not change.

Process:

Create or Replace procedure Myprocedure (param1 in INTEGER)--param1 form parameters

As

BEGIN

........

END

Call:

num:=100;

Myprocedure (num); --num actual parameters

Analysis:

The actual parameter num passes its value 100 to the value of the formal parameter param1,param1 of the procedure to 100, and returns to the statement of the calling procedure after the procedure executes, without any change in the value of the actual parameter num.

(2) Out mode

The out mode is a value pass. When invoking a procedure, the formal parameter ignores the value of the actual parameter and is initialized to a null value, and the formal parameter is read-write, so the value of the formal parameter can be modified during the process, and the formal parameter assigns its value to the actual parameter when the procedure is finished.

Process

Create or Replace procedure Myprocedure (param1 out INTEGER)--param1 form parameter

As

BEGIN

Dbms_output.    Put_Line (param1); --PARAM1 is null

Param1:=1; --Modify the value of param1 to 1

END

Call:

num:=100;

Myprocedure (num); --num actual parameters

Analysis:

The actual parameter does not pass its value to the formal parameter of the procedure, but the initial value of the formal parameter of the procedure is null, the value of param1 becomes 1 when the procedure is executed, and the value of the formal parameter is assigned to the actual parameter when the process returns, so NUM's value changes from 100 to 1;

(3) in out mode

In-out mode is the value passed. When the procedure is called, the value of the actual parameter is assigned to the formal parameter in the way the value is passed, and the value of the formal parameter can be modified in the process, and the formal parameter assigns its value to the actual parameter when the process is finished.

Process:

Create or Replace procedure Myprocedure (param1 in Out INTEGER)--param1 form parameter, can accept the value that the actual parameter transmits

As

BEGIN

Dbms_output.    Put_Line (param1); --PARAM1 is null

Param1:=1;

END

Call:

num:=100;

Myprocedure (num); --num actual parameters

Analysis:

The actual parameter num passes its value 100 to the formal parameter of the procedure param1,param1 the value to 100, the value of param1 in the procedure execution becomes 1, and so on when the process is completed and returns the value of the formal parameter param1 to the actual parameter num, So num's value changed from 100 to 1;

Literals or constants are treated as actual parameters due to the existence of a value copy. So the actual argument associated with the form parameter of the out or in-out pattern must be a variable, not a constant or an expression. You must have a location where the return value can be stored.

The main difference between out mode and in-out mode is that in-out mode can pass the value of the actual parameter to the formal parameter of the procedure, and the initial value of the form parameter of the out pattern can only be null.

Second, the value of the transfer and the reference

By default, the default is in mode, parameter receives variables, constants, and arguments can be constants, variables, expression types

The way the reference is passed is relatively efficient, especially when passing large PL/SQL arrays.

Nocopy Tips

The combinatorial mode always passes parameters as a reference, so it is not allowed to use the NOCOPY hint in the in mode;

The out-in-out mode can tell the PL/SQL compiler to pass the parameter value by using the Nocopy keyword, which is not always valid except for a compiler hint, not a compile instruction;

• The presence of nocopy is ignored in the following scenarios, as shown in:

An explanation of the parameter patterns of Oracle processes and functions