In the CREATE PROCEDURE or CREATE FUNCTION statement, you can set a default value for a formal parameter by including the DEFAULT clause or using the assignment operator (:=).
(name [ IN|OUT|IN OUT ] data_type [{DEFAULT | := } expr ])
name specifies the identifier assigned to the parameter. IN|OUT|IN OUT specifies the parameter mode. data_type specifies the data type assigned to the variable. expr specifies the default value assigned to the parameter. You must provide a value for the parameter if a DEFAULT clause is not included.
Each time you call a function or stored procedure, the default value is evaluated. For example, if you assign SYSDATE to a parameter of the DATE type, the value of the parameter will be the time of the current call. The parameter value no longer indicates the time when the stored procedure or function is created.
CREATE OR REPLACE PROCEDURE hire_emp (
p_empno NUMBER,
p_ename VARCHAR2,
p_hiredate DATE := SYSDATE
) RETURN
IS
BEGIN
INSERT INTO emp(empno, ename, hiredate)
VALUES(p_empno, p_ename, p_hiredate);
DBMS_OUTPUT.PUT_LINE('Hired!') ;
END emp_comp;
hire_emp 7575, Clark
hire_emp 7575, Clark, 15-FEB-2010
This example indicates that you add a new employee whose employment date is February 15, 2010 regardless of the current value of SYSDATE.
CREATE OR REPLACE PROCEDURE hire_emp (
p_empno NUMBER,
p_ename VARCHAR2,
p_hiredate DATE DEFAULT SYSDATE
) RETURN
IS
BEGIN
INSERT INTO emp(empno, ename, hiredate)
VALUES(p_empno, p_ename, p_hiredate);
DBMS_OUTPUT.PUT_LINE('Hired!') ;
END emp_comp;