DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET has_error = 1; | The following is another handler which means that in case any error occurs, rollback the previous operation, issue an error message, and exit the current code block. If you declare it inside theBEGIN END
block of a stored procedure, it will terminate stored procedure immediately.
DECLARE EXIT HANDLER FOR SQLEXCEPTION | The following handler means that if there are no more rows to fetch, in case of a cursor or SELECT INTO statement, set the value of the no_row_found
variable to 1 and continue execution.
DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_row_found = 1; | The following handler means that if a duplicate key error occurs, MySQL error 1062 is issued. It issues an error message and continues execution.
DECLARE CONTINUE HANDLER FOR 1062 |
MySQL handler example in stored procedures
First, we create a new table named article_tags
for the demonstration:
CREATE TABLE article_tags( | The article_tags
table stores the relationships between articles and tags. Each article may have many tags and vice versa. For the sake of simplicity, we don’t create articles
and tags
tables, as well as the foreign keys in the article_tags
table.
Next, we create a stored procedure that inserts article id and tag id into the article_tags
table:
DELIMITER $$ | Then, we add tag id 1, 2 and 3 for the article 1 by calling the insert_article_tags
stored procedure as follows:
CALL insert_article_tags(1,1); | After that, we try to insert a duplicate key to check if the handler is really invoked.
CALL insert_article_tags(1,3); | We got an error message. However, because we declared the handler as a CONTINUE
handler, the stored procedure continued the execution. As the result, we got the tag count for the article as well.
If we change the CONTINUE
in the handler declaration to EXIT
, we will get an error message only.
DELIMITER $$ | Finally, we can try to add a duplicate key to see the effect.
CALL insert_article_tags_2(1,3); |
MySQL handler precedence
In case there are multiple handlers that are eligible for handling an error, MySQL will call the most specific handler to handle the error first.
An error always maps to one MySQL error code because in MySQL it is the most specific. AnSQLSTATE
may map to many MySQL error codes therefore it is less specific. An SQLEXCPETION
or an SQLWARNING
is the shorthand for a class of SQLSTATES
values so it is the most generic.
Based on the handler precedence’s rules, MySQL error code handler, SQLSTATE
handler andSQLEXCEPTION
takes the first, second and third precedence.
Suppose we declare three handlers in the insert_article_tags_3
stored procedure as follows:
DELIMITER $$ | We try to insert a duplicate key into the article_tags
table by calling the stored procedure:
CALL insert_article_tags_3(1,3); | As you see the MySQL error code handler is called.
Using named error condition
Let’s start with an error handler declaration.
DECLARE EXIT HANDLER FOR 1051 SELECT 'Please create table abc first'; | What does the number 1051 really mean? Imagine you have a big stored procedure polluted with those numbers all over places; it will become a nightmare to maintain the code.
Fortunately, MySQL provides us with the DECLARE CONDITION
statement that declares a named error condition, which associates with a condition.
The syntax of the DECLARE CONDITION
statement is as follows:
DECLARE condition_name CONDITION FOR condition_value; | The condition_value
can be a MySQL error code such as 1015 or a SQLSTATE
value. Thecondition_value
is represented by the condition_name
.
After declaration, we can refer to condition_name
instead of condition_value
.
So we can rewrite the code above as follows:
DECLARE table_not_found CONDITION for 1051; | This code is obviously more readable than the previous one.
Notice that the condition declaration must appear before handler or cursor declarations.
原标题:MySQL Error Handling in Stored Procedures 2
关键词:MYSQL