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... 1261 views Андрей Волков Wed, 12 Sep 2018, 15:23:39
PL/SQL package: Collecting Rel...
PL/SQL package: Collecting Rel... 1033 views sepia Sat, 01 Dec 2018, 10:54:57
Why Oracle DBAs learn PL/SQL a...
Why Oracle DBAs learn PL/SQL a... 1245 views Андрей Волков Wed, 12 Sep 2018, 14:43:12
Introduction to PL/SQL
Introduction to PL/SQL 1749 views Antoniy Wed, 12 Sep 2018, 15:18:13

Comments (0)

There are no comments posted here yet

Leave your comments

  1. Posting comment as a guest. Sign up or login to your account.
0 Characters
Attachments (0 / 3)
Share Your Location
Type the text presented in the image below