SQL Store Procedure 的例外處理 ( DECLARE Error Handler )

測試環境為 CentOS 8 x86_64 (虛擬機)

例外處理 DECLARE Error Handler 的概念很簡單就是在 Store Procedure 裡面發生錯誤時需要怎麼來處裡,如在使用 Cursor – https://benjr.tw/102792 時利用 NOT FOUND 來決定是否要繼續處理資料.

範例參考 – https://mariadb.com/kb/en/declare-handler/

先來建立需要的資料表.

[root@localhost ~]# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 5.5.60-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> CREATE DATABASE testdb;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> USE testdb;
Database changed

建立資料表 test ,欄位為 s1 (為 Primary Key).

MariaDB [testdb]> CREATE TABLE test (s1 INT, PRIMARY KEY (s1));
Query OK, 0 rows affected (0.012 sec)

建立測試用 handlerdemo PROCEDURE

DROP PROCEDURE IF EXISTS handlerdemo;

DELIMITER //

CREATE PROCEDURE handlerdemo ( )
     BEGIN
       DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x2 = 1;
       SET @x = 1;
       INSERT INTO test VALUES (1);
       SET @x = 2;
       INSERT INTO test VALUES (1);
       SET @x = 3;
     END;
     //

DELIMITER ;

Procedure 說明:

• DROP PROCEDURE IF EXISTS handlerdemo;
  如果宣告過相同的 Procedure 就刪除.
• DELIMITER // //
  Mysql (MariaDB) 透過分號 “;” 來表示這 SQL Statement 的敘述完成,但 Procedure 裡面可能會有多筆 SQL 敘述 ,這時候可以使用 DELIMITER // … // ,來表示裡面是一整個敘述.
• DELIMITER ;
  把結束符號修改回為 分號 “;” .
• CREATE PROCEDURE handlerdemo ( )
  建立一個 Procedure ,不使用傳入(IN),傳回值(OUT)或是 INOUT.
• BEGIN END
  Procedure 程式宣告必須包含在裡面.
• DECLARE CONTINUE HANDLER FOR SQLSTATE ‘23000’ SET @x2 = 1;
  看需求可以一次定義多個 HANDLER ,這邊只定義了當發生 SQLSTATE 代碼為 23000 (多種錯誤會共用相同的 SQLSTAT ,這邊範例是當發生相同的 Primary Key duplicate key 時的錯誤) 把 @x2 設定為 1. 下面詳細說明.
• INSERT INTO test VALUES (1);
  第二行時故意讓 Primary Key 與前一筆相同.

這邊就來看一下關於例外處理 DECLARE Error Handler 的使用方式, DECLARE … HANDLER 宣告方式如下:

MariaDB [(none)]> help DECLARE HANDLER;
Name: 'DECLARE HANDLER'
Description:
Syntax:
DECLARE handler_action HANDLER
    FOR condition_value [, condition_value] ...
    statement

handler_action: 
    CONTINUE
  | EXIT
  | UNDO

condition_value:
    mysql_error_code
  | SQLSTATE [VALUE] sqlstate_value
  | condition_name
  | SQLWARNING
  | NOT FOUND
  | SQLEXCEPTION

• handler_action
  可以使用 CONTINUE , EXIT 或是 UNDO , 區間範圍為 BEGIN … END .
• condition_value
  1. mysql_error_code
     每一種錯誤都有屬於自己的 Error Code ,關於 MariaDB Error Codes 請參考 – https://mariadb.com/kb/en/mariadb-error-codes/
  2. SQLSTATE [VALUE] sqlstate_value
     另外一種就是 SQLSTATE , 這個是同質性的錯誤碼,以前面的範例 23000 -多種錯誤會共用相同的 SQLSTAT ,這邊範例是當發生相同的 Primary Key duplicate key 時的錯誤, 關於 MariaDB SQLSTATE 請參考 – https://mariadb.com/kb/en/mariadb-error-codes/
  3. condition_name
     可以使用以下的方式來定義.
     DECLARE duplicate_key CONDITION FOR SQLSTATE ‘23000’;
     DECLARE CONTINUE HANDLER FOR duplicate_key SET @x2 = 1;
  4. SQLWARNING
     這代表所有 SQLSTATE 01 開頭的錯誤,如:
     01000 ER_WARN_TOO_FEW_RECORDS (Row %ld doesn’t contain data for all columns)
     01000 ER_WARN_TOO_MANY_RECORDS (Row %ld was truncated; it contained more data than there were input columns
     01000 WARN_DATA_TRUNCATED (Data truncated for column ‘%s’ at row %ld)
     01000 ER_SP_UNINIT_VAR (Referring to uninitialized variable %s)
     01000 ER_SIGNAL_WARN (Unhandled user-defined warning condition)
  5. NOT FOUND
     這代表所有 SQLSTATE 02 開頭的錯誤,如:
     02000 ER_SP_FETCH_NO_DATA No data – zero rows fetched, selected, or processed
     02000 ER_SIGNAL_NOT_FOUND Unhandled user-defined not found condition
  6. SQLEXCEPTION
     所有 SQLSTATE 不屬於 00 , 01 , 02 開頭的錯誤.

測試一下.

變數 @x 與 @x2 沒定義過所以都是 NULL.

MariaDB [testdb]> SELECT @x,@x2;
+------+------+
| @x   | @x2  |
+------+------+
| NULL | NULL |
+------+------+
1 row in set (0.000 sec)

呼叫剛剛寫好的 Store Procedure.

MariaDB [testdb]> CALL handlerdemo( );
Query OK, 0 rows affected (0.003 sec)

可以看到 @x 為 3 (執行到最後) 並且 @x2 被設定為 1 (因為發生 Duplicate Key)

MariaDB [testdb]> SELECT @x,@x2;
+------+------+
| @x   | @x2  |
+------+------+
|    3 |    1 |
+------+------+
1 row in set (0.001 sec)