存储过程的异常处理



这部分，主要讲解一些简单的问题、异常处理器和条件。下面首先看几个简单的问题：
1、log of failures 错误日志
在INSERT失败时，希望将错误记录到日志文件中，以便需要时能查看到错误记录，当然也可以将错误时间、出错原因等记录到指定的文件中。
2、下面做一个例子，创建一个主键表，一个外键表，在mysql中由于使用了InnoDB存储引擎，所以外键关联检查是打开的，所以向外键表中插入非主键表的值时，是不会成功的，操作过程和结果如下：
mysql> create table t2
    -> (s1 int,primary key(s1))
    -> engine=innodb;//
Query OK, 0 rows affected (0.28 sec)

mysql> create table t3
    -> (s1 int,key(s1),foreign key(s1) references t2(s1))
    -> engine=innodb;//
Query OK, 0 rows affected (0.12 sec)

mysql> insert into t3 values(4);//
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`db1/t3`, CONSTRAINT `t3_ibfk_1` FOREIGN KEY (`s1`) REFERENCES `t2` (`s1`))
3、现在建立一个错误日志记录表
mysql> create table error_log(error_message char(100));//
Query OK, 0 rows affected (0.03 secmysql> create table error_log(error_message char(100));//
Query OK, 0 rows affected (0.03 sec).
4、建立一个存储过程，使用DECLARE EXIT HANDLER语句来处理异常，意思是如果发生1452错误，这个存储过程程序就会将错误信息记录到表error_log中，其中EXIT的意思是当错误信息提交并记录后退出这个符合语句，下面是存储过程的程序：
mysql> create procedure pro16(parameter1 int)
    -> begin
    -> declare exit handler for 1452
    ->  insert into error_log(error_message) values(concat(‘time:’,current_date,’.foreign key reference failure for value=’,parameter1));
    -> insert into t3 values(parameter1);
    -> end;//
Query OK, 0 rows affected (0.02 sec)
5、调用存储过程
mysql> call pro16(10)//
Query OK, 1 row affected (0.11 sec)

mysql> select * from error_log//
+————————————————————+
| error_message                                              |
+————————————————————+
| time:2010-06-27.foreign key reference failure for value=10 |
+————————————————————+
1 row in set (0.00 sec)

mysql> select * from t3//
Empty set (0.00 sec)
根据结果可以看出，屏幕上没有输出错误信息，而是将错误信息存储到error_log表中。t3表中没有增加任何东西，但是error_log表中记录了错误信息，这就说明insert into t3操作失败。

DECLARE HANDLER syntax 声明异常处理的语法

DECLARE HANDLER的语法
语法是这样的：
DECLARE
{EXIT|CONTINUE}
HANDLER FOR
{error-number|{SQLSTATE error-string}|condition}
SQL statement；
    这段错误处理程序会在其他程序出错后被自动触发。MySQL允许两种处理器，一种是EXIT，它在执行完原主程序后错误处理就停止运行；另一种是CONTINUE处理器，它在执行完错误处理后，继续执行原主程序，那么这个主程序就会一直运行，而无法终止。下面使用CONTINUE编写一个处理错误的例子。
1、
mysql> create table t4(s1 int,primary key(s1));//
Query OK, 0 rows affected (0.12 sec)
mysql> create procedure pro17()
    -> begin
    ->  declare continue handler for sqlstate ’23000′ set @x2=1;
    ->  set @x=1;
    -> insert into t4 values(1);
    -> set @x=2;
    -> insert into t4 values(1);
    -> set @x=3;
    -> end;//
Query OK, 0 rows affected (0.03 sec)
    pro17()是使用CONTINUE编写一个错误处理程序，这次使用SQLSTATE值定义一个处理程序，在这里sqlstate ’23000′是一个更常用的错误代码，它是当外键约束出错或主键约束出错时就会被调用。在pro17()这个程序中，首先设置set @x=1;，向表中插入数值1；然后设置set @x=2;再次尝试向主键表中插入数值1，由于主键有唯一性的限制，所以这次会失败；由于插入失败，错误处理程序被触发，并执行错误处理程序，设置set @x2=1；错误执行语句执行完成后，由于使用CONTINUE处理的异常，所以程序返回到失败的插入语句之后，继续执行set @x=3;后程序结束。
    根据以上分析，下面调用一下存储过程程序，并查看运行结果如下：
mysql> call pro17()//
Query OK, 0 rows affected (0.02 sec)

mysql> select @x,@x2//
+——+——+
| @x   | @x2  |
+——+——+
| 3    | 1    |
+——+——+
1 row in set (0.00 sec)
以上显示结果是@x=3，@x2=1，和上面分析的情况相同，说明程序运行无误。

Page 33———————– 1. DECLARE CONDITION 

DECLARE CONDITION环境声明
    在进行错误处理的时候，可以使用SQLSTATE或指定一个错误代码，实际上，也可以给他们定义一个名字，然后在进行处理的时候使用定义的名字。比如看下面这个例子：
mysql> create procedure pro18()
    -> begin
    -> declare`constraint violation` condition for sqlstate ’23000′;#注意constraint violation两侧的是“`”，即Tab键上边，1键左边的按键，不是单引号，写成单引号会报错
    -> declare exit handler for `constraint violation` rollback;#注意constraint violation两侧的是“`”
    -> start transaction;
    -> insert into t2(s1) values(1);
    -> insert into t2(s1) values(1);
    -> commit;
    -> end;//
Query OK, 0 rows affected (0.00 sec)
    首先给sqlstate ’23000′定义了一个名字`constraint violation`，在进行操作的时候，就直接可以用这个名字了。t2表是一个innodb表，所以对这个表的插入操作都会ROLLBACK回滚，在这里例子中，由于主键插入两个同样的值导致sqlstate 23000错误发生，导致回滚事件发生。sqlstate 23000是约束错误。
    下面调用这个存储过程并查看运行结果：
mysql> call pro18()//
Query OK, 0 rows affected (0.04 sec)

mysql> select * from t2//
Empty set (0.01 sec)
    可以看到t2中没有插入任何记录，全部事务都回滚了。

     下面再来介绍几个声明条件，首先看例子：
mysql> create procedure pro19()
    -> begin
    -> declare exit handler for not found begin end;
    -> declare exit handler for sqlexception begin end;
    -> declare exit handler for sqlwarning begin end;
    -> end;//
Query OK, 0 rows affected (0.00 sec)
这个例子展示了三个预条件声明：NOT FOUNT是找不到行，SQLEXCEPTION是错误，SQLWARNING是警告或注释；这三个条件声明是预声明，所以不需要声明条件就可以使用。但是如果使用:declare sqlexception condition….这种格式的话，就会报错。