Error 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 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 G Enerates A 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 Gen Erates A 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
FOUND
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 SQL State variable:
DECLARE CONTINUE HANDLER for 1329
SET l_done=1;
Error handling examples
There are several declarative forms of error handling:
§ If any error (not FOUND), set L_error to continue after 1:
DECLARE CONTINUE HANDLER for SQLEXCEPTION
SET l_error=1;
§ Exits the current block or stored procedure after executing rollback and generating an error message if any error occurs (not FOUND).
DECLARE EXIT HANDLER for SQLEXCEPTION
BEGIN
ROLLBACK;
SELECT ' Error occurred–terminating ';
End;
§ If the MySQL 1062 error (Duplicate health value) occurs, executes the SELECT statement (sends a message to the caller) and continues execution
DECLARE CONTINUE hander for 106 2
SELECT ' Duplicate key in index ';
§ If the SQLSTATE 2300 error (a duplicate health value) occurs, executes the SELECT statement (sends a message to the caller) and continues execution
DECLARE CONTINUE hander for SQLSTATE ' 23000 '
SELECT ' Duplicate key in index ';
§ When a cursor or SQL SELECT statement does not return a value, the l_done=1 is set to continue execution
DECLARE CONTINUE HANDLER for not
FOUND
SET l_done=1;
§ This example, in addition to using the SQLSTATE variable rather than the named condition, is the same as the previous example
DECLARE CONTINUE HANDLER for SQLSTATE ' 02000 '
SET l_done=1;
§ In addition to using MySQL's error code variable instead of a named condition or SQLSTATE variable, this example is the previous two examples
DECLARE CONTINUE HANDLER for 1329
SET l_done=1;