MySQL exception handling

--The article content through the network search combination,

MySQL exception, you can customize the exception, and then apply. You can also use the system default exception to capture the app.

First, the exception definition:

DECLARE condition_name condition for [condition_type];

The condition_name parameter represents the name of the exception;

condition_type Parameter exception type

Condition_type consists of SQLState [VALUE] Sqlstate_value|mysql_error_code:

Both Sqlstate_value and Mysql_error_code can represent MySQL errors;

Sqlstate_value is a string type error code of length 5; Mysql_error_code is a numeric type error code;

Example:

"Error 1148 (42000)" is defined with the name command_not_allowed. There are two ways to do this:

Method One: Use Sqlstate_value

DECLARE command_not_allowed CONDITION for SQLSTATE ' 42000 ';

Method Two: Use Mysql_error_code

DECLARE command_not_allowed CONDITION for 1148;

Second, exception handling

DECLARE Handler_type handler for Condition_value [,...] Sp_statement

Handler_type: continue| Exit| UNDO

Handler_type is an error-handling method, with a parameter of one of 3 values;

Continue indicates that an error has been encountered and is not processed, continue execution;

Exit means exit immediately when an error is encountered;

Undo indicates that the rollback operation was not supported by MySQL until the operation was recalled after encountering an error;

Condition_value: SQLSTATE [VALUE] sqlstate_value| condition_name| Sqlwarning| Not found| Sqlexception|mysql_error_code

Condition_value represents the error type; SQLSTATE [value] Sqlstate_value is a string error value containing 5 characters;
Condition_name represents the name of the error condition defined by the declare condition;

SQLWarning matches all SQLState error codes starting with 01;

Not found matches all SQLSTATE error codes that begin with 02;

SqlException matches all SQLState error codes that are not captured by sqlwarning or not found;

Mysql_error_code matching numeric type error codes;

Example:

Method One: Catch Sqlstate_value exceptions

This method captures the Sqlstate_value value. If you encounter a Sqlstate_value value of "42S02", perform a continue operation and output "no_such_table" information

DECLARE CONTINUE HANDLER for SQLSTATE ' 42s02 ' SET @info = ' no_such_table ';

Method Two: Capturing Mysql_error_code anomalies

This method captures the Mysql_error_code value. If you encounter a Mysql_error_code value of 1146, perform a continue operation and output the "no_such_table" message;

DECLARE CONTINUE HANDLER for 1146 SET @info = ' no_such_table ';

Method Three: Define the condition first and then catch the exception

DECLARE no_such_table CONDITION for 1146;

DECLARE CONTINUE HANDLER for no_such_table SET @info = ' no_such_table ';

Method Four: Use SQLWarning to catch exceptions

DECLARE EXIT HANDLER for sqlwarning SET @info = ' ERROR ';

Method Five: Use not found to catch exceptions

DECLARE EXIT HANDLER for not FOUND SET @info = ' no_such_table ';

Method Six: Use SqlException to catch exceptions

DECLARE EXIT HANDLER for SQLEXCEPTION SET @info = ' ERROR ';

3. Example

CREATE PROCEDURE P_test_excep ()

BEGIN

DECLARE EXIT HANDLER for SQLEXCEPTION inserts into Test_log (ID) VALUES (1);
INSERT into TEST (ID) VALUES (1);
END

--inserts data into the log table when a primary key conflict occurs.

MySQL exception handling