MySQL DECLARE定义条件和处理程序(附带实例)
特定条件需要特定处理,这些条件可以联系到错误以及子程序中的一般流程控制。
定义条件是事先定义程序执行过程中遇到的问题,处理程序定义了在遇到这些问题时应当采取的处理方式,并且保证存储过程或函数在遇到警告或错误时能继续执行。这样可以增强存储程序处理问题的能力,避免程序异常停止运行。
本节将介绍 MySQL 中如何使用 DECLARE 关键字来定义条件和处理程序。
例如,在 ERROR 1142(42000)中,sqlstate_value 的值是 42000,MySQL_error_code 的值是 1142。这个语句指定需要特殊处理的条件。它将一个名字和指定的错误条件关联起来。这个名字可以被用在随后定义处理程序的 DECLARE HANDLER 语句中。
【实例】定义“ERROR 1148(42000)”错误,名称为command_not_allowed。可以用两种不同的方法来定义,代码如下:
condition_value 表示错误类型,可以有以下取值:
sp_statement 参数为程序语句段,表示在遇到定义的错误时需要执行的存储过程或函数。
【实例】定义处理程序的几种方式,代码如下:
【实例】定义条件和处理程序,具体执行的过程如下:
定义条件是事先定义程序执行过程中遇到的问题,处理程序定义了在遇到这些问题时应当采取的处理方式,并且保证存储过程或函数在遇到警告或错误时能继续执行。这样可以增强存储程序处理问题的能力,避免程序异常停止运行。
本节将介绍 MySQL 中如何使用 DECLARE 关键字来定义条件和处理程序。
MySQL定义条件
定义条件使用 DECLARE 语句,语法格式如下: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 为数值类型错误代码。
例如,在 ERROR 1142(42000)中,sqlstate_value 的值是 42000,MySQL_error_code 的值是 1142。这个语句指定需要特殊处理的条件。它将一个名字和指定的错误条件关联起来。这个名字可以被用在随后定义处理程序的 DECLARE HANDLER 语句中。
【实例】定义“ERROR 1148(42000)”错误,名称为command_not_allowed。可以用两种不同的方法来定义,代码如下:
//方法一:使用sqlstate_value DECLARE command_not_allowed CONDITION FOR SQLSTATE '42000'; //方法二:使用mysql_error_code DECLARE command_not_allowed CONDITION FOR 1148
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 个值:CONTINUE、EXIT 和 UNDO:
- CONTINUE 表示遇到错误不处理,继续执行;
- EXIT 表示遇到错误马上退出;
- UNDO 表示遇到错误后撤回之前的操作,MySQL 中暂时不支持这样的操作。
condition_value 表示错误类型,可以有以下取值:
- SQLSTATE [VALUE] sqlstate_value:包含 5 个字符的字符串错误值。
- condition_name:表示 DECLARE CONDITION 定义的错误条件名称。
- SQLWARNING:匹配所有以 01 开头的 SQLSTATE 错误代码。
- NOT FOUND:匹配所有以 02 开头的 SQLSTATE 错误代码。
- SQLEXCEPTION:匹配所有没有被 SQLWARNING 或 NOT FOUND 捕获的 SQLSTATE 错误代码。
- MySQL_error_code:匹配数值类型错误代码。
sp_statement 参数为程序语句段,表示在遇到定义的错误时需要执行的存储过程或函数。
【实例】定义处理程序的几种方式,代码如下:
//方法一:捕获sqlstate_value DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02' SET @info='NO_SUCH_TABLE'; //方法二:捕获mysql_error_code 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';上述代码是 6 种定义处理程序的方法:
- 第一种方法是捕获 sqlstate_value 值。如果遇到 sqlstate_value 值为“42S02”,执行 CONTINUE 操作,并且输出“NO_SUCH_TABLE”信息。
- 第二种方法是捕获 MySQL_error_code 值。如果遇到 MySQL_error_code 值为 1146,执行 CONTINUE 操作,并且输出“NO_SUCH_TABLE”信息。
- 第三种方法是先定义条件,再调用条件。这里先定义 no_such_table 条件,遇到 1146 错误就执行 CONTINUE 操作。
- 第四种方法是使用 SQLWARNING。SQLWARNING 捕获所有以 01 开头的 sqlstate_value 值,然后执行 EXIT 操作,并且输出“ERROR”信息。
- 第五种方法是使用 NOT FOUND。NOT FOUND 捕获所有以 02 开头的 sqlstate_value 值,然后执行 EXIT 操作,并且输出“NO_SUCH_TABLE”信息。
- 第六种方法是使用 SQLEXCEPTION。SQLEXCEPTION 捕获所有没有被 SQLWARNING 或 NOT FOUND 捕获的 sqlstate_value 值,然后执行 EXIT 操作,并且输出“ERROR”信息。
【实例】定义条件和处理程序,具体执行的过程如下:
mysql> CREATE TABLE test_db.t (s1 int,primary key (s1)); mysql> DELIMITER // mysql> CREATE PROCEDURE handlerdemo () -> BEGIN -> DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x2 = 1; -> SET @x = 1; -> INSERT INTO test_db.t VALUES (1); -> SET @x = 2; -> INSERT INTO test_db.t VALUES (1); -> SET @x = 3; -> END; -> // mysql> DELIMITER ; /*调用存储过程*/ mysql> CALL handlerdemo(); /*查看调用过程结果*/ mysql> SELECT @x; +------+ | @x | +------+ | 3 | +------+
@x
是 1 个用户变量,执行结果 @x 等于 3,这表明 MySQL 被执行到程序的末尾。如果“DECLARECONTINUE HANDLER FOR SQLSTATE '23000' SET @x2 = 1;”这 1 行不在,第 2 个 INSERT 因 PRIMARY KEY 强制而失败之后,MySQL 可能已经采取默认(EXIT)路径,并且 SELECT @x 可能已经返回 2。提示,“@var_name”表示用户变量,使用 SET 语句为其赋值,用户变量与连接有关,一个客户端定义的变量不能被其他客户端看到或使用。当客户端退出时,该客户端连接的所有变量将自动释放。