Oracle and PL/SQL Recipes: A Problem-Solution Approach

4-8. Setting Default Parameter Values

Problem

You want to create a 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,                            ...