MySQL definition exception and exception handling method _ MySQL

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. exception handling defines the handling method to be taken when a problem occurs and ensures that the exception is stored 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

Condition_nameParameter indicates the name of the exception;

Condition_typeThe parameter indicates the type of the condition. 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 numerical 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 '2013 ';

// Method 2: Use mysql_error_code

DECLARE command_not_allowed condition for 1148;

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 the three values. CONTINUE indicates that an error is not processed, and execution continues; EXIT indicates that an error is exited immediately; 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 indicates the string error VALUE that contains 5 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 that are NOT captured by SQLWARNING or not found; mysql_error_code matches the value type error code; 2.3 exception capture method

// Method 1: capture sqlstate_value exceptions

// This method captures 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 an error in mysql_error_code

// This method captures the value of mysql_error_code. If the value of mysql_error_code is 1146, perform the CONTINUE operation and output "NO_SUCH_TABLE" information;

Declare continue handler for 1146 SET @ info = 'no _ SUCH_TABLE ';

// Method 3: First define conditions and then capture exceptions

DECLARE no_such_table condition for 1146;

Declare continue handler for NO_SUCH_TABLE SET @ info = 'no _ SUCH_TABLE ';

// Method 4: capture exceptions using SQLWARNING

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()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 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 statement "" 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;

Bytes -----------------------------------------------------------------------------------------------------------------------------

If you encounter any problems during your attempt or my code is incorrect, please correct me. thank you very much!

Contact: david.louis.tian@outlook.com

Copyright @: reprinted, please indicate the source!