Declaring a handler

发布时间：2020-12-11 23:55:55 所属栏目：MySql教程来源：网络整理

导读：This tutorial shows you how to use MySQL handler to handle exceptions or errors encountered in stored procedures. When an error occurs inside a stored procedure,it is important to handle it appropriately,such as continuing or exiting the c

</table>

The following is another handler; it 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 the???BEGIN END?block of a stored procedure,it will terminate stored procedure immediately.

</td>
<td class="crayon-code">
<div class="crayon-pre">
<div id="crayon-548161c73f066533478329-1" class="crayon-line"><span class="crayon-r">DECLARE<span class="crayon-h"> <span class="crayon-e">EXIT <span class="crayon-e">HANDLER <span class="crayon-st">FOR<span class="crayon-h"> <span class="crayon-e">SQLEXCEPTION
<div id="crayon-548161c73f066533478329-2" class="crayon-line crayon-striped-line"><span class="crayon-e">BEGIN
<div id="crayon-548161c73f066533478329-3" class="crayon-line"><span class="crayon-v">ROLLBACK<span class="crayon-sy">;
<div id="crayon-548161c73f066533478329-4" class="crayon-line crayon-striped-line"><span class="crayon-i">SELECT<span class="crayon-h"> <span class="crayon-s">'An error has occurred,operation rollbacked and the stored procedure was terminated'<span class="crayon-sy">;
<div id="crayon-548161c73f066533478329-5" class="crayon-line"><span class="crayon-st">END<span class="crayon-sy">;

</td>

</tr></table>

If there are no more rows to fetch,in case of a?or?statement,set the value of the?no_row_found?variable to 1 and continue execution.

</td>
<td class="crayon-code">
<div class="crayon-pre">
<div id="crayon-548161c73f06e998259390-1" class="crayon-line"><span class="crayon-r">DECLARE<span class="crayon-h"> <span class="crayon-st">CONTINUE<span class="crayon-h"> <span class="crayon-e">HANDLER <span class="crayon-st">FOR<span class="crayon-h"> <span class="crayon-st">NOT<span class="crayon-h"> <span class="crayon-e">FOUND <span class="crayon-e">SET <span class="crayon-v">no_row_found<span class="crayon-h"> <span class="crayon-o">=<span class="crayon-h"> <span class="crayon-cn">1<span class="crayon-sy">;

</td>

</tr></table>

If a duplicate key error occurs,MySQL error 1062 is issued. The following handler issues an error message and continues execution.

</td>
<td class="crayon-code">
<div class="crayon-pre">
<div id="crayon-548161c73f075760806018-1" class="crayon-line"><span class="crayon-r">DECLARE<span class="crayon-h"> <span class="crayon-st">CONTINUE<span class="crayon-h"> <span class="crayon-e">HANDLER <span class="crayon-st">FOR<span class="crayon-h"> <span class="crayon-cn">1062
<div id="crayon-548161c73f075760806018-2" class="crayon-line crayon-striped-line"><span class="crayon-i">SELECT<span class="crayon-h"> <span class="crayon-s">'Error,duplicate key occurred'<span class="crayon-sy">;

</td>

</tr></table>

MySQL handler example in stored procedures

First,we create a new table named??article_tags?for the demonstration:

</td>
<td class="crayon-code">
<div class="crayon-pre">
<div id="crayon-548161c73f07c185701323-1" class="crayon-line"><span class="crayon-e">CREATE <span class="crayon-e">TABLE <span class="crayon-e">article_tags<span class="crayon-sy">(
<div id="crayon-548161c73f07c185701323-2" class="crayon-line crayon-striped-line"><span class="crayon-h">????<span class="crayon-e">article_id <span class="crayon-t">INT<span class="crayon-sy">,
<div id="crayon-548161c73f07c185701323-3" class="crayon-line"><span class="crayon-h">????<span class="crayon-e">tag_id???? <span class="crayon-t">INT<span class="crayon-sy">,
<div id="crayon-548161c73f07c185701323-4" class="crayon-line crayon-striped-line"><span class="crayon-h">????<span class="crayon-e">PRIMARY <span class="crayon-e">KEY<span class="crayon-sy">(<span class="crayon-v">article_id<span class="crayon-sy">,<span class="crayon-v">tag_id<span class="crayon-sy">)
<div id="crayon-548161c73f07c185701323-5" class="crayon-line"><span class="crayon-sy">)<span class="crayon-sy">;

