MySQL定义异常和异常处理详解

//方法一：使用sqlstate_value 
DECLARE command_not_allowed CONDITION FOR SQLSTATE '42000′; 
//方法二：使用mysql_error_code 
DECLARE command_not_allowed CONDITION FOR 1148; 

//方法一：捕获sqlstate_value异常 
//这种方法是捕获sqlstate_value值。如果遇到sqlstate_value值为”42S02″，执行CONTINUE操作，并输出”NO_SUCH_TABLE”信息 
DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02′ SET @info='NO_SUCH_TABLE'; 


//方法二：捕获mysql_error_code异常 
//这种方法是捕获mysql_error_code值。如果遇到mysql_error_code值为1146，执行CONTINUE操作，并输出”NO_SUCH_TABLE”信息； 
DECLARE CONTINUE HANDLER FOR 1146 SET @info='NO_SUCH_TABLE'; 


//方法三：先定义条件，然后捕获异常 
DECLARE no_such_table CONDITION FOR 1146; 
DECLARE CONTINUE HANDLER FOR NO_SUCH_TABLE SET @info='NO_SUCH_TABLE';  


//方法四：使用SQLWARNING捕获异常 
DECLARE EXIT HANDLER FOR SQLWARNING SET @info='ERROR';  


//方法五：使用NOT FOUND捕获异常 
DECLARE EXIT HANDLER FOR NOT FOUND SET @info='NO_SUCH_TABLE';


//方法六：使用SQLEXCEPTION捕获异常 
DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info='ERROR';

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

 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 ; 

 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) 

 mysql> truncate table location;
Query OK, 0 rows affected (0.04 sec)
mysql> select * from location;
Empty set (0.00 sec)
mysql> exit;
Bye

david@Louis:~$ mysql -u root -p
Enter password: 
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 53
Server version: 5.5.38-0ubuntu0.14.04.1 (Ubuntu)

mysql> use wms;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from location;
Empty set (0.00 sec)

mysql> select @x;
+------+
| @x  |
+------+
| NULL |
+------+
1 row in set (0.00 sec)

 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 ; 

 mysql> CALL handlerInsertWithException();
Query OK, 0 rows affected (0.09 sec)

mysql> select @x;
+------+
| @x  |
+------+
|  3 |
+------+
1 row in set (0.00 sec)