MySQL Error Handling in Stored Procedures 2

2018-06-17 22:59:37来源:未知 阅读 ()

新老客户大回馈,云服务器低至5折

 

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:

If a condition whose value matches the  condition_value , MySQL will execute the statementand 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 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 BEGINand 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.

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.

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.

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

 

MySQL handler example in stored procedures

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

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 tagstables, 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:

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

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

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.

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

 

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. AnSQLSTATE may map to many MySQL error codes therefore it is less specific. An SQLEXCPETIONor 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:

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

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.

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:

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:

This code is obviously more readable than the previous one.

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

标签:

版权申明:本站文章部分自网络,如有侵权,请联系:west999com@outlook.com
特别注意:本站所有转载文章言论不代表本站观点,本站所提供的摄影照片,插画,设计作品,如需使用,请与原作者联系,版权归原作者所有

上一篇:MySQL: @variable vs. variable. Whats the difference?

下一篇:Mysql存储过程(四)——异常处理