Shulou(Shulou.com)06/01 Report--

This paper gives an example of error handling of mysql stored procedures. Share with you for your reference, the details are as follows:

When an error occurs in a stored procedure, it is important to handle it appropriately, for example, to continue or exit the execution of the current code block and to issue a meaningful error message. Where mysql provides a simple way to define handlers that handle everything from general conditions (such as warnings or exceptions) to specific conditions (such as specific error codes). When we're done, let's use the DECLARE HANDLER statement to try to declare a handler. Let's look at the syntax first:

DECLARE action HANDLER FOR condition_value statement

In the above sql, if the value of the condition matches the condition_value, the MySQL executes the statement and continues or exits the current block of code based on that operation. Where the action accepts one of the following values:

CONTINUE: continue to execute closed code blocks (BEGIN... END). EXIT: the handler declares the termination of the execution of the block of closed code.

Condition_value specifies a specific condition or a class of conditions that activate a handler. Condition_value accepts one of the following values:

A MySQL error code. The standard SQLSTATE state value or it can be a SQLSTATE or SQLSTATE condition, which is short for SQLSTATE value class. The NOTFOUND condition is used for cursors or SELECT INTO variable_list statements. The naming condition associated with the MySQL error code or SQLSTATE value.

Most importantly, the above sql can be a simple statement or a compound statement surrounded by the BEGIN and END keywords. After the introduction, let's look at several examples of declaration handlers. The first is an example of setting the value of the has_error variable to 1 and continuing execution when an error occurs in the program:

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET has_error = 1

Again, when an error occurs, roll back the previous operation, issue an error message, and exit the current code block. If you declare it in the BEGIN END block of a stored procedure, the stored procedure is terminated immediately:

DECLARE EXIT HANDLER FOR SQLEXCEPTIONBEGINROLLBACK;SELECT'An error has occurred, operation rollbacked and the stored procedure was terminated';END

The following handler means that if there are no more rows to extract, set the value of the no_row_found variable to 1 and continue execution with the cursor or select into statement:

DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_row_found = 1

The following handler issues MySQL error 1062 if a duplicate key error occurs. It sends an error message and continues to execute:

DECLARE CONTINUE HANDLER FOR 1062SELECT 'Error, duplicate key occurred'

The above examples may be a little abstract. Let's not talk too much about it. Let's create a new table called article_tags to do some concrete work:

USE testdb;CREATE TABLE article_tags (article_id INT, tag_id INT, PRIMARY KEY (article_id,tag_id))

The article_tags table stores the relationship between articles and tags. Each article may have many tags, and vice versa. For simplicity, we will not create article tables and tags tables and foreign keys in the article_ tags table.

When we're done, let's create a stored procedure that inserts the id of the article and the id of the tag into the article_tags table:

USE testdb;DELIMITER $$CREATE PROCEDURE insert_article_tags (IN article_id INT, IN tag_id INT) BEGIN DECLARE CONTINUE HANDLER FOR 1062 SELECT CONCAT ('duplicate keys (', article_id,',',tag_id,') found') AS msg;-insert a new record into article_tags INSERT INTO article_tags (article_id,tag_id) VALUES (article_id,tag_id);-- return tag count for the article SELECT COUNT (*) FROM article_tags;END$$DELIMITER

Then, by calling the insert_article_tags stored procedure, we add the tags ID:1,2 and 3 for article ID to 1, as shown below:

CALL insert_article_tags (1 ~ (1)); CALL insert_article_tags ((1 ~ 1)); CALL insert_article_tags (1 ~ (1))

Let's try again to insert a duplicate key to check whether the handler is actually called:

CALL insert_article_tags (1pr 3)

Execute the above query statement and get the following results:

Mysql > CALL insert_article_tags (1pr 3) +-- + | msg | +-+ | duplicate keys (1 row in set+-+ 3) found | +-- + 1 row in set+-+ | COUNT (*) | | +-+ | 3 | +-+ 1 row in setQuery OK | 0 rows affected

You will receive an error message after execution. However, because we declare the handler as a CONTINUE handler, the stored procedure continues to execute. Therefore, finally, the tag count value of the article is: 3. Take a look at a picture:

