mysql存储过程中错误处理有哪几种类型_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 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 theDECLARE HANDLERstatement as follows:DECLAREactionHANDLERFORcondition_valuestatement;

If a condition whose value matches thecondition_value, MySQL will execute thestatementand continue or exit the current code block based on theaction.

Theactionaccepts 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.

Thecondition_valuespecifies a particular condition or a class of conditions that activates the handler. Thecondition_valueaccepts one of the following values:A MySQL error code.

A standardSQLSTATEvalue. Or it can be anSQLWARNING,NOTFOUNDorSQLEXCEPTIONcondition, which is shorthand for the class ofSQLSTATEvalues. TheNOTFOUNDcondition is used for acursororSELECT INTO variable_liststatement.

A named condition associated with either a MySQL error code orSQLSTATEvalue.

Thestatementcould be a simple statement or a compound statement enclosing by theBEGINandENDkeywords.

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 thehas_errorvariable to 1 and continue the execution.DECLARECONTINUEHANDLERFORSQLEXCEPTIONSEThas_error=1;

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 theBEGIN ENDblock of a stored procedure, it will terminate stored procedure immediately.DECLAREEXITHANDLERFORSQLEXCEPTIONBEGINROLLBACK;SELECT'Anerrorhasoccurred,operationrollbackedandthestoredprocedurewasterminated';END;

If there are no more rows to fetch, in case of acursororSELECT INTOstatement, set the value of theno_row_foundvariable to 1 and continue execution.DECLARECONTINUEHANDLERFORNOTFOUNDSETno_row_found=1;

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

MySQL handler example in stored procedures

First, we create a new table namedarticle_tagsfor the demonstration:CREATETABLEarticle_tags(article_idINT,tag_idINT,PRIMARYKEY(article_id,tag_id));

Thearticle_tagstable stores the relationships between articles and tags. Each article may have many tags and vice versa. For the sake of simplicity, we don’t createarticlesandtagstables, as well as theforeign keysin thearticle_tagstable.

Second, we create a stored procedure that inserts a pair of ids of article and tag into thearticle_tagstable:DELIMITER$$CREATEPROCEDUREinsert_article_tags(INarticle_idINT,INtag_idINT)BEGINDECLARECONTINUEHANDLERFOR1062SELECTCONCAT('duplicatekeys(',article_id,',',tag_id,')found')ASmsg;--insertanewrecordintoarticle_tagsINSERTINTOarticle_tags(article_id,tag_id)VALUES(article_id,tag_id);--returntagcountforthearticleSELECTCOUNT(*)FROMarticle_tags;END

Third, we add tag id 1, 2 and 3 for the article 1 by calling theinsert_article_tagsstored procedure as follows:CALLinsert_article_tags(1,1);CALLinsert_article_tags(1,2);CALLinsert_article_tags(1,3);

Fourth, let’s try to insert a duplicate key to see if the handler is really invoked.CALLinsert_article_tags(1,3);

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

If we change theCONTINUEin the handler declaration toEXIT, we will get only the error message.DELIMITER$$CREATEPROCEDUREinsert_article_tags_2(INarticle_idINT,INtag_idINT)BEGINDECLAREEXITHANDLERFORSQLEXCEPTIONSELECT'SQLExceptioninvoked';DECLAREEXITHANDLERFOR1062SELECT'MySQLerrorcode1062invoked';DECLAREEXITHANDLERFORSQLSTATE'23000'SELECT'SQLSTATE23000invoked';--insertanewrecordintoarticle_tagsINSERTINTOarticle_tags(article_id,tag_id)VALUES(article_id,tag_id);--returntagcountforthearticleSELECTCOUNT(*)FROMarticle_tags;END

Now, we can try to add a duplicate key to see the effect.CALLinsert_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.

An error always maps to one MySQL error code so a MySQL it is the most specific. AnSQLSTATEmay map to many MySQL error codes therefore it is less specific. AnSQLEXCPETIONor anSQLWARNINGis the shorthand for a class ofSQLSTATESvalues so it is the most generic.

Based on the handler precedence’s rules, MySQL error code handler,SQLSTATEhandler andSQLEXCEPTIONtakes the first, second and third precedence.

Suppose we declare three handlers in theinsert_article_tags_3stored procedure as follows:DELIMITER$$CREATEPROCEDUREinsert_article_tags_3(INarticle_idINT,INtag_idINT)BEGINDECLAREEXITHANDLERFOR1062SELECT'Duplicatekeyserrorencountered';DECLAREEXITHANDLERFORSQLEXCEPTIONSELECT'SQLExceptionencountered';DECLAREEXITHANDLERFORSQLSTATE'23000'SELECT'SQLSTATE23000';--insertanewrecordintoarticle_tagsINSERTINTOarticle_tags(article_id,tag_id)VALUES(article_id,tag_id);--returntagcountforthearticleSELECTCOUNT(*)FROMarticle_tags;END

We now try to insert a duplicate key into thearticle_tagstable by calling the stored procedure:CALLinsert_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.DECLAREEXITHANDLERFOR1051SELECT'Pleasecreatetableabcfirst';SELECT*FROMabc;

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 theDECLARE CONDITIONstatement that declares a named error condition, which associates with a condition. The syntax of theDECLARE CONDITIONstatement is as follows:DECLAREcondition_nameCONDITIONFORcondition_value;

Thecondition_valuecan be a MySQL error code such as 1015 or aSQLSTATEvalue. Thecondition_valueis represented by thecondition_name.

After declaration, you can refer to thecondition_nameinstead of thecondition_value.

So we can rewrite the code above as follows:DECLAREtable_not_foundCONDITIONfor1051;DECLAREEXITHANDLERFORtable_not_foundSELECT'Pleasecreatetableabcfirst';SELECT*FROMabc;

This code is obviously more readable than the previous one.

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

原文链接：http://outofmemory.cn/mysql/procedure/mysql-error-handling-in-stored-procedures