</td>

</tr></table>

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??in the??article_tags?table.

Second,we create a stored procedure that inserts a pair of ids of article and tag into the?article_tags?table:

</td>
<td class="crayon-code">
<div class="crayon-pre">
<div id="crayon-548161c73f084954874343-1" class="crayon-line"><span class="crayon-i">DELIMITER<span class="crayon-h"> <span class="crayon-sy">$<span class="crayon-sy">$
<div id="crayon-548161c73f084954874343-2" class="crayon-line crayon-striped-line">?
<div id="crayon-548161c73f084954874343-3" class="crayon-line"><span class="crayon-e">CREATE <span class="crayon-e">PROCEDURE <span class="crayon-e">insert_article_tags<span class="crayon-sy">(<span class="crayon-st">IN<span class="crayon-h"> <span class="crayon-e">article_id <span class="crayon-t">INT<span class="crayon-sy">,<span class="crayon-h"> <span class="crayon-st">IN<span class="crayon-h"> <span class="crayon-e">tag_id <span class="crayon-t">INT<span class="crayon-sy">)
<div id="crayon-548161c73f084954874343-4" class="crayon-line crayon-striped-line"><span class="crayon-e">BEGIN
<div id="crayon-548161c73f084954874343-5" class="crayon-line">?
<div id="crayon-548161c73f084954874343-6" class="crayon-line crayon-striped-line"><span class="crayon-e"> <span class="crayon-r">DECLARE<span class="crayon-h"> <span class="crayon-st">CONTINUE<span class="crayon-h"> <span class="crayon-e">HANDLER <span class="crayon-st">FOR<span class="crayon-h"> <span class="crayon-cn">1062
<div id="crayon-548161c73f084954874343-7" class="crayon-line"><span class="crayon-h"> <span class="crayon-e">SELECT <span class="crayon-e">CONCAT<span class="crayon-sy">(<span class="crayon-s">'duplicate keys ('<span class="crayon-sy">,<span class="crayon-v">article_id<span class="crayon-sy">,<span class="crayon-s">','<span class="crayon-sy">,<span class="crayon-v">tag_id<span class="crayon-sy">,<span class="crayon-s">') found'<span class="crayon-sy">)<span class="crayon-h"> <span class="crayon-st">AS<span class="crayon-h"> <span class="crayon-v">msg<span class="crayon-sy">;
<div id="crayon-548161c73f084954874343-8" class="crayon-line crayon-striped-line">?
<div id="crayon-548161c73f084954874343-9" class="crayon-line"><span class="crayon-h"> <span class="crayon-o">--<span class="crayon-h"> <span class="crayon-i">insert<span class="crayon-h"> <span class="crayon-i">a<span class="crayon-h"> <span class="crayon-r">new<span class="crayon-h"> <span class="crayon-e">record <span class="crayon-e">into <span class="crayon-e">article_tags
<div id="crayon-548161c73f084954874343-10" class="crayon-line crayon-striped-line"><span class="crayon-e"> <span class="crayon-e">INSERT <span class="crayon-e">INTO <span class="crayon-e">article_tags<span class="crayon-sy">(<span class="crayon-v">article_id<span class="crayon-sy">,<span class="crayon-v">tag_id<span class="crayon-sy">)
<div id="crayon-548161c73f084954874343-11" class="crayon-line"><span class="crayon-h"> <span class="crayon-e">VALUES<span class="crayon-sy">(<span class="crayon-v">article_id<span class="crayon-sy">,<span class="crayon-v">tag_id<span class="crayon-sy">)<span class="crayon-sy">;
<div id="crayon-548161c73f084954874343-12" class="crayon-line crayon-striped-line">?
<div id="crayon-548161c73f084954874343-13" class="crayon-line"><span class="crayon-h"> <span class="crayon-o">--<span class="crayon-h"> <span class="crayon-st">return<span class="crayon-h"> <span class="crayon-e">tag <span class="crayon-e">count <span class="crayon-st">for<span class="crayon-h"> <span class="crayon-e">the <span class="crayon-e">article
<div id="crayon-548161c73f084954874343-14" class="crayon-line crayon-striped-line"><span class="crayon-e"> <span class="crayon-e">SELECT <span class="crayon-e">COUNT<span class="crayon-sy">(<span class="crayon-o">*<span class="crayon-sy">)<span class="crayon-h"> <span class="crayon-e">FROM <span class="crayon-v">article_tags<span class="crayon-sy">;
<div id="crayon-548161c73f084954874343-15" class="crayon-line"><span class="crayon-st">END

