The following SQL procedure accepts two IN parameters: an employee number and an employee rating. Depending on the value of rating, the employee table is updated with new values in the salary and bonus columns.
CREATE PROCEDURE UPDATE_SALARY_IF (IN employee_number CHAR(6), INOUT rating SMALLINT) LANGUAGE SQL MODIFIES SQL DATA BEGIN DECLARE not_found CONDITION FOR SQLSTATE '02000'; DECLARE EXIT HANDLER FOR not_found SET rating = -1; IF rating = 1 THEN UPDATE employee SET salary = salary * 1.10, bonus = 1000 WHERE empno = employee_number; ELSEIF rating = 2 THEN UPDATE employee SET salary = salary * 1.05, bonus = 500 WHERE empno = employee_number; ELSE UPDATE employee SET salary = salary * 1.03, bonus = 0 WHERE empno = employee_number; END IF; END
(C) Copyright IBM Corporation 1992, 2006. All Rights Reserved.