We have now covered in detail the error-handling features of MySQL. We’ll finish up this discussion by offering an example that puts all of these features together. We will take a simple stored procedure that contains no exception handling and apply the concepts from this chapter to ensure that it will not raise any unhandled exceptions for all problems that we can reasonably anticipate.
The example stored procedure creates a new
departments row. It takes the names of the
new department, the manager of the department, and the department’s
location. It retrieves the appropriate
employee_id from the
employees table using the manager’s name.
Example 6-21 shows the
version of the stored procedure without exception handling.
CREATE PROCEDURE sp_add_department (p_department_name VARCHAR(30), p_manager_surname VARCHAR(30), p_manager_firstname VARCHAR(30), p_location VARCHAR(30), out p_sqlcode INT, out p_status_message VARCHAR(100)) MODIFIES SQL DATA BEGIN DECLARE l_manager_id INT; DECLARE csr_mgr_id cursor for SELECT employee_id FROM employees WHERE surname=UPPER(p_manager_surname) AND firstname=UPPER(p_manager_firstname); OPEN csr_mgr_id; FETCH csr_mgr_id INTO l_manager_id; INSERT INTO departments (department_name,manager_id,location) VALUES(UPPER(p_department_name),l_manager_id,UPPER(p_location)); CLOSE csr_mgr_id; END$$
This program reflects the typical development process for many of us: we concentrate on implementing ...