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)
沒有解決問題,試試搜尋本站其他內容

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *

這個網站採用 Akismet 服務減少垃圾留言。進一步了解 Akismet 如何處理網站訪客的留言資料