声明异常处理的语法:
DECLARE
{ EXIT | CONTINUE }
HANDLER FOR
{ error-number | { SQLSTATE error-string } | condition }
SQL statement
上面就是错误处理的用法,也就是一段当程序出错后自动触发的代码。
MySQL允许两种处理器,一种是EXIT处理,另外一种是CONTINUE处理。他跟EXIT处理类似。不同在于它执行后,原主程序任然继续运行。
mysql> CREATE TABLE t2(
-> s1 INT, PRIMARY KEY (s1))
-> engine=innodb;//
Query OK, 0 rows affected (0.21 sec)
mysql> CREATE TABLE t3 (s1 INT, KEY (s1),
-> FOREIGN KEY (s1) REFERENCES t2 (s1))
-> engine=innodb;//
Query OK, 0 rows affected (0.07 sec)
mysql> INSERT INTO t3 VALUES (5);//
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`db5/t3`, CONSTRAINT `t3_ibfk_1` FOREIGN KEY (`s1`) REFERENCES `t2` (`s1`))
mysql> INSERT INTO t3 VALUES (5);//
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`db5/t3`, CONSTRAINT `t3_ibfk_1` FOREIGN KEY (`s1`) REFERENCES `t2` (`s1`))
mysql> CREATE TABLE error_log (error_message
-> VARCHAR(150))//
Query OK, 0 rows affected (0.07 sec)
mysql> CREATE PROCEDURE p200 (parameter1 INT)
-> BEGIN
-> DECLARE EXIT HANDLER FOR 1452
-> INSERT INTO error_log VALUES
-> (CONCAT('Time: ',current_date,
-> '. Foreign Key Reference Failure For
'> Value = ',parameter1));
-> INSERT INTO t3 VALUES (parameter1);
-> END;//
Query OK, 0 rows affected (0.01 sec)
mysql> call p200(5);//
Query OK, 1 row affected (0.05 sec)
mysql> select * from error_log;//
+---------------------------------------------------------------+
| error_message |
+---------------------------------------------------------------+
| Time: 2014-03-04. Foreign Key Reference Failure For
Value = 5 |
+---------------------------------------------------------------+
1 row in set (0.00 sec)
上面的DECLARE EXIT HANDLER FOR 1452是用来处理异常的,意思是如果1452发生了,这个程序将会在错误记录表中插入一行,EXIT意思是当动作成功提交后退出这个复合语句。但是这里有一个缺点,因为需要知道发生的错误是什么才能准确的记录到表里面。
DECLARE
{ EXIT | CONTINUE }
HANDLER FOR
{ error-number | { SQLSTATE error-string } | condition }
SQL statement
上面就是错误处理的用法,也就是一段当程序出错后自动触发的代码。
MySQL允许两种处理器,一种是EXIT处理,另外一种是CONTINUE处理。他跟EXIT处理类似。不同在于它执行后,原主程序任然继续运行。
mysql> CREATE TABLE t2(
-> s1 INT, PRIMARY KEY (s1))
-> engine=innodb;//
Query OK, 0 rows affected (0.21 sec)
mysql> CREATE TABLE t3 (s1 INT, KEY (s1),
-> FOREIGN KEY (s1) REFERENCES t2 (s1))
-> engine=innodb;//
Query OK, 0 rows affected (0.07 sec)
mysql> INSERT INTO t3 VALUES (5);//
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`db5/t3`, CONSTRAINT `t3_ibfk_1` FOREIGN KEY (`s1`) REFERENCES `t2` (`s1`))
mysql> INSERT INTO t3 VALUES (5);//
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`db5/t3`, CONSTRAINT `t3_ibfk_1` FOREIGN KEY (`s1`) REFERENCES `t2` (`s1`))
mysql> CREATE TABLE error_log (error_message
-> VARCHAR(150))//
Query OK, 0 rows affected (0.07 sec)
mysql> CREATE PROCEDURE p200 (parameter1 INT)
-> BEGIN
-> DECLARE EXIT HANDLER FOR 1452
-> INSERT INTO error_log VALUES
-> (CONCAT('Time: ',current_date,
-> '. Foreign Key Reference Failure For
'> Value = ',parameter1));
-> INSERT INTO t3 VALUES (parameter1);
-> END;//
Query OK, 0 rows affected (0.01 sec)
mysql> call p200(5);//
Query OK, 1 row affected (0.05 sec)
mysql> select * from error_log;//
+---------------------------------------------------------------+
| error_message |
+---------------------------------------------------------------+
| Time: 2014-03-04. Foreign Key Reference Failure For
Value = 5 |
+---------------------------------------------------------------+
1 row in set (0.00 sec)
上面的DECLARE EXIT HANDLER FOR 1452是用来处理异常的,意思是如果1452发生了,这个程序将会在错误记录表中插入一行,EXIT意思是当动作成功提交后退出这个复合语句。但是这里有一个缺点,因为需要知道发生的错误是什么才能准确的记录到表里面。