mysql存儲(chǔ)過(guò)程之錯(cuò)誤處理實(shí)例詳解

本文實(shí)例講述了mysql存儲(chǔ)過(guò)程之錯(cuò)誤處理。分享給大家供大家參考，具體如下：

當(dāng)存儲(chǔ)過(guò)程中發(fā)生錯(cuò)誤時(shí)，重要的是適當(dāng)處理它，例如：繼續(xù)或退出當(dāng)前代碼塊的執(zhí)行，并發(fā)出有意義的錯(cuò)誤消息。其中mysql提供了一種簡(jiǎn)單的方法來(lái)定義處理從一般條件(如警告或異常)到特定條件(例如特定錯(cuò)誤代碼)的處理程序。完事我們來(lái)使用DECLARE HANDLER語(yǔ)句來(lái)嘗試聲明一個(gè)處理程序，先來(lái)看語(yǔ)法：

DECLARE action HANDLER FOR condition_value statement;

上述sql中，如果條件的值與condition_value匹配，則MySQL將執(zhí)行statement，并根據(jù)該操作繼續(xù)或退出當(dāng)前的代碼塊。其中，操作(action)接受以下值之一：

• CONTINUE：繼續(xù)執(zhí)行封閉代碼塊(BEGIN ... END)。
• EXIT：處理程序聲明封閉代碼塊的執(zhí)行終止。

condition_value指定一個(gè)特定條件或一類(lèi)激活處理程序的條件。condition_value接受以下值之一：

• 一個(gè)MySQL錯(cuò)誤代碼。
• 標(biāo)準(zhǔn)SQLSTATE值或者它可以是SQLWARNING，NOTFOUND或SQLEXCEPTION條件，這是SQLSTATE值類(lèi)的簡(jiǎn)寫(xiě)。NOTFOUND條件用于游標(biāo)或SELECT INTO variable_list語(yǔ)句。
• 與MySQL錯(cuò)誤代碼或SQLSTATE值相關(guān)聯(lián)的命名條件。

最重要的是，上述sql可以是一個(gè)簡(jiǎn)單的語(yǔ)句或由BEGIN和END關(guān)鍵字包圍的復(fù)合語(yǔ)句。介紹完事之后，咱們來(lái)看幾個(gè)聲明處理程序的例子，首先是當(dāng)程序發(fā)生錯(cuò)誤時(shí)，將has_error變量的值設(shè)置為1并繼續(xù)執(zhí)行的例子：

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET has_error = 1;

再來(lái)看當(dāng)發(fā)生錯(cuò)誤時(shí)，回滾上一個(gè)操作，發(fā)出錯(cuò)誤消息，并退出當(dāng)前代碼塊。 如果在存儲(chǔ)過(guò)程的BEGIN END塊中聲明它，則會(huì)立即終止存儲(chǔ)過(guò)程：

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
SELECT 'An error has occurred, operation rollbacked and the stored procedure was terminated';
END;

以下處理程序的意思是，如果沒(méi)有更多的行要提取，在光標(biāo)或select into語(yǔ)句的情況下，將no_row_found變量的值設(shè)置為1并繼續(xù)執(zhí)行：

DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_row_found = 1;

以下處理程序如果發(fā)生重復(fù)的鍵錯(cuò)誤，則會(huì)發(fā)出MySQL錯(cuò)誤1062。 它發(fā)出錯(cuò)誤消息并繼續(xù)執(zhí)行：

DECLARE CONTINUE HANDLER FOR 1062
SELECT 'Error, duplicate key occurred';

上面這些實(shí)例可能有點(diǎn)抽象，咱們廢話不多說(shuō)，先來(lái)創(chuàng)建一個(gè)名為article_tags的新表，來(lái)具體操作下：

USE testdb;
CREATE TABLE article_tags(
  article_id INT,
  tag_id   INT,
  PRIMARY KEY(article_id,tag_id)
);

其中呢，article_tags表存儲(chǔ)文章和標(biāo)簽之間的關(guān)系。每篇文章可能有很多標(biāo)簽，反之亦然。 為了簡(jiǎn)單起見(jiàn)，我們不會(huì)在article_tags表中創(chuàng)建文章(article)表和標(biāo)簽(tags)表以及外鍵。

完事呢，我們來(lái)創(chuàng)建一個(gè)存儲(chǔ)過(guò)程，將文章的id和標(biāo)簽的id插入到article_tags表中：

USE testdb;
DELIMITER $$
CREATE PROCEDURE insert_article_tags(IN article_id INT, IN tag_id INT)
BEGIN
 DECLARE CONTINUE HANDLER FOR 1062
 SELECT CONCAT('duplicate keys (',article_id,',',tag_id,') found') AS msg;
 -- insert a new record into article_tags
 INSERT INTO article_tags(article_id,tag_id)
 VALUES(article_id,tag_id);
 -- return tag count for the article
 SELECT COUNT(*) FROM article_tags;
END$$
DELIMITER ;

然后呢，我們通過(guò)調(diào)用insert_article_tags存儲(chǔ)過(guò)程，為文章ID為1添加標(biāo)簽ID：1,2和3，如下所示：

CALL insert_article_tags(1,1);
CALL insert_article_tags(1,2);
CALL insert_article_tags(1,3);

我們?cè)賴(lài)L試插入一個(gè)重復(fù)的鍵來(lái)檢查處理程序是否真的被調(diào)用：

CALL insert_article_tags(1,3);

執(zhí)行上面查詢(xún)語(yǔ)句，得到以下結(jié)果：

mysql> CALL insert_article_tags(1,3);
+----------------------------+
| msg            |
+----------------------------+
| duplicate keys (1,3) found |
+----------------------------+
1 row in set
+----------+
| COUNT(*) |
+----------+
|    3 |
+----------+
1 row in set
Query OK, 0 rows affected

