Error Handling Examples

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;