MySQL definition exceptions and exception handling methods

In MySQL, specific exceptions require specific processing. These exceptions can be linked to errors, as well as general process control in subroutines. Defining an exception is a prior definition of the problem encountered during the execution of the program, and exception handling defines how it should be handled when a problem is encountered, and ensures that the stored procedure or function can continue to execute when it encounters an error or a warning.

1 Exception Definitions1.1 Syntax

DECLARE condition_name condition for [condition_type];

1.2 Description

The condition_name parameter represents the name of the exception;

The condition_type parameter represents the type of condition, 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;

1.3 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;

2 Custom Exception handling2.1 Exception Handling Syntax

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

2.2 Parameter Description

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 indicates the type of error;
• SQLSTATE [value] Sqlstate_value is a string error value that contains 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;

2.3 Exception Capture Method

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 Synthesis Examples

Create a table, set the primary key for the table, and see which step to take if you do not define exception handling and define exception handling.

Show Databases;use wms;create table location (location_id int primary key,location_name varchar (50));

Example 1: Do not define exception cases

DELIMITER//create PROCEDURE handlerinsertnoexception () begin/*declare CONTINUE HANDLER for SQLSTATE ' 23000 ' SET @x2 =1;*/ SET @x=1;insert into Location VALUES (1, ' Beijing '); SET @x=2;insert into Location VALUES (1, ' Wuxi '); SET @x=3; End;//delimiter;

Call the stored procedure with the result:

Mysql> call Handlerinsertnoexception (); ERROR 1062 (23000): Duplicate entry ' 1 ' for key ' PRIMARY ' mysql> select @x;+------+|    @x |+------+| 2 |+------+1 row in Set (0.00 sec) mysql> Select * from location;+-------------+---------------+| location_id |           Location_name |+-------------+---------------+| 1 | Beijing |+-------------+---------------+1 row in Set (0.00 sec)

Note: To clear the data in the table before the operation Example 2, and exit the login again, in order to avoid the client variable @x effect, see the 1th in the conclusion in detail.

mysql> TRUNCATE TABLE location; Query OK, 0 rows affected (0.04 sec) mysql> select * from location; Empty Set (0.00 sec) mysql> exit; Bye[email protected]:~$ mysql-u root-penter password:welcome to the MySQL monitor.  Commands End With; or \g.your MySQL connection ID is 53Server version:5.5.38-0ubuntu0.14.04.1 (Ubuntu) mysql> use WMS; Reading table information for completion of table and column namesyou can turn off this feature to get a quicker startup W Ith-adatabase changedmysql> SELECT * from location; Empty Set (0.00 sec) mysql> Select @x;+------+| @x   |+------+| NULL |+------+1 row in Set (0.00 sec)

Example 2: Define Exception Handling cases:

DELIMITER//create PROCEDURE handlerinsertwithexception () begindeclare CONTINUE HANDLER for SQLSTATE ' 23000 ' SET @x2 = 1; SET @x=1;insert into Location VALUES (1, ' Beijing '); SET @x=2;insert into Location VALUES (1, ' Wuxi '); SET @x=3; End;//delimiter;

Call the stored procedure with the result:

Mysql> call Handlerinsertwithexception (); Query OK, 0 rows affected (0.09 sec) mysql> Select @x;+------+| @x   |+------+|    3 |+------+1 row in Set (0.00 sec)

Description and conclusion:

In MySQL, @var_name represents a user variable, it is assigned a value using the SET statement, the user variable is related to the connection, and a client-defined variable cannot be seen or used by other clients. When the client exits, all the variables for that client connection are automatically freed.

Second, in Example 1, due to the comment of the exception declaration "", when the same primary key is inserted into the table, the exception is triggered, and the default (exit) path is taken, and the view at this time @x returns 2, indicating that the following insert statement does not execute and exits.

Third, the definition of exception handling, at this time encountered an error will continue to follow the definition of the exception, but only the first data is inserted into the table, the user variable @x=3 description has been executed to the end;

--------------------------------------------------------------------------------------------------------------- --------------

If you have any problems in the process of trying, or if my code is wrong, please correct me, thank you very much!

Contact information: [Email protected]

Copyright @: Reprint please indicate the source!

MySQL definition exceptions and exception handling methods