</td>

</tr></table>

Third,we add tag id 1,2 and 3 for the article 1 by calling the??insert_article_tags?stored procedure as follows:

</td>
<td class="crayon-code">
<div class="crayon-pre">
<div id="crayon-548161c73f08b442922262-1" class="crayon-line"><span class="crayon-e">CALL <span class="crayon-e">insert_article_tags<span class="crayon-sy">(<span class="crayon-cn">1<span class="crayon-sy">,<span class="crayon-cn">1<span class="crayon-sy">)<span class="crayon-sy">;
<div id="crayon-548161c73f08b442922262-2" class="crayon-line crayon-striped-line"><span class="crayon-e">CALL <span class="crayon-e">insert_article_tags<span class="crayon-sy">(<span class="crayon-cn">1<span class="crayon-sy">,<span class="crayon-cn">2<span class="crayon-sy">)<span class="crayon-sy">;
<div id="crayon-548161c73f08b442922262-3" class="crayon-line"><span class="crayon-e">CALL <span class="crayon-e">insert_article_tags<span class="crayon-sy">(<span class="crayon-cn">1<span class="crayon-sy">,<span class="crayon-cn">3<span class="crayon-sy">)<span class="crayon-sy">;

</td>

</tr></table>

Fourth,let’s try to insert a duplicate key to see if the handler is really invoked.

</td>
<td class="crayon-code">
<div class="crayon-pre">
<div id="crayon-548161c73f092181838051-1" class="crayon-line"><span class="crayon-e">CALL <span class="crayon-e">insert_article_tags<span class="crayon-sy">(<span class="crayon-cn">1<span class="crayon-sy">,<span class="crayon-cn">3<span class="crayon-sy">)<span class="crayon-sy">;

</td>

</tr></table>

We got an error message. However,because we declared the handler as a?CONTINUE?handler,the stored procedure continued execution. As the result,we got the tag count for the article as well.

MySQL Error Handling Example

If we change the?CONTINUE?in the handler declaration to?EXIT,we will get only the error message.

