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

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 ...

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