Table of Contents Previous Next


4 Stored Procedure Language : 4.2 SPL Programs : 4.2.5 Procedure and Function Parameters

Parameters are declared in the procedure or function definition, enclosed within parenthesis following the procedure or function name. Parameters declared in the procedure or function definition are known as formal parameters. When the procedure or function is invoked, the calling program supplies the actual data that is to be used in the called program’s processing as well as the variables that are to receive the results of the called program’s processing. The data and variables supplied by the calling program when the procedure or function is called are referred to as the actual parameters.
(name [ IN | OUT | IN OUT ] data_type [ DEFAULT value ])
name is an identifier assigned to the formal parameter. If specified, IN defines the parameter for receiving input data into the procedure or function. An IN parameter can also be initialized to a default value. If specified, OUT defines the parameter for returning data from the procedure or function. If specified, IN OUT allows the parameter to be used for both input and output. If all of IN, OUT, and IN OUT are omitted, then the parameter acts as if it were defined as IN by default. Whether a parameter is IN, OUT, or IN OUT is referred to as the parameter’s mode. data_type defines the data type of the parameter. value is a default value assigned to an IN parameter in the called program if an actual parameter is not specified in the call.
In this example, p_deptno is an IN formal parameter, p_empno and p_ename are IN OUT formal parameters, and p_job, p_hiredate, and p_sal are OUT formal parameters.
Note: In the previous example, no maximum length was specified on the VARCHAR2 parameters and no precision and scale were specified on the NUMBER parameters. It is illegal to specify a length, precision, scale or other constraints on parameter declarations. These constraints are automatically inherited from the actual parameters that are used when the procedure or function is called.
The emp_query procedure can be called by another program, passing it the actual parameters. The following is an example of another SPL program that calls emp_query.
In this example, v_deptno, v_empno, v_ename, v_job, v_hiredate, and v_sal are the actual parameters.
You can use either positional or named parameter notation when passing parameters to a function or procedure. If you specify parameters using positional notation, you must list the parameters in the order that they are declared; if you specify parameters with named notation, the order of the parameters is not significant.
To specify parameters using named notation, list the name of each parameter followed by an arrow (=>) and the parameter value. Named notation is more verbose, but makes your code easier to read and maintain.
As previously discussed, a parameter has one of three possible modes - IN, OUT, or IN OUT. The following characteristics of a formal parameter are dependent upon its mode:
As shown by the table, an IN formal parameter is initialized to the actual parameter with which it is called unless it was explicitly initialized with a default value. The IN parameter may be referenced within the called program, however, the called program may not assign a new value to the IN parameter. After control returns to the calling program, the actual parameter always contains the same value as it was set to prior to the call.
The OUT formal parameter is initialized to the actual parameter with which it is called. The called program may reference and assign new values to the formal parameter. If the called program terminates without an exception, the actual parameter takes on the value last set in the formal parameter. If a handled exception occurs, the value of the actual parameter takes on the last value assigned to the formal parameter. If an unhandled exception occurs, the value of the actual parameter remains as it was prior to the call.
Like an IN parameter, an IN OUT formal parameter is initialized to the actual parameter with which it is called. Like an OUT parameter, an IN OUT formal parameter is modifiable by the called program and the last value in the formal parameter is passed to the calling program’s actual parameter if the called program terminates without an exception. If a handled exception occurs, the value of the actual parameter takes on the last value assigned to the formal parameter. If an unhandled exception occurs, the value of the actual parameter remains as it was prior to the call.
You can set a default value of a formal parameter by including the DEFAULT clause or using the assignment operator (:=) in the CREATE PROCEDURE or CREATE FUNCTION statement.
(name [ IN|OUT|IN OUT ] data_type [{DEFAULT | := } expr ])
name is an identifier assigned to the parameter.
IN|OUT|IN OUT specifies the parameter mode.
data_type is the data type assigned to the variable.
expr is the default value assigned to the parameter. If you do not include a DEFAULT clause, the caller must provide a value for the parameter.
The default value is evaluated every time the function or procedure is invoked. For example, assigning SYSDATE to a parameter of type DATE causes the parameter to have the time of the current invocation, not the time when the procedure or function was created.
If the parameter declaration includes a default value, you can omit the parameter from the actual parameter list when you call the function. Calls to the sample procedure (hire_emp) must include two arguments: the employee number (p_empno) and employee name (p_empno). The third parameter (p_hiredate) defaults to the value of SYSDATE:
Adds a new employee with a hiredate of February 15, 2010, regardless of the current value of SYSDATE.
You can write the same function by substituting the DEFAULT keyword for the assignment operator:

4 Stored Procedure Language : 4.2 SPL Programs : 4.2.5 Procedure and Function Parameters

Table of Contents Previous Next