</td>
<td class="crayon-code">
<div class="crayon-pre">
<div id="crayon-548161c73f099789943661-1" class="crayon-line"><span class="crayon-i">DELIMITER<span class="crayon-h"> <span class="crayon-sy">$<span class="crayon-sy">$
<div id="crayon-548161c73f099789943661-2" class="crayon-line crayon-striped-line">?
<div id="crayon-548161c73f099789943661-3" class="crayon-line"><span class="crayon-e">CREATE <span class="crayon-e">PROCEDURE <span class="crayon-e">insert_article_tags_2<span class="crayon-sy">(<span class="crayon-st">IN<span class="crayon-h"> <span class="crayon-e">article_id <span class="crayon-t">INT<span class="crayon-sy">,<span class="crayon-h"> <span class="crayon-st">IN<span class="crayon-h"> <span class="crayon-e">tag_id <span class="crayon-t">INT<span class="crayon-sy">)
<div id="crayon-548161c73f099789943661-4" class="crayon-line crayon-striped-line"><span class="crayon-e">BEGIN
<div id="crayon-548161c73f099789943661-5" class="crayon-line">?
<div id="crayon-548161c73f099789943661-6" class="crayon-line crayon-striped-line"><span class="crayon-e"> <span class="crayon-r">DECLARE<span class="crayon-h"> <span class="crayon-e">EXIT <span class="crayon-e">HANDLER <span class="crayon-st">FOR<span class="crayon-h"> <span class="crayon-e">SQLEXCEPTION
<div id="crayon-548161c73f099789943661-7" class="crayon-line"><span class="crayon-e"> <span class="crayon-i">SELECT<span class="crayon-h"> <span class="crayon-s">'SQLException invoked'<span class="crayon-sy">;
<div id="crayon-548161c73f099789943661-8" class="crayon-line crayon-striped-line">?
<div id="crayon-548161c73f099789943661-9" class="crayon-line"><span class="crayon-h"> <span class="crayon-r">DECLARE<span class="crayon-h"> <span class="crayon-e">EXIT <span class="crayon-e">HANDLER <span class="crayon-st">FOR<span class="crayon-h"> <span class="crayon-cn">1062
<div id="crayon-548161c73f099789943661-10" class="crayon-line crayon-striped-line"><span class="crayon-h">????????<span class="crayon-i">SELECT<span class="crayon-h"> <span class="crayon-s">'MySQL error code 1062 invoked'<span class="crayon-sy">;
<div id="crayon-548161c73f099789943661-11" class="crayon-line">?
<div id="crayon-548161c73f099789943661-12" class="crayon-line crayon-striped-line"><span class="crayon-h"> <span class="crayon-r">DECLARE<span class="crayon-h"> <span class="crayon-e">EXIT <span class="crayon-e">HANDLER <span class="crayon-st">FOR<span class="crayon-h"> <span class="crayon-i">SQLSTATE<span class="crayon-h"> <span class="crayon-s">'23000'
<div id="crayon-548161c73f099789943661-13" class="crayon-line"><span class="crayon-h"> <span class="crayon-i">SELECT<span class="crayon-h"> <span class="crayon-s">'SQLSTATE 23000 invoked'<span class="crayon-sy">;
<div id="crayon-548161c73f099789943661-14" class="crayon-line crayon-striped-line">?
<div id="crayon-548161c73f099789943661-15" class="crayon-line"><span class="crayon-h"> <span class="crayon-o">--<span class="crayon-h"> <span class="crayon-i">insert<span class="crayon-h"> <span class="crayon-i">a<span class="crayon-h"> <span class="crayon-r">new<span class="crayon-h"> <span class="crayon-e">record <span class="crayon-e">into <span class="crayon-e">article_tags
<div id="crayon-548161c73f099789943661-16" class="crayon-line crayon-striped-line"><span class="crayon-e"> <span class="crayon-e">INSERT <span class="crayon-e">INTO <span class="crayon-e">article_tags<span class="crayon-sy">(<span class="crayon-v">article_id<span class="crayon-sy">,<span class="crayon-v">tag_id<span class="crayon-sy">)
<div id="crayon-548161c73f099789943661-17" class="crayon-line"><span class="crayon-h">?? <span class="crayon-e">VALUES<span class="crayon-sy">(<span class="crayon-v">article_id<span class="crayon-sy">,<span class="crayon-v">tag_id<span class="crayon-sy">)<span class="crayon-sy">;
<div id="crayon-548161c73f099789943661-18" class="crayon-line crayon-striped-line">?
<div id="crayon-548161c73f099789943661-19" class="crayon-line"><span class="crayon-h"> <span class="crayon-o">--<span class="crayon-h"> <span class="crayon-st">return<span class="crayon-h"> <span class="crayon-e">tag <span class="crayon-e">count <span class="crayon-st">for<span class="crayon-h"> <span class="crayon-e">the <span class="crayon-e">article
<div id="crayon-548161c73f099789943661-20" class="crayon-line crayon-striped-line"><span class="crayon-e"> <span class="crayon-e">SELECT <span class="crayon-e">COUNT<span class="crayon-sy">(<span class="crayon-o">*<span class="crayon-sy">)<span class="crayon-h"> <span class="crayon-e">FROM <span class="crayon-v">article_tags<span class="crayon-sy">;
<div id="crayon-548161c73f099789943661-21" class="crayon-line"><span class="crayon-st">END

