（2.12）Mysql之SQL基础——存储过程条件定义与错误处理

转自：博客园桦仔

5、存储过程条件定义与错误处理

-- (1)定义
【1】条件定义：declare condition_name condition for condition_value;
【2】错误处理：declare handler_type handler for condition_value[]　sql_statement;

handler_type：
　　【1】continue：遇到错误后不处理，继续向下执行　　【2】exit　：遇到错误后退出过程　　【3】undo：表示遇到错误后撤回之前的操作，但mysql暂时还不支持这种

DECLARE condition_name CONDITION FOR[condition_type]
[condition_type]:
SQLSTATE[VALUE] sqlstate_value |mysql_error_code

condition_name：表示条件名称

condition_type：表示条件的类型

sqlstate_value和mysql_error_code都可以表示mysql错误

sqlstate_value为长度5的字符串错误代码

mysql_error_code为数值类型错误代码，例如：ERROR1142(42000)中，sqlstate_value的值是42000，

mysql_error_code的值是1142

这个语句指定需要特殊处理条件。他将一个名字和指定的错误条件关联起来。

这个名字随后被用在定义处理程序的DECLARE HANDLER语句中

定义ERROR1148(42000)错误，名称为command_not_allowed。

可以用两种方法定义

//方法一：使用sqlstate_value
DECLARE command_not_allowed CONDITION FOR SQLSTATE '42000'

//方法二：使用mysql_error_code
DECLARE command_not_allowed CONDITION FOR SQLSTATE 1148

2．定义处理程序

MySQL中可以使用DECLARE关键字来定义处理程序。其基本语法如下：

DECLARE handler_type HANDLER FOR 
condition_value[,...] sp_statement 
handler_type: 
CONTINUE | EXIT | UNDO 
condition_value: 
SQLSTATE [VALUE] sqlstate_value |
condition_name | SQLWARNING 
| NOT FOUND | SQLEXCEPTION | mysql_error_code 

其中，handler_type参数指明错误的处理方式，该参数有3个取值。这3个取值分别是CONTINUE、EXIT和UNDO。

CONTINUE表示遇到错误不进行处理，继续向下执行；

EXIT表示遇到错误后马上退出；

UNDO表示遇到错误后撤回之前的操作，MySQL中暂时还不支持这种处理方式。

注意：通常情况下，执行过程中遇到错误应该立刻停止执行下面的语句，并且撤回前面的操作。

但是，MySQL中现在还不能支持UNDO操作。

因此，遇到错误时最好执行EXIT操作。如果事先能够预测错误类型，并且进行相应的处理，那么可以执行CONTINUE操作。

condition_value参数指明错误类型，该参数有6个取值。

sqlstate_value和mysql_error_code与条件定义中的是同一个意思。

condition_name是DECLARE定义的条件名称。

SQLWARNING表示所有以01开头的sqlstate_value值。

NOT FOUND表示所有以02开头的sqlstate_value值。

SQLEXCEPTION表示所有没有被SQLWARNING或NOT FOUND捕获的sqlstate_value值。

sp_statement表示一些存储过程或函数的执行语句。

下面是定义处理程序的几种方式。代码如下：

//方法一：捕获sqlstate_value 
DECLARE CONTINUE HANDLER FOR SQLSTATE '42000'
SET @info='CAN NOT FIND'; 
//方法二：捕获mysql_error_code 
DECLARE CONTINUE HANDLER FOR 1148SET @info='CAN NOT FIND'; 
//方法三：先定义条件，然后调用 
DECLARE can_not_find CONDITION FOR 1146 ; 
DECLARE CONTINUE HANDLER FOR can_not_find SET 
@info='CAN NOT FIND'; 
//方法四：使用SQLWARNING 
DECLARE EXIT HANDLER FOR SQLWARNING SET @info='ERROR'; 
//方法五：使用NOT FOUND 
DECLARE EXIT HANDLER FOR NOT FOUND SET @info='CAN NOT FIND'; 
//方法六：使用SQLEXCEPTION 
DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info='ERROR';

上述代码是6种定义处理程序的方法。

第一种方法是捕获sqlstate_value值。如果遇到sqlstate_value值为42000，执行CONTINUE操作，并且输出"CAN NOT FIND"信息。

第二种方法是捕获mysql_error_code值。如果遇到mysql_error_code值为1148，执行CONTINUE操作，并且输出"CAN NOT FIND"信息。

第三种方法是先定义条件，然后再调用条件。这里先定义can_not_find条件，遇到1148错误就执行CONTINUE操作。

第四种方法是使用SQLWARNING。SQLWARNING捕获所有以01开头的sqlstate_value值，然后执行EXIT操作，并且输出"ERROR"信息。

第五种方法是使用NOT FOUND。NOT FOUND捕获所有以02开头的sqlstate_value值，然后执行EXIT操作，并且输出"CAN NOT FIND"信息。

第六种方法是使用SQLEXCEPTION。SQLEXCEPTION捕获所有没有被SQLWARNING或NOT FOUND捕获的sqlstate_value值，然后执行EXIT操作，并且输出"ERROR"信息

定义条件和处理程序

CREATE TABLE t8(s1 INT,PRIMARY KEY(s1))

DELIMITER //
CREATE PROCEDURE handlerdemo()
BEGIN
DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @X2=1;
SET @X=1;
INSERT INTO t8 VALUES(1);
SET @X=2;
INSERT INTO t8 VALUES(1);
SET @X=3;
END;
//
DELIMITER ;

/* 调用存储过程*/
CALL handlerdemo();

/* 查看调用存储过程结果*/
SELECT @X

 

@X是一个用户变量，执行结果@X等于3，这表明MYSQL执行到程序的末尾。

如果DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @X2=1;，这一行不存在

第二个INSERT因PRIMARY KEY约束而失败之后，MYSQL可能已经采取EXIT策略，并且SELECT @X可能已经返回2

注意：@X表示用户变量，使用SET语句为其赋值，用户变量与连接有关，一个客户端定义的变量不能被其他客户端所使用

即有作用域的，该客户端退出时，客户端连接的所有变量将自动释放

这里的变量跟SQLSERVER没有什么区别，都是用来存储临时值的

MYSQL这里的条件和预定义程序其实跟SQLSERVER的自定义错误是一样的