Error Handling Examples
(Page 1 of 4 )

In this second article in a three-part series, you will learn more about errors and exception handlers. It is excerpted from chapter six of the book MySQL Stored Procedure Programming, written by Guy Harrison and Steven Feuerstein (O'Reilly; ISBN: 0596100892). Copyright © 2006 O'Reilly Media, Inc. All rights reserved. Used with permission from the publisher. Available from booksellers or direct from O'Reilly Media.

Handler Examples

Here are some examples of handler declarations:

• If any error condition arises (other than a NOT FOUND), continue execution after setting l_error=1:
  
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    SET l_error=1; 
  
• If any error condition arises (other than aNOT FOUND), exit the current block or stored program after issuing aROLLBACK statement and issuing an error message: 
  
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SELECT 'Error occurred – terminating';
    END; 
  
• If MySQL error 1062 (duplicate key value) is encountered, continue execution after executing theSELECTstatement (which generates a message for the calling program):
  
    DECLARE CONTINUE HANDER FOR 1062 
        SELECT 'Duplicate key in index';
  
• IfSQLSTATE23000 (duplicate key value) is encountered, continue execution after executing theSELECT statement (which generates a message for the calling program):
  
    DECLARE CONTINUE HANDER FOR SQLSTATE '23000'
        SELECT 'Duplicate key in index'; 
  
• When a cursor fetch or SQL retrieves no values, continue execution after settingl_done=1:
  
    DECLARE CONTINUE HANDLER FOR NOT
  FOUND
        SET l_done=1; 
  
• Same as the previous example, except specified using aSQLSTATEvariable rather than a named condition:
  
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000'
        SET l_done=1; 
  
• Same as the previous two examples, except specified using a MySQL error code variable rather than a named condition orSQLSTATE variable:
  
    DECLARE CONTINUE HANDLER FOR 1329
        SET l_done=1;

Next: Handler Precedence >>
 

More MySQL Articles
More By O'Reilly Media

Comments On: Error Handling Examples

· This article is an excerpt from the book "MySQL Stored Procedure Programming,"...   >>> Post your comment now!

Buy this book...

This article is excerpted from chapter six of the book MySQL Stored Procedure Programming, written by Guy Harrison and Steven Feuerstein (O'Reilly; ISBN: 0596100892). Check it out today at your favorite bookstore. Buy this book now.