In the following statement, the parameters rating and v_empno are passed in to the procedure. The time in service is returned as a date duration in output parameter return_parm. If the time in service with the company is less then 6 months, the GOTO statement transfers control to the end of the procedure and new_salary is left unchanged.
CREATE PROCEDURE adjust_salary (IN v_empno CHAR(6), IN rating INTEGER, OUT return_parm DECIMAL(8,2)) LANGUAGE SQL MODIFIES SQL DATA BEGIN DECLARE new_salary DECIMAL(9,2); DECLARE service DECIMAL(8,2); SELECT salary, CURRENT_DATE - hiredate INTO new_salary, service FROM employee WHERE empno = v_empno; IF service < 600 THEN GOTO exit1; END IF; IF rating = 1 THEN SET new_salary = new_salary + (new_salary * .10); ELSEIF rating = 2 THEN SET new_salary = new_salary + (new_salary * .05); END IF; UPDATE employee SET salary = new_salary WHERE empno = v_empno; EXIT1: SET return_parm = service; END
(C) Copyright IBM Corporation 1992, 2006. All Rights Reserved.