MySQL Stored Procedure Programming

Putting It All Together

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.

Example 6-21. Stored procedure without error 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 ...