A parameter has the three possible modes: IN, OUT, and IN OUT.
- The initial value of the formal parameter when the stored procedure or function is called.
- Whether the called stored procedure or function can modify the formal parameter.
- The process of passing the value of the actual parameter from the calling program to the called program.
- The output value of the formal parameter when an unhandled exception occurs in the called program.
The following table summarizes the behavior of each parameter based on the parameter mode.
| Mode property | IN | IN OUT | OUT |
|---|---|---|---|
| The initial value of the formal parameter | The actual parameter value | The actual parameter value | The actual parameter value |
| Whether the called program can modify the formal parameter | No | Yes | Yes |
| The value of the actual parameter after normal termination of the called program | The original actual parameter value prior to the call | The last value of the formal parameter | The last value of the formal parameter |
| The value of the actual parameter after a handled exception in the called program | The original actual parameter value prior to the call | The last value of the formal parameter | The last value of the formal parameter |
| The value of the actual parameter after an unhandled exception in the called program | The original actual parameter value prior to the call | The original actual parameter value prior to the call | The original actual parameter value prior to the call |
As shown in the table, an IN formal parameter is initialized to an actual parameter only when called, unless it is explicitly initialized with a default value. The IN parameter can be referenced in the called program, but the called program may not assign a new value to the IN parameter. When the called program ends and control returns to the calling program, the actual parameter contains the same value as the parameter is set to before the call.
The OUT formal parameter is initialized to the actual parameter only when called. The called program can reference and assign a new value to the formal parameter. If the called program ends without an exception, the value of the actual parameter is the last value that is assigned to the formal parameter. If a handled exception occurs, the value of the actual parameter is the last value that is assigned to the formal parameter. If an unhandled exception occurs, the value of the actual parameter is the value that is assigned before the call.
Similar to an IN parameter, an IN OUT formal parameter is initialized to the actual parameter only when it is called. Similar to an OUT parameter, an IN OUT formal parameter can be modified by the called program. If the called program ends with no exceptions, the last value of the formal parameter is passed to the actual parameter. If a handled exception occurs, the value of the actual parameter is the last value that is assigned to the formal parameter. If an unhandled exception occurs, the value of the actual parameter is the value that is assigned before the call.