MySQL中存儲過程定義條件和異常處理的示例分析

小編給大家分享一下MySQL中存儲過程定義條件和異常處理的示例分析，希望大家閱讀完這篇文章之后都有所收獲，下面讓我們一起去探討吧！

查看調(diào)用存儲過程時的報錯代碼
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

去官網(wǎng)查看對應的存儲過程異常代碼
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 語句指定處理一個或多個條件的句柄。如果這些條件之中有一個條件觸發(fā)了，則指定的語句會執(zhí)行，執(zhí)行的語句可以是一個簡單的語句如SET var_name = value，也可以是使用BEGIN ... END格式的復雜語句。
句柄聲明必須出現(xiàn)在變量或條件聲明之后。
句柄動作的值指明了句柄執(zhí)行的動作：

CONTINUE: 繼續(xù)執(zhí)行現(xiàn)有的程序
EXIT: 終止執(zhí)行句柄聲明的BEGIN ... END語句，即使條件發(fā)生在內(nèi)部的塊中
UNDO: 目前尚不支持

DECLARE ... HANDLER條件的值標明了激活句柄的特定條件或類別。有如下形式：
① mysql錯誤碼（mysql_error_code）: 一種MySQL內(nèi)部的標明MySQL錯誤代碼的數(shù)字碼，例如 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中聲明的條件名稱，條件名稱可以關(guān)聯(lián)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的值的簡寫，這和游標的上下文有關(guān)，用來控制當游標達到數(shù)據(jù)集的末尾時的數(shù)據(jù)庫動作。如果沒有任何行是可用的狀態(tài)，No Data條件會伴隨'02000'的SQLSTATE發(fā)生。想要檢測到這個條件，需要設定一個針對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;

創(chuàng)建測試表

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)

創(chuàng)建存儲過程

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 ;

調(diào)用存儲過程報錯
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)

再次調(diào)用存儲過程，跳過重復主鍵的行
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來進行事務的自動回滾操作，如在一個存儲過程中發(fā)生了錯誤會自動對其進行回滾操作
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中存儲過程定義條件和異常處理的示例分析”有了一定的了解，如果想了解更多相關(guān)知識，歡迎關(guān)注億速云行業(yè)資訊頻道，感謝各位的閱讀！