Problem
You want to create a PL/SQL procedure that accepts several parameters. However, some of those parameters should be made optional and contain default values.
Solution
You can allow the procedure caller to omit the parameters if default values are declared for the variables within the procedure. The following example shows a procedure declaration that contains default values:
PROCEDURE process_emp_paycheck(EMP_ID IN NUMBER,
PAY_CODE IN NUMBER,
SICK_USED IN NUMBER,
VACATION_USED IN NUMBER,
FEDERAL_TAX IN NUMBER DEFAULT .08,
STATE_TAX IN NUMBER DEFAULT .035);
And here is an example execution:
EXEC process_emp_paycheck(EMP_ID=>10,
PAY_CODE=>10,
VACATION_USED=>8.0,
SICK_USED=>8.0);
Since the procedure contains default values, the parameters can be omitted when the procedure is called.
How It Works
The ability to provide a default value for a variable declaration is optional. To do so, you must provide the declaration of the variable with the keyword DEFAULT
followed by the value, as shown in the solution to this recipe. If a default value is declared, then you needn't specify a value for the parameter when the function or procedure is called. If you do specify a value for a parameter that has a default value, the specified value overrides the default.