MySQL Stored Procedure Programming

Putting It All Together

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;