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 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 '20140901'
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 T specified using a SQLSTATE variable rather than a named condition:
Declare continue handler for sqlstate '20140901'
SET l_done = 1;
Same as the previous two examples, counter t specified using a MySQL error code variable rather than a named condition or SQLSTATE variable:
Declare continue handler for 1329
SET l_done = 1;
Error Handling example
There are several types of error handling statements:
§ If any error (not found) occurs, set l_error to 1 and continue execution:
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
SET l_error = 1;
§ If any error occurs (not found), execute ROLLBACK and generate an error message to exit the current block or stored procedure.
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
SELECT 'error occurred-terminating ';
END;
§ If a MySQL 1062 error (duplicate key value) occurs, execute the SELECT statement (send a message to the calling program) and continue to execute
Declare continue hander for 106 2
SELECT 'duplicate key in Index ';
§ If the SQLSTATE 2300 error (duplicate key value) occurs, execute the SELECT statement (send a message to the calling program) and continue to execute
Declare continue hander for sqlstate '20140901'
SELECT 'duplicate key in Index ';
§ When no return value is returned for a cursor or SQL selection statement, set l_done = 1 and continue execution.
DECLARE CONTINUE HANDLER FOR NOT
FOUND
SET l_done = 1;
§ In this example, apart from using the SQLSTATE variable rather than the naming condition, this is the same as in the previous example.
Declare continue handler for sqlstate '20140901'
SET l_done = 1;
§ In this example, besides using the MySQL error code variable instead of the naming condition or SQLSTATE variable
Declare continue handler for 1329
SET l_done = 1;