MySQL儲存過程錯誤處理

本教學將向您展示如何使用MySQL處理程式來處理在儲存過程中遇到的異常或錯誤。

當儲存過程中發生錯誤時，重要的是適當處理它，例如：繼續或退出當前程式碼塊的執行，並行出有意義的錯誤訊息。

MySQL提供了一種簡單的方法來定義處理從一般條件(如警告或異常)到特定條件(例如特定錯誤程式碼)的處理程式。

宣告處理程式

要宣告一個處理程式，您可以使用DECLARE HANDLER語句如下：

DECLARE action HANDLER FOR condition_value statement;

如果條件的值與condition_value匹配，則MySQL將執行statement，並根據該操作繼續或退出當前的程式碼塊。

操作(action)接受以下值之一：

CONTINUE：繼續執行封閉程式碼塊(BEGIN ... END)。
EXIT：處理程式宣告封閉程式碼塊的執行終止。

condition_value指定一個特定條件或一類啟用處理程式的條件。condition_value接受以下值之一：

一個MySQL錯誤程式碼。
標準SQLSTATE值或者它可以是SQLWARNING，NOTFOUND或SQLEXCEPTION條件，這是SQLSTATE值類的簡寫。NOTFOUND條件用於游標或SELECT INTO variable_list語句。
與MySQL錯誤程式碼或SQLSTATE值相關聯的命名條件。

該語句可以是一個簡單的語句或由BEGIN和END關鍵字包圍的複合語句。

MySQL錯誤處理範例

我們來看幾個宣告處理程式的例子。

以下處理程式意味著如果發生錯誤，則將has_error變數的值設定為1並繼續執行。

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET has_error = 1;

以下是另一個處理程式，如果發生錯誤，回滾上一個操作，發出錯誤訊息，並退出當前程式碼塊。如果在儲存過程的BEGIN END塊中宣告它，則會立即終止儲存過程。

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

以下處理程式如果沒有更多的行要提取，在游標或SELECT INTO語句的情況下，將no_row_found變數的值設定為1並繼續執行。

DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_row_found = 1;

以下處理程式如果發生重複的鍵錯誤，則會發出MySQL錯誤1062。它發出錯誤訊息並繼續執行。

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

儲存過程中的MySQL處理程式範例

首先，為了更好地演示，我們建立一個名為article_tags的新表：

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

article_tags表儲存文章和標籤之間的關係。每篇文章可能有很多標籤，反之亦然。為了簡單起見，我們不會在article_tags表中建立文章(article)表和標籤(tags)表以及外來鍵。

接下來，建立一個儲存過程，將文章的id和標籤的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 ;

然後，通過呼叫insert_article_tags儲存過程，為文章ID為1新增標籤ID：1,2和3，如下所示：

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

之後，嘗試插入一個重複的鍵來檢查處理程式是否真的被呼叫。

CALL insert_article_tags(1,3);

執行上面查詢語句，得到以下結果 -

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

執行後會收到一條錯誤訊息。但是，由於我們將處理程式宣告為CONTINUE處理程式，所以儲存過程繼續執行。因此，最後獲得了文章的標籤計數值為：3。

如果將處理程式宣告中的CONTINUE更改為EXIT，那麼將只會收到一條錯誤訊息。如下查詢語句 -

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 ;

執行上面查詢語句，得到以下結果 -

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

MySQL處理程式優先順序

如果使用多個處理程式來處理錯誤，MySQL將呼叫最特定的處理程式來處理錯誤。

錯誤總是對映到一個MySQL錯誤程式碼，因為在MySQL中它是最具體的。 SQLSTATE可以對映到許多MySQL錯誤程式碼，因此它不太具體。 SQLEXCPETION或SQLWARNING是SQLSTATES型別值的縮寫，因此它是最通用的。

假設在insert_article_tags_3儲存過程中宣告三個處理程式，如下所示：

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 ;

我們嘗試通過呼叫儲存過程將重複的鍵插入到article_tags表中：

CALL insert_article_tags_3(1,3);

如下，可以看到MySQL錯誤程式碼處理程式被呼叫。

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

使用命名錯誤條件

從錯誤處理程式宣告開始，如下 -

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

1051號是什麼意思？想象一下，你有一個大的儲存過程程式碼使用了好多類似這樣的數位; 這將成為維護程式碼的噩夢。

幸運的是，MySQL為我們提供了宣告一個命名錯誤條件的DECLARE CONDITION語句，它與條件相關聯。

DECLARE CONDITION語句的語法如下：

DECLARE condition_name CONDITION FOR condition_value;

condition_value可以是MySQL錯誤程式碼，例如：1015或SQLSTATE值。 condition_value由condition_name表示。

宣告後，可以參考condition_name，而不是參考condition_value。

所以可以重寫上面的程式碼如下：

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

這段程式碼比以前的程式碼顯然更可讀。

請注意，條件宣告必須出現在處理程式或游標宣告之前。