March 2006
Intermediate to advanced
640 pages
17h 8m
English
In Example 2-7 we show a stored procedure that uses all the features of the stored program language we have covered so far in this tutorial.
Example 2-7. A more complex stored procedure
1 CREATE PROCEDURE putting_it_all_together(in_department_id INT) 2 MODIFIES SQL DATA 3 BEGIN 4 DECLARE l_employee_id INT; 5 DECLARE l_salary NUMERIC(8,2); 6 DECLARE l_department_id INT; 7 DECLARE l_new_salary NUMERIC(8,2); 8 DECLARE done INT DEFAULT 0; 9 10 DECLARE cur1 CURSOR FOR 11 SELECT employee_id, salary, department_id 12 FROM employees 13 WHERE department_id=in_department_id; 14 15 16 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1; 17 18 CREATE TEMPORARY TABLE IF NOT EXISTS emp_raises 19 (employee_id INT, department_id INT, new_salary NUMERIC(8,2)); 20 21 OPEN cur1; 22 emp_loop: LOOP 23 24 FETCH cur1 INTO l_employee_id, l_salary, l_department_id; 25 26 IF done=1 THEN /* No more rows*/ 27 LEAVE emp_loop; 28 END IF; 29 30 CALL new_salary(l_employee_id,l_new_salary); /*get new salary*/ 31 32 IF (l_new_salary<>l_salary) THEN /*Salary changed*/ 33 34 UPDATE employees 35 SET salary=l_new_salary 36 WHERE employee_id=l_employee_id; 37 /* Keep track of changed salaries*/ 38 INSERT INTO emp_raises (employee_id,department_id,new_salary) 39 VALUES (l_employee_id,l_department_id,l_new_salary); 40 END IF; 41 42 END LOOP emp_loop; 43 CLOSE cur1; 44 /* Print out the changed salaries*/ 45 SELECT employee_id,department_id,new_salary from emp_raises 46 ORDER BY employee_id; 47 END;