Skip to Main Content
MySQL Stored Procedure Programming
book

MySQL Stored Procedure Programming

by Guy Harrison, Steven Feuerstein
March 2006
Intermediate to advanced content levelIntermediate to advanced
640 pages
17h 8m
English
O'Reilly Media, Inc.
Content preview from 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;
Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

MySQL Concurrency: Locking and Transactions for MySQL Developers and DBAs

MySQL Concurrency: Locking and Transactions for MySQL Developers and DBAs

Jesper Wisborg Krogh
MySQL 8 Administrator???s Guide

MySQL 8 Administrator???s Guide

Chintan Mehta, Hetal Oza, Subhash Shah, Ravi Shah
MySQL Cookbook, 4th Edition

MySQL Cookbook, 4th Edition

Sveta Smirnova, Alkin Tezuysal
Learning MySQL, 2nd Edition

Learning MySQL, 2nd Edition

Vinicius M. Grippa, Sergey Kuzmichev

Publisher Resources

ISBN: 0596100892Supplemental ContentErrata Page