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

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

這邊就來看一下關於 Store Procedure 的例外處理 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

概念很簡單就是在 Store Procedure 裡面發生錯誤時需要怎麼來處裡.

在使用 Cursor – http://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;
    • SQLSTATE [VALUE] sqlstate_value
      當發生錯誤 23000 (ER_DUP_KEY : Can’t write; duplicate key in table ‘%s’) , MariaDB Error Codes 請參考 – https://mariadb.com/kb/en/mariadb-error-codes/ 時,有相同的 Primary Key 時把 @x2 設定為 1.
    • condition_name
      可以使用以下的方式來定義.
      DECLARE duplicate_key CONDITION FOR SQLSTATE ‘23000’;
      DECLARE CONTINUE HANDLER FOR duplicate_key SET @x2 = 1;
    • 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)
    • 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
    • SQLEXCEPTION
      所有 SQLSTATE 不屬於 00 , 01 , 02 開頭的錯誤.
  • INSERT INTO test VALUES (1);
    第二行時故意讓 Primary Key 與前一筆相同.

測試一下.

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)

通常我會希望把錯誤記錄起來到另外一個 Table .這時候可以使用 GET DIAGNOSTICS ,用法如下主要抓取目前的 RETURNED_SQLSTATE, MYSQL_ERRNO 與 MESSAGE_TEXT 資訊.

GET [CURRENT] DIAGNOSTICS
{
    statement_property
    [, statement_property] ... 
  | CONDITION condition_number
    condition_property
    [, condition_property] ...
}

先建立要儲存的資料表.

MariaDB [testdb]> CREATE TABLE SQL_Error_Record (K1 INT(11) NOT NULL auto_increment, SQL_STATUS VARCHAR(50) , ERROR_NUM VARCHAR(50) , CONTENT VARCHAR(500), T1 TIMESTAMP , PRIMARY KEY (K1));
Query OK, 0 rows affected (0.011 sec)

MariaDB [testdb]> DESCRIBE SQL_Error_Record;
+------------+--------------+------+-----+---------------------+-------------------------------+
| Field      | Type         | Null | Key | Default             | Extra                         |
+------------+--------------+------+-----+---------------------+-------------------------------+
| K1         | int(11)      | NO   | PRI | NULL                | auto_increment                |
| SQL_STATUS | varchar(50)  | YES  |     | NULL                |                               |
| ERROR_NUM  | varchar(50)  | YES  |     | NULL                |                               |
| CONTENT    | varchar(500) | YES  |     | NULL                |                               |
| T1         | timestamp    | NO   |     | current_timestamp() | on update current_timestamp() |
+------------+--------------+------+-----+---------------------+-------------------------------+
5 rows in set (0.005 sec)

建立 Procedure.

DROP PROCEDURE IF EXISTS handlerdemo;

DELIMITER //

CREATE PROCEDURE handlerdemo ( )
     BEGIN
       DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' 
       BEGIN  
         GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,@errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
         INSERT INTO testdb.SQL_Error_Record (SQL_STATUS , ERROR_NUM , CONTENT) VALUE(@sqlstate, @errno, @text);
       END;
       SET @x = 1;
       INSERT INTO test VALUES (1);
       SET @x = 2;
       INSERT INTO test VALUES (1);
       SET @x = 3;
     END;
     //

DELIMITER ;

Procedure 說明,主要差別就在 DECLARE HANDLER 這一段:

  • DECLARE CONTINUE HANDLER FOR SQLSTATE ‘23000’
    當發生錯誤 23000 (ER_DUP_KEY : Can’t write; duplicate key in table ‘%s’) , 做以下 BEGIN END; 裡面的敘述.
  • GET DIAGNOSTICS CONDITION 1 @sqlstate = RETURNED_SQLSTATE,@errno = MYSQL_ERRNO, @text = MESSAGE_TEXT;
    使用 GET DIAGNOSTICS ,抓取目前的 RETURNED_SQLSTATE, MYSQL_ERRNO 與 MESSAGE_TEXT 資訊,並儲存到這三個變數 @sqlstate ,@errno , @text.

    GET [CURRENT] DIAGNOSTICS
    {
        statement_property
        [, statement_property] ... 
      | CONDITION condition_number
        condition_property
        [, condition_property] ...
    }
    
    statement_property:
        variable = statement_property_name
    
    condition_property:
        variable  = condition_property_name
    
    statement_property_name:
        NUMBER
      | ROW_COUNT
    
    condition_property_name:
        CLASS_ORIGIN
      | SUBCLASS_ORIGIN
      | RETURNED_SQLSTATE
      | MESSAGE_TEXT
      | MYSQL_ERRNO
      | CONSTRAINT_CATALOG
      | CONSTRAINT_SCHEMA
      | CONSTRAINT_NAME
      | CATALOG_NAME
      | SCHEMA_NAME
      | TABLE_NAME
      | COLUMN_NAME
      | CURSOR_NAME
    

先清除 test 資料表裡面的資料.

MariaDB [testdb]> TRUNCATE test;
Query OK, 0 rows affected (0.012 sec)

測試一下,錯誤的確被記錄到 SQL_Error_Record 資料表.

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

MariaDB [testdb]> SELECT * FROM SQL_Error_Record;
+----+------------+-----------+---------------------------------------+---------------------+
| K1 | SQL_STATUS | ERROR_NUM | CONTENT                               | T1                  |
+----+------------+-----------+---------------------------------------+---------------------+
|  1 | 23000      | 1062      | Duplicate entry '1' for key 'PRIMARY' | 2020-06-03 15:31:50 |
+----+------------+-----------+---------------------------------------+---------------------+
1 row in set (0.001 sec)
沒有解決問題,試試搜尋本站其他內容

發佈留言

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

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