MySQL中存储过程定义条件和异常处理的示例分析

2024-04-02 19:04:59 205人浏览独家记忆

摘要

小编给大家分享一下Mysql中存储过程定义条件和异常处理的示例分析，希望大家阅读完这篇文章之后都有所收获，下面让我们一起去探讨吧！查看调用存储过程时的报错代码mysql>

小编给大家分享一下Mysql中存储过程定义条件和异常处理的示例分析，希望大家阅读完这篇文章之后都有所收获，下面让我们一起去探讨吧！

查看调用存储过程时的报错代码
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 too_many_rows CONDITION FOR 1172;
    ->
    ->
    -> 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 ;