PL/SQL User's Guide and Reference

Parameter Default Values

As the example below shows, you can initialize IN parameters to default values. That way, you can pass different numbers of actual parameters to a subprogram, accepting or overriding the default values as you please. Moreover, you can add new formal parameters without having to change every call to the subprogram.

PROCEDURE create_dept (
    new_dname CHAR DEFAULT 'TEMP',
    new_loc   CHAR DEFAULT 'TEMP') IS
BEGIN
   INSERT INTO dept
      VALUES (deptno_seq.NEXTVAL, new_dname, new_loc);
END create_dept;

If an actual parameter is not passed, the default value of its corresponding formal parameter is used. Consider the following calls to create_dept:

create_dept;
create_dept('MARKETING');
create_dept('MARKETING', 'NEW YORK');

The first call passes no actual parameters, so both default values are used. The second call passes one actual parameter, so the default value for new_loc is used. The third call passes two actual parameters, so neither default value is used.

Usually, you can use positional notation to override the default values of formal parameters. However, you cannot skip a formal parameter by leaving out its actual parameter. For example, the following call incorrectly associates the actual parameter 'NEW YORK' with the formal parameter new_dname:

create_dept('NEW YORK');  -- incorrect

You cannot solve the problem by leaving a placeholder for the actual parameter. For example, the following call is illegal:

create_dept( , 'NEW YORK');  -- illegal

In such cases, you must use named notation, as follows:

create_dept(new_loc => 'NEW YORK');

Also, you cannot assign a null to an uninitialized formal parameter by leaving out its actual parameter. For example, given the declaration

DECLARE
   FUNCTION gross_pay (
      emp_id   IN NUMBER, 
      st_hours IN NUMBER DEFAULT 40, 
      ot_hours IN NUMBER) RETURN REAL IS
   BEGIN
      ...
   END;

the following function call does not assign a null to ot_hours:

IF gross_pay(emp_num) > max_pay THEN ...  -- illegal

Instead, you must pass the null explicitly, as in

IF gross_pay(emp_num, ot_hour => NULL) > max_pay THEN ...

or you can initialize ot_hours to NULL, as follows:

ot_hours IN NUMBER DEFAULT NULL;

Finally, when creating a stored subprogram, you cannot use bind variables in the DEFAULT clause. The following SQL*Plus example raises a bad bind variable exception because at the time of creation, num is just a placeholder whose value might change:

SQL> VARIABLE num NUMBER
SQL> CREATE FUNCTION gross_pay (emp_id IN NUMBER DEFAULT :num, ...