MySQL Error handling and raising in Stored procedures

The way that MySQL stored procedure errors are captured differs greatly from that of Oracle.

You can use the DECLARE keyword to define a handler in MySQL. The basic syntax is as follows:

DECLAREHandler_type Handler forCondition_value[,...]sp_statement Handler_type:CONTINUE | EXIT condition_value:sqlstate[VALUE]Sqlstate_value|Condition_name|sqlwarning|  notFOUND|SQLEXCEPTION|Mysql_error_code

The Handler_type parameter indicates how the error is handled, which has 2 values. These 1 values are continue, EXIT, respectively.

Continue indicates an error encountered, executes a predefined way, and continues down execution;

Exit indicates that after encountering an error, execute a predefined method and exit immediately;

Note: Typically, errors encountered during execution should immediately stop executing the following statement and recall the previous action.

The Condition_value parameter indicates the type of error, which has 6 values.

Sqlstate_value and Mysql_error_code are the same meaning as in conditional definitions.

Condition_name is the condition name defined by declare.

SQLWarning represents all sqlstate_value values that begin with 01.

Not found represents all sqlstate_value values that begin with 02.

SqlException represents all sqlstate_value values that are not captured by sqlwarning or not found.

Sp_statement represents some execution statements for stored procedures or functions.

Here are a few ways to define the handler. The code is as follows:

//method One: Capture Sqlstate_valueDECLARE CONTINUEHANDLER forSQLSTATE'42000'SET @info='CAN not FIND'; //method Two: Capture Mysql_error_codeDECLARE CONTINUEHANDLER for1148SET @info='CAN not FIND'; //method Three: Define the condition first, and then call theDECLARECan_not_find CONDITION for 1146 ; DECLARE CONTINUEHANDLER forCan_not_findSET @info='CAN not FIND'; //method Four: Use SQLWarningDECLARE EXITHANDLER forSQLWarningSET @info='ERROR'; //method Five: Use not FOUNDDECLARE EXITHANDLER for  notFOUNDSET @info='CAN not FIND'; //method Six: Use SqlExceptionDECLARE EXITHANDLER forSQLEXCEPTIONSET @info='ERROR';

The above code is 6 ways to define handlers.

The first method is to capture the Sqlstate_value value. If a sqlstate_value value of 42000 is encountered, the continue operation is performed and the "CAN not FIND" message is output.

The second method is to capture the Mysql_error_code value. If a mysql_error_code value of 1148 is encountered, the continue operation is performed and the "CAN not FIND" message is output.

The third method is to define the condition before calling the condition. The can_not_find condition is defined first, and the continue operation is performed with a 1148 error.

The fourth method is to use SQLWarning. SQLWarning captures all Sqlstate_value values starting with 01, then executes the exit operation and outputs the "ERROR" information.

The fifth method is to use not FOUND. Not found captures all sqlstate_value values starting with 02, then performs the exit operation and outputs "CAN not FIND" information.

The sixth method is to use SqlException. SqlException captures all sqlstate_value values that are not captured by sqlwarning or not found.

The

DECLARE CONTINUE  for  not SET = 1;

DECLARE CONTINUE  for 1062 SELECT ' Error, duplicate key occurred ';

If you want to do more with error capture, you can do this.

DECLARE EXIT  for SQLEXCEPTION BEGIN ROLLBACK ; SELECT ' An error had occurred, Operation Rollbacked and the stored procedure was terminated ' ; END;

Example

DELIMITER $$CREATE PROCEDUREInsert_article_tags (incharticle_idINT,inchtag_idINT)BEGIN     DECLARE CONTINUEHANDLER for 1062    SELECTCONCAT ('Duplicate keys (', article_id,',', tag_id,') found') asmsg; --insert a new record into Article_tags    INSERT  intoarticle_tags (article_id,tag_id)VALUES(article_id,tag_id); --return tag Count for the article    SELECT COUNT(*) fromarticle_tags;END

Reference:

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

http://www.mysqltutorial.org/mysql-signal-resignal/

Http://www.cnblogs.com/lyhabc/p/3793524.html

http://www.zhdba.com/mysqlops/2013/08/31/mysql-handler-2/

MySQL Error handling and raising in Stored procedures