探讨 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 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 ; 看完了这篇文章，相信你对“MySQL中存储过程定义条件和异常处理的示例分析”有了一定的了解，如果想了解更多相关知识，感谢各位的阅读！ （编辑：银川站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
