日历

2017年十二月
« 9月    
 123
45678910
11121314151617
18192021222324
25262728293031

最近评论

    linux企业应用

    mysql第八部分 存储过程的异常处理

    在这部分,主要讲解一些简单的问题、异常处理器和条件。下面首先看几个简单的问题:
    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….这种格式的话,就会报错。

    评论已关闭。