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 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> 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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- MySQL儲存過程的異常處理方法MySql儲存過程
- MySQL定義異常和異常處理詳解MySql
- [MySQL光速入門]017 儲存過程中的"異常處理"MySql儲存過程
- (11)mysql 中的條件定義、處理MySql
- MySQL儲存過程-->通過遊標遍歷和異常處理遷移資料到歷史表MySql儲存過程
- 異常處理過程
- oracle 儲存過程遊標中處理並記錄異常Oracle儲存過程
- MySQL 儲存過程空結果集錯誤Error 1329 No data 的異常處理MySql儲存過程Error
- MySQL儲存過程中捕獲異常的方法MySql儲存過程
- 儲存過程——異常捕獲&列印異常資訊儲存過程
- 儲存過程WHERE條件不生效儲存過程
- 某次BW 異常處理過程
- Mysql儲存過程 變數，條件，迴圈語句用法MySql儲存過程變數
- MySQL儲存過程詳解 mysql 儲存過程MySql儲存過程
- MySQL遊標和異常處理MySql
- mysql 儲存過程和事件排程MySql儲存過程事件
- Mysql 的儲存過程和儲存函式MySql儲存過程儲存函式
- MySQL異常處理MySql
- mysql-定時呼叫儲存過程MySql儲存過程
- (9)mysql 中的儲存過程和自定義函式MySql儲存過程函式
- springboot下新增全域性異常處理和自定義異常處理Spring Boot
- MySQL儲存過程詳解 mysql 儲存過程linkMySql儲存過程
- MySQL自定義函式與儲存過程MySql函式儲存過程
- mysql和orcale的儲存過程和儲存函式MySql儲存過程儲存函式
- MySQL儲存過程中處理陣列 UDF程式碼分享MySql儲存過程陣列
- MySQL 儲存過程和函式MySql儲存過程函式
- MySQL儲存過程和函式MySql儲存過程函式
- restframework 異常處理及自定義異常RESTFramework
- Mysql 儲存過程MySql儲存過程
- mysql 儲存過程中變數的定義與賦值操作MySql儲存過程變數賦值
- MySQL儲存過程的建立和使用MySql儲存過程
- MySql儲存過程—2、第一個MySql儲存過程的建立MySql儲存過程
- MySQL 儲存過程中事務sql異常回滾MySql儲存過程
- MySQL版本對varchar的定義和限制條件MySql
- mysql儲存過程整理MySql儲存過程
- MySQL之儲存過程MySql儲存過程
- [MYSQL -23儲存過程]MySql儲存過程
- MYSQL儲存過程管理MySql儲存過程