</td>

</tr></table>

Now,we can try to add a duplicate key to see the effect.

</td>
<td class="crayon-code">
<div class="crayon-pre">
<div id="crayon-548161c73f0a0991411073-1" class="crayon-line"><span class="crayon-e">CALL <span class="crayon-e">insert_article_tags_2<span class="crayon-sy">(<span class="crayon-cn">1<span class="crayon-sy">,<span class="crayon-cn">3<span class="crayon-sy">)<span class="crayon-sy">;

</td>

</tr></table>

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.

An error always maps to one MySQL error code so a MySQL it is the most specific. An?SQLSTATE?may map to many MySQL error codes therefore it is less specific. An?SQLEXCPETION?or an?SQLWARNINGis 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:

</td>
<td class="crayon-code">
<div class="crayon-pre">
<div id="crayon-548161c73f0a7375377059-1" class="crayon-line"><span class="crayon-i">DELIMITER<span class="crayon-h"> <span class="crayon-sy">$<span class="crayon-sy">$
<div id="crayon-548161c73f0a7375377059-2" class="crayon-line crayon-striped-line">?
<div id="crayon-548161c73f0a7375377059-3" class="crayon-line"><span class="crayon-e">CREATE <span class="crayon-e">PROCEDURE <span class="crayon-e">insert_article_tags_3<span class="crayon-sy">(<span class="crayon-st">IN<span class="crayon-h"> <span class="crayon-e">article_id <span class="crayon-t">INT<span class="crayon-sy">,<span class="crayon-h"> <span class="crayon-st">IN<span class="crayon-h"> <span class="crayon-e">tag_id <span class="crayon-t">INT<span class="crayon-sy">)
<div id="crayon-548161c73f0a7375377059-4" class="crayon-line crayon-striped-line"><span class="crayon-e">BEGIN
<div id="crayon-548161c73f0a7375377059-5" class="crayon-line">?
<div id="crayon-548161c73f0a7375377059-6" class="crayon-line crayon-striped-line"><span class="crayon-e"> <span class="crayon-r">DECLARE<span class="crayon-h"> <span class="crayon-e">EXIT <span class="crayon-e">HANDLER <span class="crayon-st">FOR<span class="crayon-h"> <span class="crayon-cn">1062<span class="crayon-h"> <span class="crayon-i">SELECT<span class="crayon-h"> <span class="crayon-s">'Duplicate keys error encountered'<span class="crayon-sy">;
<div id="crayon-548161c73f0a7375377059-7" class="crayon-line"><span class="crayon-h"> <span class="crayon-r">DECLARE<span class="crayon-h"> <span class="crayon-e">EXIT <span class="crayon-e">HANDLER <span class="crayon-st">FOR<span class="crayon-h"> <span class="crayon-e">SQLEXCEPTION <span class="crayon-i">SELECT<span class="crayon-h"> <span class="crayon-s">'SQLException encountered'<span class="crayon-sy">;
<div id="crayon-548161c73f0a7375377059-8" class="crayon-line crayon-striped-line"><span class="crayon-h"> <span class="crayon-r">DECLARE<span class="crayon-h"> <span class="crayon-e">EXIT <span class="crayon-e">HANDLER <span class="crayon-st">FOR<span class="crayon-h"> <span class="crayon-i">SQLSTATE<span class="crayon-h"> <span class="crayon-s">'23000'<span class="crayon-h"> <span class="crayon-i">SELECT<span class="crayon-h"> <span class="crayon-s">'SQLSTATE 23000'<span class="crayon-sy">;
<div id="crayon-548161c73f0a7375377059-9" class="crayon-line">?
<div id="crayon-548161c73f0a7375377059-10" class="crayon-line crayon-striped-line"><span class="crayon-h"> <span class="crayon-o">--<span class="crayon-h"> <span class="crayon-i">insert<span class="crayon-h"> <span class="crayon-i">a<span class="crayon-h"> <span class="crayon-r">new<span class="crayon-h"> <span class="crayon-e">record <span class="crayon-e">into <span class="crayon-e">article_tags
<div id="crayon-548161c73f0a7375377059-11" class="crayon-line"><span class="crayon-e"> <span class="crayon-e">INSERT <span class="crayon-e">INTO <span class="crayon-e">article_tags<span class="crayon-sy">(<span class="crayon-v">article_id<span class="crayon-sy">,<span class="crayon-v">tag_id<span class="crayon-sy">)
<div id="crayon-548161c73f0a7375377059-12" class="crayon-line crayon-striped-line"><span class="crayon-h"> <span class="crayon-e">VALUES<span class="crayon-sy">(<span class="crayon-v">article_id<span class="crayon-sy">,<span class="crayon-v">tag_id<span class="crayon-sy">)<span class="crayon-sy">;
<div id="crayon-548161c73f0a7375377059-13" class="crayon-line">?
<div id="crayon-548161c73f0a7375377059-14" class="crayon-line crayon-striped-line"><span class="crayon-h"> <span class="crayon-o">--<span class="crayon-h"> <span class="crayon-st">return<span class="crayon-h"> <span class="crayon-e">tag <span class="crayon-e">count <span class="crayon-st">for<span class="crayon-h"> <span class="crayon-e">the <span class="crayon-e">article
<div id="crayon-548161c73f0a7375377059-15" class="crayon-line"><span class="crayon-e"> <span class="crayon-e">SELECT <span class="crayon-e">COUNT<span class="crayon-sy">(<span class="crayon-o">*<span class="crayon-sy">)<span class="crayon-h"> <span class="crayon-e">FROM <span class="crayon-v">article_tags<span class="crayon-sy">;
<div id="crayon-548161c73f0a7375377059-16" class="crayon-line crayon-striped-line"><span class="crayon-st">END

