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 SQLEXCEPTIO N
    SET l_error=1; 
  
• If any error condition arises (other than a NOT FOUND ), exit the current block or stored program after issuing a ROLLBACK 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 the SELECT statement (which generates a message for the calling program):
  
    DECLARE CONTINUE HANDER FOR 106 2 
        SELECT 'Duplicate key in index';
  
• If SQLSTATE 23000 (duplicate key value) is encountered, continue execution after executing the SELECT 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 setting l_done=1 :
  
    DECLARE CONTINUE HANDLER FOR NOT
  FOUN D
        SET l_done=1; 
  
• Same as the previous example, except specified using a SQLSTATE variable 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 or SQLSTATE variable:
  
    DECLARE CONTINUE HANDLER FOR 132 9
        SET l_done=1;