Detailed explanation of MySQL definition exceptions and exception handling, and detailed explanation of mysql Exception Handling

Detailed explanation of MySQL definition exceptions and exception handling, and detailed explanation of mysql Exception Handling

In MySQL, specific exceptions must be handled. These exceptions can be associated with errors and general process control in subprograms. Defining exceptions is a problem encountered during the execution of the program in advance. Exception Handling defines the handling methods to be taken when a problem occurs, it also ensures that stored procedures or functions can continue to be executed in case of errors or warnings.

1. Exception Definition

1.1 syntax 

DECLARE condition_name condition for [condition_type];

1.2 description

The condition_name parameter indicates the Exception name;
The condition_type parameter indicates the condition type. condition_type consists of SQLSTATE [VALUE] sqlstate_value | mysql_error_code:

Both sqlstate_value and mysql_error_code can indicate MySQL errors;
Sqlstate_value is a string-type error code with a length of 5;
Mysql_error_code is a numeric error code;

1.3 example 
Define "ERROR 1148 (42000)" and name it command_not_allowed. You can use either of the following methods:

// Method 1: Use sqlstate_value DECLARE command_not_allowed condition for sqlstate '000000'; // Method 2: Use mysql_error_code DECLARE command_not_allowed condition for 42000;

2. Custom Exception Handling

2.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 the error handling method, and the parameter is one of three values;
CONTINUE indicates that an error is not handled and the execution continues;
EXIT indicates to EXIT immediately when an error occurs;
UNDO indicates that the previous operation is recalled after an error occurs. MySQL does not support rollback at the moment;

Condition_value:SQLSTATE [VALUE] sqlstate_value | condition_name | SQLWARNING | not found | SQLEXCEPTION | mysql_error_code

Condition_value indicates the error type;
SQLSTATE [VALUE] sqlstate_value is a string error VALUE that contains five characters;
Condition_name indicates the name of the error CONDITION defined by declare condition;
SQLWARNING matches all SQLSTATE error codes starting with 01;
Not found matches all SQLSTATE error codes starting with 02;
SQLEXCEPTION matches all SQLSTATE error codes NOT captured by SQLWARNING or not found;
Error Code of mysql_error_code matching value type;

2.3 exception capture method 

// Method 1: capture sqlstate_value exceptions // This method is to capture the sqlstate_value value. If the sqlstate_value is "42S02", perform the CONTINUE operation and output the "NO_SUCH_TABLE" Information declare continue handler for sqlstate '42s02 'SET @ info = 'no _ SUCH_TABLE '; // Method 2: Catch the mysql_error_code exception // This method is to capture the value of mysql_error_code. If the value of mysql_error_code is 1146, execute the CONTINUE operation and output the "NO_SUCH_TABLE" information; declare continue handler for 1146 SET @ info = 'no _ SUCH_TABLE '; // method 3: define conditions first, and then capture the exception DECLARE no_such_table condition for 1146; declare continue handler for NO_SUCH_TABLE SET @ info = 'no _ SUCH_TABLE '; // Method 4: use SQLWARNING to capture exceptions declare exit handler for sqlwarning set @ info = 'error'; // Method 5: use not found to capture exceptions declare exit handler for not found set @ info = 'no _ SUCH_TABLE '; // Method 6: capture exceptions using sqlexception declare exit handler for sqlexception set @ info = 'error ';

3 comprehensive example 

Create a table and set the primary key of the table. If you do not define exception handling or exception handling, you can see where to proceed.

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

Example 1: do not define exceptions

 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 and 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: Before example 2, clear the table data and log out again to avoid the influence of the client variable @ x. For details, see the first point in the conclusion.

 mysql> truncate table location;Query OK, 0 rows affected (0.04 sec)mysql> select * from location;Empty set (0.00 sec)mysql> exit;Byedavid@Louis:~$ 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 with -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 the exception handling case:

 DELIMITER //CREATE PROCEDURE handlerInsertWithException()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 and 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: 

1. in MySQL, @ var_name indicates the user variable, which is assigned a value using the SET statement. The user variable is related to the connection. The variables defined by one client cannot be seen or used by other clients. When the client exits, all variables connected to the client are automatically released.

2. In example 1, because the exception Declaration "" is commented, inserting the same primary key to the table triggers an exception and uses the default EXIT path; check that @ x returns 2 at this time, indicating that the following INSERT statement is exited if it is not executed.

3. If exception handling is defined, the execution will continue as the exception is defined. However, only the first data is inserted into the table, at this time, the user variable @ x = 3 indicates that the execution has reached the end;

The above is all the content of this article. I hope it will be helpful for your learning and support for helping customers.