MySQL Stored Procedure Programming

Name

EXC-01: Handle exceptions that cannot be avoided but can be anticipated

Synopsis

If you are writing a program in which you can predict that a certain error will occur, you should include a handler in your code for that error, allowing for a graceful and informative failure.

Example

This recommendation is easily demonstrated with a simple, single-row lookup cursor. An error that often occurs is No data to FETCH, which indicates that the cursor didn't identify any rows. Consider the following function that returns the name of a department for its ID:

    CREATE FUNCTION department_name(in_dept_id INT) RETURNS VARCHAR(30)
      READS SQL DATA
    BEGIN
      DECLARE  v_dept_name VARCHAR(30);

      DECLARE dept_csr CURSOR FOR
      SELECT department_name
        FROM departments
       WHERE department_id=in_dept_id;

       OPEN dept_csr;
       FETCH dept_csr INTO  v_dept_name;
       CLOSE dept_csr;

      RETURN  v_dept_name;
    END;

As currently coded, this function will raise the No data to FETCH error if an invalid department ID is passed in.

    mysql> SELECT department_name(1);
    +--------------------+
    | department_name(1) |
    +--------------------+
    | DUPLIN             |
    +--------------------+
    1 row in set (0.00 sec)

    mysql> SELECT department_name(60);
    ERROR 1329 (02000): No data to FETCH

That may be fine for some scenarios, but in this particular case, we simply want to return a special string (No such Department). The program that calls department_name can then decide for itself if it wants or needs to raise an error or simply proceed. In this case, the solution is to add ...