</td>

</tr></table>

We now try to insert a duplicate key into the?article_tags?table by calling the stored procedure:

</td>
<td class="crayon-code">
<div class="crayon-pre">
<div id="crayon-548161c73f0ae701835812-1" class="crayon-line"><span class="crayon-e">CALL <span class="crayon-e">insert_article_tags_3<span class="crayon-sy">(<span class="crayon-cn">1<span class="crayon-sy">,<span class="crayon-cn">3<span class="crayon-sy">)<span class="crayon-sy">;

</td>

</tr></table>

As you see the MySQL error code handler is called.

MySQL handler precedence

Using named error condition

Let’s start with an error handler declaration.

</td>
<td class="crayon-code">
<div class="crayon-pre">
<div id="crayon-548161c73f0b8222448350-1" class="crayon-line"><span class="crayon-r">DECLARE<span class="crayon-h"> <span class="crayon-e">EXIT <span class="crayon-e">HANDLER <span class="crayon-st">FOR<span class="crayon-h"> <span class="crayon-cn">1051<span class="crayon-h"> <span class="crayon-i">SELECT<span class="crayon-h"> <span class="crayon-s">'Please create table abc first'<span class="crayon-sy">;
<div id="crayon-548161c73f0b8222448350-2" class="crayon-line crayon-striped-line"><span class="crayon-e ">SELECT *<span class="crayon-h"> <span class="crayon-e">FROM <span class="crayon-v">abc<span class="crayon-sy">;

</td>

</tr></table>

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 for the maintenance developers.

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:

</td>
<td class="crayon-code">
<div class="crayon-pre">
<div id="crayon-548161c73f0bf804134254-1" class="crayon-line"><span class="crayon-r">DECLARE<span class="crayon-h"> <span class="crayon-e">condition_name <span class="crayon-e">CONDITION <span class="crayon-st">FOR<span class="crayon-h"> <span class="crayon-v">condition_value<span class="crayon-sy">;

