Parameter modes of Oracle processes and functions

I. In, out, and in out modes

In Oracle, procedures and functions can both have parameters. The parameter type can be specified as in, out, or in out.

The three parameters are described as follows:

(1) in Mode

In mode is passed by reference. During the call process, the actual parameter passes the value as a reference to the form parameter of the stored procedure. The form parameter is in read-only mode during the process, that is: the actual parameter value can only be read through the form parameter. After the process is completed, the actual parameter value does not change.

Process:

Create or replace procedure MyProcedure (param1 in INTEGER) -- param1 form parameter

AS

BEGIN

........

END

Call:

Num: = 100;

MyProcedure (num); -- num actual Parameter

Analysis:

The actual parameter num passes its value 100 to the form parameter param1 of the process, and the value of param1 changes to 100. After the process is executed, the statement of the call process is returned, the actual value of num does not change.

(2) out mode

The out mode is value transfer. During the call process, the formal parameter ignores the actual parameter value and is initialized to a null value. The formal parameter can be read and written, so the value of the formal parameter can be modified during the process, after the process is completed, the value of the formal parameter is assigned to the actual parameter.

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 Parameter

Analysis:

The actual parameter cannot pass its value to the form parameter of the process. The initial value of the form parameter of the opposite process is null. After the process is executed, the value of param1 becomes 1, when the process returns, the value of the formal parameter is assigned to the actual parameter, so the value of num is changed from 100 to 1;

(3) in out mode

In out mode, values are passed. During the call process, the actual parameter value is assigned to the formal parameter by passing the value. During the process, the value of the formal parameter can be modified. After the process is completed, the format parameter also assigns its value to the actual parameter.

Process:

Create or replace procedure MyProcedure (param1 in out INTEGER) -- param1 format parameter, can accept the value from the actual Parameter

AS

BEGIN

DBMS_OUTPUT.PUT_LINE (param1); -- param1 is null

Param1: = 1;

END

Call:

Num: = 100;

MyProcedure (num); -- num actual Parameter

Analysis:

The actual parameter num will pass its value 100 to the form parameter param1 during the process. The value of param1 is initialized to 100, and the value of param1 in the process is changed to 1, after the process is completed, return the value of the form parameter param1 to the actual parameter num, so the value of num is changed from 100 to 1;

Replace the nominal value or constant as an actual parameter because of the existence of the value. Therefore, the actual parameter associated with an out or in out mode parameter must be a variable rather than a constant or expression. There must be a location where the returned values can be stored.

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

Ii. Pass value and reference

By default, the in mode of PL/SQL is the value passing mode, and the ou and in out modes are the reference passing mode.

The reference transmission method is highly efficient, and it is particularly outstanding when transferring large PL/SQL arrays.

NOCOPY prompt

· In mode, parameters are always transmitted in reference mode, so NOCOPY prompts cannot be used in mode;

· In out and in out modes, the NOCOPY keyword can be used to tell PL/SQL Compiler to pass parameter values in reference mode. NOCOPY is only a compiler prompt, not a Compilation instruction, so it is not always valid;

· The existence of NOCOPY is ignored in the following situations, as shown in: