MySQL---Error Handling 異常處理

[mtcha...@gmail.com]

　　Error Handling 異常處理
　　
　　好了，我們現下要講的是異常處理
　　
　　1. Sample Problem: Log Of Failures 問題樣例︰故障記錄
　　
　　當INSERT失敗時，我希望能將其記錄在日誌檔案中我們用來展示出錯處理的問題樣例是很普通的。我希望得到錯誤的記錄。當INSERT失敗時，我想在另一個檔案中記下這些錯誤的訊息，例如出錯時間，出錯原因等。我對插入特別感興趣的原因是它將違反外鍵關聯的約束
　　
　　2. Sample Problem: Log Of Failures (2)
　　
　　mysql> CREATE TABLE t2
　　s1 INT, PRIMARY KEY (s1))
　　engine=innodb;//
　　mysql> CREATE TABLE t3 (s1 INT, KEY (s1),
　　FOREIGN KEY (s1) REFERENCES t2 (s1))
　　engine=innodb;//
　　mysql> INSERT INTO t3 VALUES (5);//
　　...
　　ERROR 1216 (23000): Cannot add or update a child row: a foreign
key
　　constraint fails（這裡顯示的是系統的出錯訊息）
　　
　　我開始要創建一個主鍵表，以及一個外鍵表。我們使用的是InnoDB，因此外鍵關聯檢查是打開的。然後當我向外鍵表中插入非主鍵表中的值時，動作將會失敗。當然這種條件下可以很快找到錯誤號1216。
　　
　　3. Sample Problem: Log Of Failures
　　
　　CREATE TABLE error_log (error_message
　　CHAR(80))//
　　
　　下一步就是建立一個在做插入動作出錯時存儲錯誤的表。
　　
　　4. Sample Problem: Log Of Errors
　　
　　CREATE PROCEDURE p22 (parameter1 INT)
　　BEGIN
　　
　　DECLARE EXIT HANDLER FOR 1216
　　INSERT INTO error_log VALUES
　　(CONCAT('Time: ',current_date,
　　'. Foreign Key Reference Failure For
　　Value = ',parameter1));
　　INSERT INTO t3 VALUES (parameter1);
　　END;//
　　
　　上面就是我們的程式。這裡的第一個語句DECLARE
EXIT
HANDLER是用來處理異常的。意思是如果錯誤1215發生了，這個程式將會在錯誤記錄表中插入一行。EXIT意思是當動作成功提交後退出這個複合語句。
　　
　　5. Sample Problem: Log Of Errors
　　
　　CALL p22 (5) //
　　
　　調用這個存儲過程會失敗，這很正常，因為5值並沒有在主鍵表中出現。但是沒有錯誤訊息返回因為出錯處理已經包含在過程中了。t3表中沒有增加任何東西，但是error_log表中記錄下了一些訊息，這就告訴我們INSERT
into table t3動作失敗。
　　
　　DECLARE HANDLER syntax 聲明異常處理的語法
　　
　　DECLARE
　　{ EXIT | CONTINUE }
　　HANDLER FOR
　　{ error-number | { SQLSTATE error-string } | condition }
　　SQL statement
　　
　　上面就是錯誤處理的用法，也就是一段當程式出錯後自動觸發的代碼。MySQL允許兩種處理器，一種是EXIT處理，我們剛才所用的就是這種。另一種就是我們將要演示的，CONTINUE處理，它跟EXIT處理類似，不同在於它執行後，原主程式仍然繼續營運，那麼這個複合語句就沒有退場門了。
　　
　　1. DECLARE CONTINUE HANDLER example CONTINUE處理例子
　　
　　CREATE TABLE t4 (s1 int,primary key(s1));//
　　CREATE PROCEDURE p23 ()
　　BEGIN
　　DECLARE CONTINUE HANDLER
　　FOR SQLSTATE '23000' SET @x2 = 1;
　　SET @x = 1;
　　INSERT INTO t4 VALUES (1);
　　SET @x = 2;
　　INSERT INTO t4 VALUES (1);
　　SET @x = 3;
　　END;//
　　
　　這是MySQL參考手冊上的CONTINUE處理的例子，這個例子十分好，所以我把它拷貝到這裡。透過這個例子我們可以看出CONTINUE處理是如何工作的。
　　
　　2. DECLARE CONTINUE HANDLER聲明CONTINUE異常處理
　　
　　CREATE TABLE t4 (s1 int,primary key(s1));//
　　CREATE PROCEDURE p23 ()
　　BEGIN
　　DECLARE CONTINUE HANDLER
　　FOR SQLSTATE '23000' SET @x2 = 1; <--
　　SET @x = 1;
　　INSERT INTO t4 VALUES (1);
　　SET @x = 2;
　　INSERT INTO t4 VALUES (1);
　　SET @x = 3;
　　END;//
　　
　　這次我將為SQLSTATE值定義一個處理程式。還記得前面我們使用的MySQL錯誤代碼1216嗎？事實上這裡的23000SQLSTATE是更常用的，當外鍵約束出錯或主鍵約束出錯就被調用了。
　　
　　3. DECLARE CONTINUE HANDLER
　　
　　CREATE TABLE t4 (s1 int,primary key(s1));//
　　CREATE PROCEDURE p23 ()
　　BEGIN
　　DECLARE CONTINUE HANDLER
　　FOR SQLSTATE '23000' SET @x2 = 1;
　　SET @x = 1; <--
　　INSERT INTO t4 VALUES (1);
　　SET @x = 2;
　　INSERT INTO t4 VALUES (1);
　　SET @x = 3;
　　END;//
　　
　　這個存儲過程的第一個執行的語句是"SET @x = 1"。
　　
　　4. DECLARE CONTINUE HANDLER example
　　
　　CREATE TABLE t4 (s1 int,primary key(s1));//
　　CREATE PROCEDURE p23 ()
　　BEGIN
　　DECLARE CONTINUE HANDLER
　　FOR SQLSTATE '23000' SET @x2 = 1;
　　SET @x = 1;
　　INSERT INTO t4 VALUES (1);
　　SET @x = 2;
　　INSERT INTO t4 VALUES (1); <--
　　SET @x = 3;
　　END;//
　　
　　營運後值1被插入到主鍵表中。
　　
　　5. DECLARE CONTINUE HANDLER
　　
　　CREATE TABLE t4 (s1 int,primary key(s1));//
　　CREATE PROCEDURE p23 ()
　　BEGIN
　　DECLARE CONTINUE HANDLER
　　FOR SQLSTATE '23000' SET @x2 = 1;
　　SET @x = 1;
　　INSERT INTO t4 VALUES (1);
　　SET @x = 2; <--
　　INSERT INTO t4 VALUES (1);
　　SET @x = 3;
　　END;//
　　
　　然後@x的值變為2。
　　
　　6. DECLARE CONTINUE HANDLER example
　　
　　CREATE TABLE t4 (s1 int,primary key(s1));//
　　CREATE PROCEDURE p23 ()
　　BEGIN
　　DECLARE CONTINUE HANDLER
　　FOR SQLSTATE '23000' SET @x2 = 1;
　　SET @x = 1;
　　INSERT INTO t4 VALUES (1);
　　SET @x = 2;
　　INSERT INTO t4 VALUES (1); <--
　　SET @x = 3;
　　END;//
　　
　　然後程式嘗試再次往主鍵表中插入數值，但失敗了，因為主鍵有唯一性限制。
　　
　　7. DECLARE CONTINUE HANDLER example
　　
　　CREATE TABLE t4 (s1 int,primary key(s1));//
　　CREATE PROCEDURE p23 ()
　　BEGIN
　　DECLARE CONTINUE HANDLER
　　FOR SQLSTATE '23000' SET @x2 = 1; <--
　　SET @x = 1;
　　INSERT INTO t4 VALUES (1);
　　SET @x = 2;
　　INSERT INTO t4 VALUES (1);
　　SET @x = 3;
　　END;//
　　
　　由於插入失敗，錯誤處理程式被觸發，開始進行錯誤處理。下一個執行的語句是錯誤處理的語句，@x2被設為2。
　　
　　8. DECLARE CONTINUE HANDLER example
　　
　　CREATE TABLE t4 (s1 int,primary key(s1));//
　　CREATE PROCEDURE p23 ()
　　BEGIN
　　DECLARE CONTINUE HANDLER
　　FOR SQLSTATE '23000' SET @x2 = 1;
　　SET @x = 1;
　　INSERT INTO t4 VALUES (1);
　　SET @x = 2;
　　INSERT INTO t4 VALUES (1);
　　SET @x = 3; <--
　　END;//
　　
　　到這裡並沒有結束，因為這是CONTINUE異常處理。所以執行返回到失敗的插入語句之後，繼續執行將@x設定為3動作。
　　
　　9. DECLARE CONTINUE HANDLER example
　　
　　mysql> CALL p23()//
　　Query OK, 0 rows affected (0.00 sec)
　　mysql> SELECT @x, @x2//
　　
　　+------+------+
　　| @x | @x2 |
　　+------+------+
　　| 3 | 1 |
　　+------+------+
　　1 row in set (0.00 sec)
　　
　　營運過程後我們觀察@x的值，很確定的可以知道是3，觀察@x2的值，為1。從這裡可以判斷程式營運無誤，完全按照我們的思路進行。大家可以花點時間去調整錯誤處理器，讓檢查放在語句段的首部，而不是放在可能出現錯誤的地方，雖然那樣看起來程式很紊亂，跳來跳去的感覺。但是這樣的代碼很安全也很清楚。
　　
　　1. DECLARE CONDITION
　　
　　CREATE PROCEDURE p24 ()
　　BEGIN
　　DECLARE `Constraint Violation`
　　CONDITION FOR SQLSTATE '23000';
　　DECLARE EXIT HANDLER FOR
　　`Constraint Violation` ROLLBACK;
　　START TRANSACTION;
　　INSERT INTO t2 VALUES (1);
　　INSERT INTO t2 VALUES (1);
　　COMMIT;
　　END; //
　　
　　這是另外一個錯誤處理的例子，在前面的基礎上修改的。事實上你可給SQLSTATE或者錯誤代碼其他的名字，你就可以在處理中使用自己定義的名字了。下面看它是怎么實現的︰我把表t2定義為InnoDB表，所以對這個表的插入操作都會ROLLBACK（回滾），ROLLBACK（回滾事務）也是恰好會發生的。因為對主鍵插入兩個同樣的值會導致SQLSTATE
23000錯誤發生，這裡SQLSTATE 23000是約束錯誤。
　　
　　2. DECLARE CONDITION聲明條件
　　
　　CREATE PROCEDURE p24 ()
　　BEGIN
　　DECLARE `Constraint Violation`
　　CONDITION FOR SQLSTATE '23000';
　　DECLARE EXIT HANDLER FOR
　　`Cons