</td>

</tr></table>

The??condition_value?can be a MySQL error code such as 1015 or a?SQLSTATE?value. The?condition_value?is represented by the?condition_name.

After declaration,you can refer to the??condition_name?instead of the?condition_value.

So we can rewrite the code above as follows:

</td>
<td class="crayon-code">
<div class="crayon-pre">
<div id="crayon-548161c73f0c5332288803-1" class="crayon-line"><span class="crayon-r">DECLARE<span class="crayon-h"> <span class="crayon-e">table_not_found <span class="crayon-e">CONDITION <span class="crayon-st">for<span class="crayon-h"> <span class="crayon-cn">1051<span class="crayon-sy">;
<div id="crayon-548161c73f0c5332288803-2" class="crayon-line crayon-striped-line"><span class="crayon-r">DECLARE<span class="crayon-h"> <span class="crayon-e">EXIT <span class="crayon-e">HANDLER <span class="crayon-st">FOR<span class="crayon-h">??<span class="crayon-e">table_not_found <span class="crayon-i">SELECT<span class="crayon-h"> <span class="crayon-s">'Please create table abc first'<span class="crayon-sy">;
<div id="crayon-548161c73f0c5332288803-3" class="crayon-line"><span class="crayon-e ">SELECT *<span class="crayon-h"> <span class="crayon-e">FROM <span class="crayon-v">abc<span class="crayon-sy">;

</td>

</tr></table>

This code is obviously more readable than the previous one.

Notice that the condition declaration must appear before handler or cursor declarations.

原文：http://www.mysqltutorial.org/mysql-error-handling-in-stored-procedures/

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!

This tutorial shows you how to use MySQL handler to handle exceptions or errors encountered in stored procedures.

When an error occurs inside a stored procedure,it is important to handle it appropriately,such as continuing or exiting the current code block’s execution,and issuing a meaningful error message.

MySQL provides an easy way to define handlers that handle from general conditions such as warnings or exceptions to specific conditions e.g.,specific error codes.

Declaring a handler

To declare a handler,you use the??DECLARE HANDLER?statement as follows:

If a condition whose value matches the??condition_value,MySQL will execute the?statement?and continue or exit the current code block based on the?action.

The?action?accepts one of the following values:

CONTINUE:? the execution of the enclosing code block (?BEGIN?…?END) continues.
EXIT: the execution of the enclosing code block,where the handler is declared,terminates.

The??condition_value?specifies a particular condition or a class of conditions that activates the handler. The??condition_value?accepts one of the following values:

A MySQL error code.
A standard?SQLSTATE?value. Or it can be an?SQLWARNING,?NOTFOUND?or?SQLEXCEPTIONcondition,which is shorthand for the class of?SQLSTATE?values. The?NOTFOUND?condition is used for a?or??SELECT INTO variable_list?statement.
A named condition associated with either a MySQL error code or?SQLSTATE?value.

The?statement?could be a simple statement or a compound statement enclosing by the?BEGIN?andEND?keywords.

MySQL error handling examples

Let’s look into several examples of declaring handlers.

The following handler means if an error occurs,set the value of the??has_error?variable to 1 and continue the execution.

</td>
<td class="crayon-code">
<div class="crayon-pre">
<div id="crayon-548161c73f05d207284107-1" class="crayon-line"><span class="crayon-r">DECLARE<span class="crayon-h"> <span class="crayon-st">CONTINUE<span class="crayon-h"> <span class="crayon-e">HANDLER <span class="crayon-st">FOR<span class="crayon-h"> <span class="crayon-e">SQLEXCEPTION <span class="crayon-e">SET <span class="crayon-v">has_error<span class="crayon-h"> <span class="crayon-o">=<span class="crayon-h"> <span class="crayon-cn">1<span class="crayon-sy">;

</td>

</tr>