-- including OR REPLACE is more convenient when updating a subprogram
CREATE OR REPLACE PROCEDURE award_bonus (emp_id NUMBER, bonus NUMBER) AS  commission REAL;
comm_missing EXCEPTION;
BEGIN -- executable part starts here
 SELECT commission_pct / 100 INTO commission FROM employees
 WHERE employee_id = emp_id;
 IF commission IS NULL THEN
 RAISE comm_missing;
 ELSE
 UPDATE employees SET salary = salary + bonus*commission 
 WHERE employee_id = emp_id;
 END IF;
EXCEPTION -- exception-handling part starts here
 WHEN comm_missing THEN
 DBMS_OUTPUT.PUT_LINE('This employee does not receive a commission.');
 commission := 0;
 WHEN OTHERS THEN
 NULL; -- for other exceptions do nothing
END award_bonus;