DROP PROCEDURE UPDATE_SAL@
CREATE PROCEDURE UPDATE_SAL
(IN empNum CHAR(6), IN rating SMALLINT)
LANGUAGE SQL
BEGIN
IF rating = 1 THEN
UPDATE employee
SET salary = salary * 1.10,
bonus = 1000
WHERE empno = empNum;
ELSEIF rating = 2 THEN
UPDATE employee
SET salary = salary * 1.05,
bonus = 500
WHERE empno = empNum;
ELSE
UPDATE employee
SET salary = salary * 1.05,
bonus = 0
WHERE empno = empNum;
END IF;
END@
-- Use SQL below to check salary change before and after call the stored procedure.
-- select salary, bonus from employee where empno ='000010'
--CALL UPDATE_SAL('000010', 2)@