What are Parameter Default values? - Oracle

This is almost similar to the DEFAULT clause you use with column definition. If you don’t provide any value to the column, the DEFAULT value is chosen to replace null automatically. In the same manner, we can design our parameters to the sub-programs in a very flexible manner. We can provide DEFAULT values to the parameters. That means the parameter values are not compulsory. But if we send parameter values, parameters get bound to those values. Let us consider the following program.

declare
 procedure dispEmp(p_sal emp.sal%type := 0, p_deptno emp.deptno%type := null) is
 begin
  if p_deptno is null then
   for r_emp in (select ename from emp where sal > p_sal)
   loop
    dbms_output.put_line(r_emp.ename);
   end loop;
  else
   for r_emp in (select ename from emp where sal > p_sal and deptno = p_deptno)
   loop 
    dbms_output.put_line(r_emp.ename);
   end loop;
  end if;
 end;
BEGIN
 dispEmp(1000,10);
    dbms_output.put_line('-------------');
 dispEmp(2000);
    dbms_output.put_line('-------------');
 dispEmp(p_deptno => 20);
END;

Even though the above program is bit lengthy, it is actually very easy to understand.  If you observe the declaration of procedure, it is something like the following:

procedure dispEmp(p_sal emp.sal%type := 0, p_deptno emp.deptno%type := null) is

The above declaration says that ‘p_sal’ should be considered 0 when no value is provided. Similarly ‘p_deptno’ should be considered null when no value is provided for it.

dispEmp(1000,10);

The above statement states that ‘p_sal’ should be treated as 1000 and ‘p_deptno’ should be treated as 10.

dispEmp(2000);

The above statement states that ‘p_sal’ should be treated as 2000 and ‘p_deptno’ should be treated as null.

dispEmp(p_deptno => 20);

The above statement states that ‘p_sal’ should be treated as 0 and ‘p_deptno’ should be treated as 20. If you observe the above statement carefully, without the named notation, it is impossible to send the value only to ‘p_deptno’.