Live ORACLE

-- 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;

/


CALL award_bonus(150, 400);

Creating a Stored Subprogram