PL/SQL procedure: Setting Default Parameter Values

Setting Default Parameter Values in PL/SQL procedureProblem

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.

 

Вас заинтересует / Intresting for you:

Describe the Basic PL/SQL Prog...
Describe the Basic PL/SQL Prog... 375 views Андрей Волков Wed, 12 Sep 2018, 15:23:39
PL/SQL package: Collecting Rel...
PL/SQL package: Collecting Rel... 145 views sepia Sat, 01 Dec 2018, 10:54:57
Why Oracle DBAs learn PL/SQL a...
Why Oracle DBAs learn PL/SQL a... 372 views Андрей Волков Wed, 12 Sep 2018, 14:43:12
Introduction to PL/SQL
Introduction to PL/SQL 493 views Antoniy Wed, 12 Sep 2018, 15:18:13

Comments on PL/SQL procedure: Setting Default Parameter Values

Be the first to comment
Please login to comment