But if you change the CONTINUE in the handler declaration to EXIT, you will receive only one error message. The query statement is as follows:

DELIMITER $$CREATE PROCEDURE insert_article_tags_exit (IN article_id INT, IN tag_id INT) BEGIN DECLARE EXIT HANDLER FOR SQLEXCEPTION SELECT 'SQLException invoked'; DECLARE EXIT HANDLER FOR 1062 SELECT' MySQL error code 1062 invoked'; DECLARE EXIT HANDLER FOR SQLSTATE '23000' SELECT' SQLSTATE 23000 invoked';-insert a new record into article_tags INSERT INTO article_tags (article_id,tag_id) VALUES (article_id,tag_id);-- return tag count for the article SELECT COUNT (*) FROM article_tags;END $$DELIMITER

Execute the above query statement and get the following results:

Mysql > CALL insert_article_tags_exit (1 row in setQuery OK 3); +-- + | MySQL error code 1062 invoked | +-+ | MySQL error code 1062 invoked | +-- + 1 row in setQuery OK, 0 rows affected

Take a look at the picture and feel it:

If we use multiple handlers to handle the error, MySQL will call the most specific handler to handle the error. This involves the question of priority, let's take a look at it in detail.

We know that errors always map to a MySQL error code because it is the most specific in MySQL. SQLSTATE can map to many MySQL error codes, so it's not very specific. SQLEXCPETION or SQLWARNING is an abbreviation for the value of the SQLSTATES type, so it is the most generic. Suppose you declare three handlers in an insert_article_tags_3 stored procedure, as follows:

DELIMITER $$CREATE PROCEDURE insert_article_tags_3 (IN article_id INT, IN tag_id INT) BEGIN DECLARE EXIT HANDLER FOR 1062 SELECT 'Duplicate keys error encountered'; DECLARE EXIT HANDLER FOR SQLEXCEPTION SELECT' SQLException encountered'; DECLARE EXIT HANDLER FOR SQLSTATE '23000' SELECT' SQLSTATE 23000mm;-- insert a new record into article_tags INSERT INTO article_tags (article_id,tag_id) VALUES (article_id,tag_id);-- return tag count for the article SELECT COUNT (*) FROM article_tags;END $$DELIMITER

Then we try to insert the duplicate key into the article_tags table by calling the stored procedure:

CALL insert_article_tags_3 (1pr 3)

As follows, you can see that the MySQL error code handler is called:

Mysql > CALL insert_article_tags_3 (1pr 3) +-- + | Duplicate keys error encountered | +-- + | Duplicate keys error encountered | +-+ 1 row in setQuery OK, 0 rows affected

When we're done, let's take a look at using naming error conditions. Start with the error handler declaration, as follows:

DECLARE EXIT HANDLER FOR 1051 SELECT 'Please create table abc first';SELECT * FROM abc

What does the 1051 mean? Imagine that you have a large stored procedure code that uses a lot of numbers like this; this will be a nightmare for maintaining the code. Fortunately, MySQL provides us with a DECLARE CONDITION statement that declares a naming error condition, which is associated with the condition. The syntax of the DECLARE CONDITION statement is as follows:

DECLARE condition_name CONDITION FOR condition_value

Condition_value can be an MySQL error code, such as 1015 or SQLSTATE value. Condition_value is represented by condition_name. After the declaration, you can refer to condition_name instead of condition_value. So you can rewrite the above code as follows:

DECLARE table_not_found CONDITION for 1051: declare EXIT HANDLER FOR table_not_found SELECT 'Please create table abc first';SELECT * FROM abc

This code is obviously more readable than previous code, but we should note that the condition declaration must appear before the handler or cursor declaration.

All right, that's it this time.

More readers who are interested in MySQL-related content can check out this site topic: "MySQL stored procedure skills Collection", "MySQL Common function Summary", "MySQL Log Operation skills Collection", "MySQL transaction Operation skills Summary" and "MySQL Database Lock related skills Summary".

It is hoped that what is described in this article will be helpful to everyone's MySQL database design.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.