MySQL 存储过程定义条件和异常处理

谁践踏了优雅 2024-04-18 02:00 78阅读 0赞

查看调用存储过程时的报错代码  
mysql> select \* from test;  
\+------+--------+  
| id   | name   |  
\+------+--------+  
|   10 | neo    |  
|   10 | neo    |  
|   20 | John   |  
|   30 | Lucy   |  
|   40 | Larry  |  
|   50 | Lilly  |  
|   60 | Carlos |  
|   70 | Jason  |  
\+------+--------+  
8 rows in set (0.00 sec)  
  
mysql> show create procedure p\_test\\G  
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
           Procedure: p\_test  
            sql\_mode: NO\_ENGINE\_SUBSTITUTION  
    Create Procedure: CREATE DEFINER=\`root\`@\`localhost\` PROCEDURE \`p\_test\`(in v\_id int)  
begin  
set @c='insert into test values(?,?)';  
select id into @a from test where id=v\_id;  
select @a;  
end  
character\_set\_client: utf8  
collation\_connection: utf8\_general\_ci  
  Database Collation: latin1\_swedish\_ci  
1 row in set (0.00 sec)  
  
mysql> call p\_test(10);  
ERROR 1172 (42000): Result consisted of more than one row  
  
去官网查看对应的存储过程异常代码  
Error: 1172 SQLSTATE: 42000 (ER\_TOO\_MANY\_ROWS)  
  
Message: Result consisted of more than one row   
  
在存储过程里面定义异常  
mysql> delimiter $$  
mysql> create procedure p\_test(in v\_id int)  
    -> begin  
    -> /\* Declare Conditions \*/  
    -> DECLARE too\_many\_rows CONDITION FOR 1172;  
    -> /\* Declare Exception Handlers, usually with set actions \*/  
    -> /\* usually with set actions, the following handler has two forms,  
   /\*>  one with begin .. end statements, and the other without \*/  
    -> DECLARE EXIT HANDLER FOR too\_many\_rows  
    -> BEGIN  
    -> select 'too many rows';  
    -> END;  
    -> set @c='insert into test values(?,?)';  
    -> select id into @a from test where id=v\_id;  
    -> select @a;  
    -> end$$  
Query OK, 0 rows affected (0.00 sec)  
  
mysql> delimiter ;  
mysql> call p\_test(10);  
\+---------------+  
| too many rows |  
\+---------------+  
| too many rows |  
\+---------------+  
1 row in set (0.00 sec)  
  
Query OK, 0 rows affected (0.00 sec)  
  
DECLARE ... HANDLER 语句指定处理一个或多个条件的句柄。如果这些条件之中有一个条件触发了，则指定的语句会执行，执行的语句可以是一个简单的语句如SET var\_name = value，也可以是使用BEGIN ... END格式的复杂语句。  
 句柄声明必须出现在变量或条件声明之后。  
句柄动作的值指明了句柄执行的动作：  
  
CONTINUE: 继续执行现有的程序  
EXIT: 终止执行句柄声明的BEGIN ... END语句，即使条件发生在内部的块中  
UNDO: 目前尚不支持  
  
DECLARE ... HANDLER条件的值标明了激活句柄的特定条件或类别。有如下形式：  
① mysql错误码（mysql\_error\_code）: 一种MySQL内部的标明MySQL错误代码的数字码，例如 1051 标明“unknown table”:  
DECLARE CONTINUE HANDLER FOR 1051  
   BEGIN  
     -- body of handler  
   END;  
不要使用MySQL错误代码 0 ，因为它代表了成功而不是错误条件。  
  
② SQLSTATE \[VALUE\] sqlstate\_value: 一种长度为5的字符串，标示了SQLSTATE的值，例如 '42S01' 标明 “unknown table”:  
DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02'  
   BEGIN  
     -- body of handler  
   END;  
  
不要使用以'00'开头的SQLSTATE的值，因为这些值代表了成功而不是错误条件。  
  
③ 在DECLARE ... CONDITION中声明的条件名称，条件名称可以关联MySQL错误代码或SQLSTATE的值。

/* 声明条件 */
    DECLARE no_such_table CONDITION FOR 1051;
    /* 声明异常处理 */
    DECLARE CONTINUE HANDLER FOR no_such_table
      BEGIN
        -- body of handler
      END;

/* 声明条件 */
    DECLARE no_such_table CONDITION FOR SQLSTATE '42S02';
    /* 声明异常处理 */
    DECLARE CONTINUE HANDLER FOR no_such_table
      BEGIN
        -- body of handler
      END;

④ SQLWARNING: 以'01'开头的SQLSTATE的值的简写  
DECLARE CONTINUE HANDLER FOR SQLWARNING  
   BEGIN  
     -- body of handler  
   END;  
  
⑤ NOT FOUND: 以'02'开头的SQLSTATE的值的简写，这和游标的上下文有关，用来控制当游标达到数据集的末尾时的数据库动作。如果没有任何行是可用的状态，No Data条件会伴随'02000'的SQLSTATE发生。想要检测到这个条件，需要设定一个针对NOT FOUND条件的句柄  
DECLARE CONTINUE HANDLER FOR NOT FOUND  
   BEGIN  
     -- body of handler  
   END;  
  
⑥ SQLEXCEPTION: 不以'00', '01', or '02'开头的SQLSTATE的值的简写  
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION  
   BEGIN  
     -- body of handler  
   END;  
  
创建测试表  
  
MariaDB \[test\]> create table actor(actor\_id int,first\_name varchar(25),last\_name varchar(25));  
 Query OK, 0 rows affected (0.14 sec)  
 MariaDB \[test\]> alter table actor add primary key(actor\_id);  
 Query OK, 0 rows affected (0.19 sec)                 
 Records: 0  Duplicates: 0  Warnings: 0  
MariaDB \[test\]> desc actor;  
 \+------------+-------------+------+-----+---------+-------+  
 | Field      | Type        | Null | Key | Default | Extra |  
 \+------------+-------------+------+-----+---------+-------+  
 | actor\_id   | int(11)     | NO   | PRI | NULL    |       |  
 | first\_name | varchar(25) | YES  |     | NULL    |       |  
 | last\_name  | varchar(25) | YES  |     | NULL    |       |  
 \+------------+-------------+------+-----+---------+-------+  
 3 rows in set (0.00 sec)  
MariaDB \[test\]> insert into actor(actor\_id,first\_name,last\_name) values(100,'James','Kevin');  
 Query OK, 1 row affected (0.06 sec)  
  
创建存储过程  
  
MariaDB \[test\]> delimiter //  
 MariaDB \[test\]> create procedure actor\_insert()  
     -> begin  
     -> set @x = 1;  
     -> insert into actor(actor\_id,first\_name,last\_name) values(100,'James','Kevin');  
     -> set @x = 2;  
     -> insert into actor(actor\_id,first\_name,last\_name) values(200,'John','Terry');  
     -> set @x = 3;  
     -> end//  
 Query OK, 0 rows affected (0.01 sec)  
 MariaDB \[test\]> delimiter ;  
  
调用存储过程报错  
MariaDB \[test\]> call actor\_insert();  
 ERROR 1062 (23000): Duplicate entry '100' for key 'PRIMARY'  
 MariaDB \[test\]> select @x;  
 \+------+  
 | @x   |  
 \+------+  
 |    1 |  
 \+------+  
 1 row in set (0.00 sec)  
  
改写存储过程，增加异常处理  
MariaDB \[test\]> delimiter //  
 MariaDB \[test\]> drop procedure actor\_insert;  
     -> //  
 Query OK, 0 rows affected (0.17 sec)  
MariaDB \[test\]> create procedure actor\_insert()  
     -> begin  
     -> declare continue handler for sqlstate '23000' set @x2=1;  
     -> set @x = 1;  
     -> insert into actor(actor\_id,first\_name,last\_name) values(100,'James','Kevin');  
     -> set @x = 2;  
     -> insert into actor(actor\_id,first\_name,last\_name) values(200,'John','Terry');  
     -> set @x = 3;  
     -> end//  
 Query OK, 0 rows affected (0.02 sec)  
  
**在这个例子中，声明** **SQLSTATE 23000，代表跳过表中** **重复的值，下面是文档中错误的描述**  
  
 Error: 1022 SQLSTATE: 23000 (ER\_DUP\_KEY)  
  
 Message: Can't write; duplicate key in table '%s'  
  
 MariaDB \[test\]> delimiter ;  
 MariaDB \[test\]> select \* from actor;  
 \+----------+------------+-----------+  
 | actor\_id | first\_name | last\_name |  
 \+----------+------------+-----------+  
 |      100 | James      | Kevin     |  
 \+----------+------------+-----------+  
 1 row in set (0.00 sec)  
MariaDB \[test\]> select @x;  
 \+------+  
 | @x   |  
 \+------+  
 |    1 |  
 \+------+  
 1 row in set (0.00 sec)  
  
再次调用存储过程，跳过重复主键的行  
MariaDB \[test\]> call actor\_insert();  
Query OK, 0 rows affected (0.07 sec)  
MariaDB \[test\]> select @x2;  
 \+------+  
 | @x2  |  
 \+------+  
 |    1 |  
 \+------+  
 1 row in set (0.00 sec)  
MariaDB \[test\]> select @x;  
 \+------+  
 | @x   |  
 \+------+  
 |    3 |  
 \+------+  
 1 row in set (0.00 sec)  
MariaDB \[test\]> select \* from actor;  
 \+----------+------------+-----------+  
 | actor\_id | first\_name | last\_name |  
 \+----------+------------+-----------+  
 |      100 | James      | Kevin     |  
 |      200 | John       | Terry     |  
 \+----------+------------+-----------+  
 2 rows in set (0.00 sec)  
  
定义一个HANDLER来进行事务的自动回滚操作，如在一个存储过程中发生了错误会自动对其进行回滚操作  
create procedure sp\_auto\_rollback\_demo()  
begin  
**declare exit handler for SQLEXCEPTION ROLLBACK;**  
start transaction;  
insert into b select 1;  
insert into b select 2;  
insert into b select 1;  
insert into b select 3;  
commit;  
end;  
  
增加测试报错代码  
delimiter //  
create procedure sp\_auto\_rollback\_demo()  
begin  
declare exit handler for SQLEXCEPTION BEGIN ROLLBACK; SELECT -1; END;  
start transaction;  
insert into b select 1;  
insert into b select 2;  
insert into b select 1;  
insert into b select 3;  
commit;  
select 1;  
end//  
delimiter ;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26506993/viewspace-2121368/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26506993/viewspace-2121368/