MySQL Error Handling in Stored Procedures 2

旧城等待， 2021-12-17 15:04 157阅读 0赞

Summary: 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:

<table> 
 <tbody> 
  <tr> 
   <td> 
    <div> 
     <div>
       1 
     </div> 
    </div> </td> 
   <td> 
    <div> 
     <div> 
      <span>DECLARE<span> <span>action <span>HANDLER <span>FOR<span> <span>condition_value <span>statement<span>;</span></span></span></span></span></span></span></span></span> 
     </div> 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

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 `SQLEXCEPTION`condition, which is shorthand for the class of `SQLSTATE` values. The `NOTFOUND` condition is used for a [cursor ][cursor]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`and `END` keywords.

## MySQL error handling examples ##

Let’s look into several examples of declaring handlers.

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

<table> 
 <tbody> 
  <tr> 
   <td> 
    <div> 
     <div>
       1 
     </div> 
    </div> </td> 
   <td> 
    <div> 
     <div> 
      <span>DECLARE<span> <span>CONTINUE<span> <span>HANDLER <span>FOR<span> <span>SQLEXCEPTION <span>SET <span>has_error<span> <span>=<span> <span>1<span>;</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span> 
     </div> 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

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 the`BEGIN END` block of a stored procedure, it will terminate stored procedure immediately.

<table> 
 <tbody> 
  <tr> 
   <td> 
    <div> 
     <div>
       1 
     </div> 
     <div>
       2 
     </div> 
     <div>
       3 
     </div> 
     <div>
       4 
     </div> 
     <div>
       5 
     </div> 
    </div> </td> 
   <td> 
    <div> 
     <div> 
      <span>DECLARE<span> <span>EXIT <span>HANDLER <span>FOR<span> <span>SQLEXCEPTION</span></span></span></span></span></span></span> 
     </div> 
     <div> 
      <span>BEGIN</span> 
     </div> 
     <div> 
      <span>ROLLBACK<span>;</span></span> 
     </div> 
     <div> 
      <span>SELECT<span> <span>'An error has occurred, operation rollbacked and the stored procedure was terminated'<span>;</span></span></span></span> 
     </div> 
     <div> 
      <span>END<span>;</span></span> 
     </div> 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

The following handler means that if there are no more rows to fetch, in case of a [cursor ][cursor]or [SELECT INTO ][SELECT INTO]statement, set the value of the  `no_row_found` variable to 1 and continue execution.

<table> 
 <tbody> 
  <tr> 
   <td> 
    <div> 
     <div>
       1 
     </div> 
    </div> </td> 
   <td> 
    <div> 
     <div> 
      <span>DECLARE<span> <span>CONTINUE<span> <span>HANDLER <span>FOR<span> <span>NOT<span> <span>FOUND <span>SET <span>no_row_found<span> <span>=<span> <span>1<span>;</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span> 
     </div> 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

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

<table> 
 <tbody> 
  <tr> 
   <td> 
    <div> 
     <div>
       1 
     </div> 
     <div>
       2 
     </div> 
    </div> </td> 
   <td> 
    <div> 
     <div> 
      <span>DECLARE<span> <span>CONTINUE<span> <span>HANDLER <span>FOR<span> <span>1062</span></span></span></span></span></span></span></span> 
     </div> 
     <div> 
      <span>SELECT<span> <span>'Error, duplicate key occurred'<span>;</span></span></span></span> 
     </div> 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

## MySQL handler example in stored procedures ##

First, we [create a new table][] named  `article_tags` for the demonstration:

<table> 
 <tbody> 
  <tr> 
   <td> 
    <div> 
     <div>
       1 
     </div> 
     <div>
       2 
     </div> 
     <div>
       3 
     </div> 
     <div>
       4 
     </div> 
     <div>
       5 
     </div> 
    </div> </td> 
   <td> 
    <div> 
     <div> 
      <span>CREATE <span>TABLE <span>article_tags<span>(</span></span></span></span> 
     </div> 
     <div> 
      <span>&nbsp;&nbsp;&nbsp;&nbsp;<span>article_id <span>INT<span>,</span></span></span></span> 
     </div> 
     <div> 
      <span>&nbsp;&nbsp;&nbsp;&nbsp;<span>tag_id&nbsp;&nbsp;&nbsp;&nbsp; <span>INT<span>,</span></span></span></span> 
     </div> 
     <div> 
      <span>&nbsp;&nbsp;&nbsp;&nbsp;<span>PRIMARY <span>KEY<span>(<span>article_id<span>,<span>tag_id<span>)</span></span></span></span></span></span></span></span> 
     </div> 
     <div> 
      <span>)<span>;</span></span> 
     </div> 
    </div> </td> 
  </tr> 
 </tbody> 
</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 [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:

<table> 
 <tbody> 
  <tr> 
   <td> 
    <div> 
     <div>
       1 
     </div> 
     <div>
       2 
     </div> 
     <div>
       3 
     </div> 
     <div>
       4 
     </div> 
     <div>
       5 
     </div> 
     <div>
       6 
     </div> 
     <div>
       7 
     </div> 
     <div>
       8 
     </div> 
     <div>
       9 
     </div> 
     <div>
       10 
     </div> 
     <div>
       11 
     </div> 
     <div>
       12 
     </div> 
     <div>
       13 
     </div> 
     <div>
       14 
     </div> 
     <div>
       15 
     </div> 
    </div> </td> 
   <td> 
    <div> 
     <div> 
      <span>DELIMITER<span> <span>$<span>$</span></span></span></span> 
     </div> 
     <div>
       &nbsp; 
     </div> 
     <div> 
      <span>CREATE <span>PROCEDURE <span>insert_article_tags<span>(<span>IN<span> <span>article_id <span>INT<span>,<span> <span>IN<span> <span>tag_id <span>INT<span>)</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span> 
     </div> 
     <div> 
      <span>BEGIN</span> 
     </div> 
     <div>
       &nbsp; 
     </div> 
     <div> 
      <span> <span>DECLARE<span> <span>CONTINUE<span> <span>HANDLER <span>FOR<span> <span>1062</span></span></span></span></span></span></span></span></span> 
     </div> 
     <div> 
      <span> <span>SELECT <span>CONCAT<span>(<span>'duplicate keys ('<span>,<span>article_id<span>,<span>','<span>,<span>tag_id<span>,<span>') found'<span>)<span> <span>AS<span> <span>msg<span>;</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span> 
     </div> 
     <div>
       &nbsp; 
     </div> 
     <div> 
      <span> <span>--<span> <span>insert<span> <span>a<span> <span>new<span> <span>record <span>into <span>article_tags</span></span></span></span></span></span></span></span></span></span></span></span> 
     </div> 
     <div> 
      <span> <span>INSERT <span>INTO <span>article_tags<span>(<span>article_id<span>,<span>tag_id<span>)</span></span></span></span></span></span></span></span></span> 
     </div> 
     <div> 
      <span> <span>VALUES<span>(<span>article_id<span>,<span>tag_id<span>)<span>;</span></span></span></span></span></span></span></span> 
     </div> 
     <div>
       &nbsp; 
     </div> 
     <div> 
      <span> <span>--<span> <span>return<span> <span>tag <span>count <span>for<span> <span>the <span>article</span></span></span></span></span></span></span></span></span></span></span> 
     </div> 
     <div> 
      <span> <span>SELECT <span>COUNT<span>(<span>*<span>)<span> <span>FROM <span>article_tags<span>;</span></span></span></span></span></span></span></span></span></span> 
     </div> 
     <div> 
      <span>END</span> 
     </div> 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

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

<table> 
 <tbody> 
  <tr> 
   <td> 
    <div> 
     <div>
       1 
     </div> 
     <div>
       2 
     </div> 
     <div>
       3 
     </div> 
    </div> </td> 
   <td> 
    <div> 
     <div> 
      <span>CALL <span>insert_article_tags<span>(<span>1<span>,<span>1<span>)<span>;</span></span></span></span></span></span></span></span> 
     </div> 
     <div> 
      <span>CALL <span>insert_article_tags<span>(<span>1<span>,<span>2<span>)<span>;</span></span></span></span></span></span></span></span> 
     </div> 
     <div> 
      <span>CALL <span>insert_article_tags<span>(<span>1<span>,<span>3<span>)<span>;</span></span></span></span></span></span></span></span> 
     </div> 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

After that, we try to insert a duplicate key to check if the handler is really invoked.

<table> 
 <tbody> 
  <tr> 
   <td> 
    <div> 
     <div>
       1 
     </div> 
    </div> </td> 
   <td> 
    <div> 
     <div> 
      <span>CALL <span>insert_article_tags<span>(<span>1<span>,<span>3<span>)<span>;</span></span></span></span></span></span></span></span> 
     </div> 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

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.

![MySQL Error Handling Example][]

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

<table> 
 <tbody> 
  <tr> 
   <td> 
    <div> 
     <div>
       1 
     </div> 
     <div>
       2 
     </div> 
     <div>
       3 
     </div> 
     <div>
       4 
     </div> 
     <div>
       5 
     </div> 
     <div>
       6 
     </div> 
     <div>
       7 
     </div> 
     <div>
       8 
     </div> 
     <div>
       9 
     </div> 
     <div>
       10 
     </div> 
     <div>
       11 
     </div> 
     <div>
       12 
     </div> 
     <div>
       13 
     </div> 
     <div>
       14 
     </div> 
     <div>
       15 
     </div> 
     <div>
       16 
     </div> 
     <div>
       17 
     </div> 
     <div>
       18 
     </div> 
     <div>
       19 
     </div> 
     <div>
       20 
     </div> 
     <div>
       21 
     </div> 
    </div> </td> 
   <td> 
    <div> 
     <div> 
      <span>DELIMITER<span> $$</span></span> 
     </div> 
     <div>
       &nbsp; 
     </div> 
     <div> 
      <span>CREATE<span> <span>PROCEDURE<span> insert_article_tags_2(<span>IN<span> article_id<span> <span>INT,<span> <span>IN<span> tag_id<span> <span>INT)</span></span></span></span></span></span></span></span></span></span></span></span></span> 
     </div> 
     <div> 
      <span>BEGIN</span> 
     </div> 
     <div>
       &nbsp; 
     </div> 
     <div> 
      <span> <span>DECLARE<span> <span>EXIT<span> <span>HANDLER<span> <span>FOR<span> <span>SQLEXCEPTION</span></span></span></span></span></span></span></span></span></span> 
     </div> 
     <div> 
      <span> <span>SELECT<span> <span>'SQLException invoked';</span></span></span></span> 
     </div> 
     <div>
       &nbsp; 
     </div> 
     <div> 
      <span> <span>DECLARE<span> <span>EXIT<span> <span>HANDLER<span> <span>FOR<span> 1062</span></span></span></span></span></span></span></span></span> 
     </div> 
     <div> 
      <span>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<span>SELECT<span> <span>'MySQL error code 1062 invoked';</span></span></span></span> 
     </div> 
     <div>
       &nbsp; 
     </div> 
     <div> 
      <span> <span>DECLARE<span> <span>EXIT<span> <span>HANDLER<span> <span>FOR<span> <span>SQLSTATE<span> <span>'23000'</span></span></span></span></span></span></span></span></span></span></span></span> 
     </div> 
     <div> 
      <span> <span>SELECT<span> <span>'SQLSTATE 23000 invoked';</span></span></span></span> 
     </div> 
     <div>
       &nbsp; 
     </div> 
     <div> 
      <span> <span>-- insert a new record into article_tags</span></span> 
     </div> 
     <div> 
      <span> <span>INSERT<span> <span>INTO<span> article_tags(article_id,tag_id)</span></span></span></span></span> 
     </div> 
     <div> 
      <span>&nbsp;&nbsp; <span>VALUES(article_id,tag_id);</span></span> 
     </div> 
     <div>
       &nbsp; 
     </div> 
     <div> 
      <span> <span>-- return tag count for the article</span></span> 
     </div> 
     <div> 
      <span> <span>SELECT COUNT(*)<span> <span>FROM<span> article_tags;</span></span></span></span></span> 
     </div> 
     <div> 
      <span>END</span> 
     </div> 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

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

<table> 
 <tbody> 
  <tr> 
   <td> 
    <div> 
     <div>
       1 
     </div> 
    </div> </td> 
   <td> 
    <div> 
     <div> 
      <span>CALL <span>insert_article_tags_2<span>(<span>1<span>,<span>3<span>)<span>;</span></span></span></span></span></span></span></span> 
     </div> 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

## ![MySQL error handling - duplicate keys][] ##

## 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. An`SQLSTATE` 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 and`SQLEXCEPTION` takes the first, second and third precedence.

Suppose we declare three handlers in the  `insert_article_tags_3` stored procedure as follows:

<table> 
 <tbody> 
  <tr> 
   <td> 
    <div> 
     <div>
       1 
     </div> 
     <div>
       2 
     </div> 
     <div>
       3 
     </div> 
     <div>
       4 
     </div> 
     <div>
       5 
     </div> 
     <div>
       6 
     </div> 
     <div>
       7 
     </div> 
     <div>
       8 
     </div> 
     <div>
       9 
     </div> 
     <div>
       10 
     </div> 
     <div>
       11 
     </div> 
     <div>
       12 
     </div> 
     <div>
       13 
     </div> 
     <div>
       14 
     </div> 
     <div>
       15 
     </div> 
     <div>
       16 
     </div> 
    </div> </td> 
   <td> 
    <div> 
     <div> 
      <span>DELIMITER<span> $$</span></span> 
     </div> 
     <div>
       &nbsp; 
     </div> 
     <div> 
      <span>CREATE<span> <span>PROCEDURE<span> insert_article_tags_3(<span>IN<span> article_id<span> <span>INT,<span> <span>IN<span> tag_id<span> <span>INT)</span></span></span></span></span></span></span></span></span></span></span></span></span> 
     </div> 
     <div> 
      <span>BEGIN</span> 
     </div> 
     <div>
       &nbsp; 
     </div> 
     <div> 
      <span> <span>DECLARE<span> <span>EXIT<span> <span>HANDLER<span> <span>FOR<span> 1062<span> <span>SELECT<span> <span>'Duplicate keys error encountered';</span></span></span></span></span></span></span></span></span></span></span></span></span> 
     </div> 
     <div> 
      <span> <span>DECLARE<span> <span>EXIT<span> <span>HANDLER<span> <span>FOR<span> <span>SQLEXCEPTION<span> <span>SELECT<span> <span>'SQLException encountered';</span></span></span></span></span></span></span></span></span></span></span></span></span></span> 
     </div> 
     <div> 
      <span> <span>DECLARE<span> <span>EXIT<span> <span>HANDLER<span> <span>FOR<span> <span>SQLSTATE<span> <span>'23000'<span> <span>SELECT<span> <span>'SQLSTATE 23000';</span></span></span></span></span></span></span></span></span></span></span></span></span></span></span></span> 
     </div> 
     <div>
       &nbsp; 
     </div> 
     <div> 
      <span> <span>-- insert a new record into article_tags</span></span> 
     </div> 
     <div> 
      <span> <span>INSERT<span> <span>INTO<span> article_tags(article_id,tag_id)</span></span></span></span></span> 
     </div> 
     <div> 
      <span> <span>VALUES(article_id,tag_id);</span></span> 
     </div> 
     <div>
       &nbsp; 
     </div> 
     <div> 
      <span> <span>-- return tag count for the article</span></span> 
     </div> 
     <div> 
      <span> <span>SELECT COUNT(*)<span> <span>FROM<span> article_tags;</span></span></span></span></span> 
     </div> 
     <div> 
      <span>END</span> 
     </div> 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

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

<table> 
 <tbody> 
  <tr> 
   <td> 
    <div> 
     <div>
       1 
     </div> 
    </div> </td> 
   <td> 
    <div> 
     <div> 
      <span>CALL<span> insert_article_tags_3(1,3);</span></span> 
     </div> 
    </div> </td> 
  </tr> 
 </tbody> 
</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.

<table> 
 <tbody> 
  <tr> 
   <td> 
    <div> 
     <div>
       1 
     </div> 
     <div>
       2 
     </div> 
    </div> </td> 
   <td> 
    <div> 
     <div> 
      <span>DECLARE<span> <span>EXIT<span> <span>HANDLER<span> <span>FOR<span> 1051<span> <span>SELECT<span> <span>'Please create table abc first';</span></span></span></span></span></span></span></span></span></span></span></span> 
     </div> 
     <div> 
      <span>SELECT<span> *<span> <span>FROM<span> abc;</span></span></span></span></span> 
     </div> 
    </div> </td> 
  </tr> 
 </tbody> 
</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 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:

<table> 
 <tbody> 
  <tr> 
   <td> 
    <div> 
     <div>
       1 
     </div> 
    </div> </td> 
   <td> 
    <div> 
     <div> 
      <span>DECLARE<span> condition_name<span> <span>CONDITION<span> <span>FOR<span> condition_value;</span></span></span></span></span></span></span> 
     </div> 
    </div> </td> 
  </tr> 
 </tbody> 
</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, we can refer to `condition_name`  instead of `condition_value` .

So we can rewrite the code above as follows:

<table> 
 <tbody> 
  <tr> 
   <td> 
    <div> 
     <div>
       1 
     </div> 
     <div>
       2 
     </div> 
     <div>
       3 
     </div> 
    </div> </td> 
   <td> 
    <div> 
     <div> 
      <span>DECLARE<span> table_not_found<span> <span>CONDITION<span> <span>for<span> 1051;</span></span></span></span></span></span></span> 
     </div> 
     <div> 
      <span>DECLARE<span> <span>EXIT<span> <span>HANDLER<span> <span>FOR<span>&nbsp;&nbsp;table_not_found<span> <span>SELECT<span> <span>'Please create table abc first';</span></span></span></span></span></span></span></span></span></span></span></span> 
     </div> 
     <div> 
      <span>SELECT<span> *<span> <span>FROM<span> abc;</span></span></span></span></span> 
     </div> 
    </div> </td> 
  </tr> 
 </tbody> 
</table>

This code is obviously more readable than the previous one.

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

转载于:https://www.cnblogs.com/kungfupanda/p/5855171.html

[cursor]: http://www.mysqltutorial.org/mysql-cursor/
[SELECT INTO]: http://www.mysqltutorial.org/mysql-select-statement-query-data.aspx
[create a new table]: http://www.mysqltutorial.org/mysql-create-table/
[foreign keys]: http://www.mysqltutorial.org/mysql-foreign-key/
[create a stored procedure]: http://www.mysqltutorial.org/getting-started-with-mysql-stored-procedures.aspx
[MySQL Error Handling Example]: http://www.mysqltutorial.org/wp-content/uploads/2014/02/mysql-error-handling.jpg
[MySQL error handling - duplicate keys]: http://www.mysqltutorial.org/wp-content/uploads/2014/02/mysql-error-handling-duplicate-keys.jpg
[MySQL handler precedence]: http://www.mysqltutorial.org/wp-content/uploads/2014/02/MySQL-handler-precedence.jpg