執(zhí)行后會(huì)收到一條錯(cuò)誤消息。 但是，由于我們將處理程序聲明為CONTINUE處理程序，所以存儲(chǔ)過(guò)程繼續(xù)執(zhí)行。因此，最后獲得了文章的標(biāo)簽計(jì)數(shù)值為：3。來(lái)看個(gè)圖：

但是如果將處理程序聲明中的CONTINUE更改為EXIT，那么將只會(huì)收到一條錯(cuò)誤消息。如下查詢(xún)語(yǔ)句：

DELIMITER $$
CREATE PROCEDURE insert_article_tags_exit(IN article_id INT, IN tag_id INT)
BEGIN
 DECLARE EXIT HANDLER FOR SQLEXCEPTION 
 SELECT 'SQLException invoked';
 DECLARE EXIT HANDLER FOR 1062 
    SELECT 'MySQL error code 1062 invoked';
 DECLARE EXIT HANDLER FOR SQLSTATE '23000'
 SELECT 'SQLSTATE 23000 invoked';
 -- insert a new record into article_tags
 INSERT INTO article_tags(article_id,tag_id)
  VALUES(article_id,tag_id);
 -- return tag count for the article
 SELECT COUNT(*) FROM article_tags;
END $$
DELIMITER ;

執(zhí)行上面查詢(xún)語(yǔ)句，得到以下結(jié)果：

mysql> CALL insert_article_tags_exit(1,3);
+-------------------------------+
| MySQL error code 1062 invoked |
+-------------------------------+
| MySQL error code 1062 invoked |
+-------------------------------+
1 row in set
Query OK, 0 rows affected

來(lái)看個(gè)圖感受下：

如果我們使用多個(gè)處理程序來(lái)處理錯(cuò)誤，MySQL將調(diào)用最特定的處理程序來(lái)處理錯(cuò)誤。這就涉及到優(yōu)先級(jí)的問(wèn)題了，我們來(lái)具體看下。

我們知道錯(cuò)誤總是映射到一個(gè)MySQL錯(cuò)誤代碼，因?yàn)樵贛ySQL中它是最具體的。 SQLSTATE可以映射到許多MySQL錯(cuò)誤代碼，因此它不太具體。 SQLEXCPETION或SQLWARNING是SQLSTATES類(lèi)型值的縮寫(xiě)，因此它是最通用的。假設(shè)在insert_article_tags_3存儲(chǔ)過(guò)程中聲明三個(gè)處理程序，如下所示：

DELIMITER $$
CREATE PROCEDURE insert_article_tags_3(IN article_id INT, IN tag_id INT)
BEGIN
 DECLARE EXIT HANDLER FOR 1062 SELECT 'Duplicate keys error encountered';
 DECLARE EXIT HANDLER FOR SQLEXCEPTION SELECT 'SQLException encountered';
 DECLARE EXIT HANDLER FOR SQLSTATE '23000' SELECT 'SQLSTATE 23000';
 -- insert a new record into article_tags
 INSERT INTO article_tags(article_id,tag_id)
 VALUES(article_id,tag_id);
 -- return tag count for the article
 SELECT COUNT(*) FROM article_tags;
END $$
DELIMITER ;

然后我們嘗試通過(guò)調(diào)用存儲(chǔ)過(guò)程將重復(fù)的鍵插入到article_tags表中：

CALL insert_article_tags_3(1,3);

如下，可以看到MySQL錯(cuò)誤代碼處理程序被調(diào)用：

mysql> CALL insert_article_tags_3(1,3);
+----------------------------------+
| Duplicate keys error encountered |
+----------------------------------+
| Duplicate keys error encountered |
+----------------------------------+
1 row in set
Query OK, 0 rows affected

完事之后，咱們?cè)賮?lái)看下使用命名錯(cuò)誤條件。從錯(cuò)誤處理程序聲明開(kāi)始，如下：

DECLARE EXIT HANDLER FOR 1051 SELECT 'Please create table abc first';
SELECT * FROM abc;

1051號(hào)是什么意思？ 想象一下，你有一個(gè)大的存儲(chǔ)過(guò)程代碼使用了好多類(lèi)似這樣的數(shù)字; 這將成為維護(hù)代碼的噩夢(mèng)。幸運(yùn)的是，MySQL為我們提供了聲明一個(gè)命名錯(cuò)誤條件的DECLARE CONDITION語(yǔ)句，它與條件相關(guān)聯(lián)。DECLARE CONDITION語(yǔ)句的語(yǔ)法如下：

DECLARE condition_name CONDITION FOR condition_value;

condition_value可以是MySQL錯(cuò)誤代碼，例如：1015或SQLSTATE值。 condition_value由condition_name表示。聲明后，可以參考condition_name，而不是參考condition_value。所以可以重寫(xiě)上面的代碼如下：

DECLARE table_not_found CONDITION for 1051;
DECLARE EXIT HANDLER FOR table_not_found SELECT 'Please create table abc first';
SELECT * FROM abc;

這段代碼比以前的代碼顯然更可讀，不過(guò)我們要注意，條件聲明必須出現(xiàn)在處理程序或游標(biāo)聲明之前。

好啦，這次就到這里了。

更多關(guān)于MySQL相關(guān)內(nèi)容感興趣的讀者可查看本站專(zhuān)題：《MySQL存儲(chǔ)過(guò)程技巧大全》、《MySQL常用函數(shù)大匯總》、《MySQL日志操作技巧大全》、《MySQL事務(wù)操作技巧匯總》及《MySQL數(shù)據(jù)庫(kù)鎖相關(guān)技巧匯總》

希望本文所述對(duì)大家MySQL數(shù)據(jù)庫(kù)計(jì)有所幫助。