MySQL 5.0 新特性教程存儲過程:第四講

2024-07-24 12:57:34

字體：大中小

供稿：網(wǎng)友

作者：MySQL AB;翻譯:陳朋奕

Error Handling 異常處理

　　好了，我們現(xiàn)在要講的是異常處理

1. Sample PRoblem: Log Of Failures 問題樣例：故障記錄

　　當(dāng)INSERT失敗時，我希望能將其記錄在日志文件中我們用來展示出錯處理的問題樣例是很
普通的。我希望得到錯誤的記錄。當(dāng)INSERT失敗時，我想在另一個文件中記下這些錯誤的
信息，例如出錯時間，出錯原因等。我對插入特別感興趣的原因是它將違反外鍵關(guān)聯(lián)的約束

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（這里顯示的是系統(tǒng)的出錯信息）

　　我開始要創(chuàng)建一個主鍵表，以及一個外鍵表。我們使用的是InnoDB，因此外鍵關(guān)聯(lián)檢查是打
開的。然后當(dāng)我向外鍵表中插入非主鍵表中的值時，動作將會失敗。當(dāng)然這種條件下可以很
快找到錯誤號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發(fā)生了，這個程序?qū)阱e誤記錄表中插入一行。EXIT意思是當(dāng)動作成功提交后退出這個復(fù)合語句。

5. Sample Problem: Log Of Errors

CALL p22 (5) //

　　調(diào)用這個存儲過程會失敗，這很正常，因為5值并沒有在主鍵表中出現(xiàn)。但是沒有錯誤信息
返回因為出錯處理已經(jīng)包含在過程中了。t3表中沒有增加任何東西，但是error_log表中記錄
下了一些信息，這就告訴我們INSERT into table t3動作失敗。

DECLARE HANDLER syntax 聲明異常處理的語法

DECLARE
{ EXIT | CONTINUE }
HANDLER FOR
{ error-number | { SQLSTATE error-string } | condition }
SQL statement

　　上面就是錯誤處理的用法，也就是一段當(dāng)程序出錯后自動觸發(fā)的代碼。MySQL允許兩種處理器，一種是EXIT處理，我們剛才所用的就是這種。另一種就是我們將要演示的，CONTINUE處理，它跟EXIT處理類似，不同在于它執(zhí)行后，原主程序仍然繼續(xù)運行，那么這個復(fù)合語句就沒有出口了。

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是更常用的，當(dāng)外鍵約束出錯或主鍵約束出錯就被調(diào)用了。

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;//

　　這個存儲過程的第一個執(zhí)行的語句是"SET @x = 1"。

4. DECLARE CONTINUE HANDLER example

　　運行后值1被插入到主鍵表中。

5. DECLARE CONTINUE HANDLER

　　然后@x的值變?yōu)?。

6. DECLARE CONTINUE HANDLER example

　　然后程序嘗試再次往主鍵表中插入數(shù)值，但失敗了，因為主鍵有唯一性限制。

7. DECLARE CONTINUE HANDLER example

　　由于插入失敗，錯誤處理程序被觸發(fā)，開始進(jìn)行錯誤處理。下一個執(zhí)行的語句是錯誤處理的語句，@x2被設(shè)為2。

8. DECLARE CONTINUE HANDLER example

　　到這里并沒有結(jié)束，因為這是CONTINUE異常處理。所以執(zhí)行返回到失敗的插入語句之后，繼續(xù)執(zhí)行將@x設(shè)定為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。從這里可以判斷程序運行無誤，完全按照我們的思路進(jìn)行。大家可以花點時間去調(diào)整錯誤處理器，讓檢查放在語句段的首部，而不是放在可能出現(xiàn)錯誤的地方，雖然那樣看起來程序很紊亂，跳來跳去的感覺。但是這樣的代碼很安全也很清楚。

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; //

　　這是另外一個錯誤處理的例子，在前面的基礎(chǔ)上修改的。事實上你可給SQLSTATE或者錯誤代碼其他的名字，你就可以在處理中使用自己定義的名字了。下面看它是怎么實現(xiàn)的：我把表t2定義為InnoDB表，所以對這個表的插入操作都會ROLLBACK（回滾），ROLLBACK（回滾事務(wù)）也是恰好會發(fā)生的。因為對主鍵插入兩個同樣的值會導(dǎo)致SQLSTATE 23000錯誤發(fā)生，這里SQLSTATE 23000是約束錯誤。

2. DECLARE CONDITION聲明條件

　　這個約束錯誤會導(dǎo)致ROLLBACK（回滾事務(wù)）和SQLSTATE 23000錯誤發(fā)生。

3. DECLARE CONDITION

mysql> CALL p24()//
Query OK, 0 rows affected (0.28 sec)

mysql> SELECT * FROM t2//
Empty set (0.00 sec)

　　我們調(diào)用這個存儲過程看結(jié)果是什么，從上面結(jié)果我們看到表t2沒有插入任何記錄。全部事務(wù)都回滾了。這正是我們想要的。

4. DECLARE CONDITION

mysql> CREATE PROCEDURE p9 ()
-> BEGIN
-> DECLARE EXIT HANDLER FOR NOT FOUND BEGIN END;
-> DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN END;
-> DECLARE EXIT HANDLER FOR SQLWARNING BEGIN END;
-> END;//
Query OK, 0 rows affected (0.00 sec)

　　這里是三個預(yù)聲明的條件：NOT FOUND (找不到行), SQLEXCEPTION (錯誤),SQLWARNING (警告或注釋)。因為它們是預(yù)聲明的，因此不需要聲明條件就可以使用。不過如果你去做這樣的聲明："DECLARE SQLEXCEPTION CONDITION ..."，你將會得到錯誤信息提示。

Cursors 游標(biāo)

　　游標(biāo)實現(xiàn)功能摘要：

DECLARE cursor-name CURSOR FOR SELECT ...;
OPEN cursor-name;
FETCH cursor-name INTO variable [, variable];
CLOSE cursor-name;

　　現(xiàn)在我們開始著眼游標(biāo)了。雖然我們的存儲過程中的游標(biāo)語法還并沒有完整的實現(xiàn)，但是已經(jīng)可以完成基本的事務(wù)如聲明游標(biāo)，打開游標(biāo)，從游標(biāo)里讀取，關(guān)閉游標(biāo)。

1. Cursor Example

CREATE PROCEDURE p25 (OUT return_val INT)
BEGIN
DECLARE a,b INT;
DECLARE cur_1 CURSOR FOR SELECT s1 FROM t;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET b = 1;
OPEN cur_1;
REPEAT
FETCH cur_1 INTO a;
UNTIL b = 1
END REPEAT;
CLOSE cur_1;
SET return_val = a;
END;//

　　我們看一下包含游標(biāo)的存儲過程的新例子。

2. Cursor Example

CREATE PROCEDURE p25 (OUT return_val INT)
BEGIN
DECLARE a,b INT; <--
DECLARE cur_1 CURSOR FOR SELECT s1 FROM t;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET b = 1;
OPEN cur_1;
REPEAT
FETCH cur_1 INTO a;
UNTIL b = 1
END REPEAT;
CLOSE cur_1;
SET return_val = a;
END;//

　　這個過程開始聲明了三個變量。附帶說一下，順序是十分重要的。首先要進(jìn)行變量聲明，然后聲明條件，隨后聲明游標(biāo)，再后面才是聲明錯誤處理器。如果你沒有按順序聲明，系統(tǒng)會提示錯誤信息。

3. Cursor Example

CREATE PROCEDURE p25 (OUT return_val INT)
BEGIN
DECLARE a,b INT;
DECLARE cur_1 CURSOR FOR SELECT s1 FROM t; <--
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET b = 1;
OPEN cur_1;
REPEAT
FETCH cur_1 INTO a;
UNTIL b = 1
END REPEAT;
CLOSE cur_1;
SET return_val = a;
END;//

　　程序第二步聲明了游標(biāo)cur_1，如果你使用過嵌入式SQL的話，就知道這和嵌入式SQL差不多。

4. Cursor Example

CREATE PROCEDURE p25 (OUT return_val INT)
BEGIN
DECLARE a,b INT;
DECLARE cur_1 CURSOR FOR SELECT s1 FROM t;
DECLARE CONTINUE HANDLER FOR NOT FOUND <--
SET b = 1; <--
OPEN cur_1;
REPEAT
FETCH cur_1 INTO a;
UNTIL b = 1
END REPEAT;
CLOSE cur_1;
SET return_val = a;
END;//

　　最后進(jìn)行的是錯誤處理器的聲明。這個CONTINUE處理沒有引用SQL錯誤代碼和SQLSTATE值。它使用的是NOT FOUND系統(tǒng)返回值，這和SQLSTATE 02000是一樣的。

5. Cursor Example

CREATE PROCEDURE p25 (OUT return_val INT)
BEGIN
DECLARE a,b INT;
DECLARE cur_1 CURSOR FOR SELECT s1 FROM t;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET b = 1;
OPEN cur_1; <--
REPEAT
FETCH cur_1 INTO a;
UNTIL b = 1
END REPEAT;
CLOSE cur_1;
SET return_val = a;
END;//

　　過程第一個可執(zhí)行的語句是OPEN cur_1，它與SELECT s1 FROM t語句是關(guān)聯(lián)的，過程將執(zhí)行SELECT s1 FROM t，返回一個結(jié)果集。

6. Cursor Example

CREATE PROCEDURE p25 (OUT return_val INT)
BEGIN
DECLARE a,b INT;
DECLARE cur_1 CURSOR FOR SELECT s1 FROM t;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET b = 1;
OPEN cur_1;
REPEAT
FETCH cur_1 INTO a; <--
UNTIL b = 1
END REPEAT;
CLOSE cur_1;
SET return_val = a;
END;//

　　這里第一個FETCH語句會獲得一行從SELECT產(chǎn)生的結(jié)果集中檢索出來的值，然而表t中有多行，因此這個語句會被執(zhí)行多次，當(dāng)然這是因為語句在循環(huán)塊內(nèi)。

7. Cursor Example

CREATE PROCEDURE p25 (OUT return_val INT)
BEGIN
DECLARE a,b INT;
DECLARE cur_1 CURSOR FOR SELECT s1 FROM t;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET b = 1; <--
OPEN cur_1;
REPEAT
FETCH cur_1 INTO a;
UNTIL b = 1
END REPEAT;
CLOSE cur_1;
SET return_val = a;
END;//

　　最后當(dāng)MySQL的FETCH沒有獲得行時，CONTINUE處理被觸發(fā)，將變量b賦值為1。

8. Cursor Example

　　到了這一步UNTIL b=1條件就為真，循環(huán)結(jié)束。在這里我們可以自己編寫代碼關(guān)閉游標(biāo)，也可以由系統(tǒng)執(zhí)行，系統(tǒng)會在復(fù)合語句結(jié)束時自動關(guān)閉游標(biāo)，但是最好不要太依賴系統(tǒng)的自動關(guān)閉行為（譯注：這可能跟java的Gc一樣，不可信）。

9. Cursor Example

　　這個例程中我們?yōu)檩敵鰠?shù)指派了一個局部變量，這樣在過程結(jié)束后的結(jié)果仍能使用。

10. Cursor Example

mysql> CALL p25(@return_val)//
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @return_val//
+-------------+
| @return_val |
+-------------+
| 5 |
+-------------+
1 row in set (0.00 sec)

　　上面是過程調(diào)用后的結(jié)果。可以看到return_val參數(shù)獲得了數(shù)值5，因為這是表t的最后一行。
　　由此可以知道游標(biāo)工作正常，出錯處理也工作正常。

Cursor Characteristics 游標(biāo)的特性

　　摘要：
　　READ ONLY只讀屬性
　　NOT SCROLLABLE順序讀取
　　ASENSITIVE敏感

　　在5.0版的MySQL中，你只可以從游標(biāo)中取值，不能對其進(jìn)行更新。因為游標(biāo)是（READONLY）只讀的。你可以這樣做：

FETCH cursor1 INTO variable1;
UPDATE t1 SET column1 = 'value1' WHERE CURRENT OF cursor1;

　　游標(biāo)也是不可以滾動的，只允許逐一讀取下一行，不能在結(jié)果集中前進(jìn)或后退。下面代碼就是錯誤的：

FETCH PRIOR cursor1 INTO variable1;
FETCH ABSOLUTE 55 cursor1 INTO variable1;

　　同時也不允許在已打開游標(biāo)進(jìn)行操作的表上執(zhí)行updates事務(wù)，因為游標(biāo)是（ASENSITIVE）敏感的。因為如果你不阻止update事務(wù)，那就不知道結(jié)果會變成什么。如果你使用的是InnoDB而不是MyISAM存儲引擎的話，結(jié)果也會不一樣。

Security 安全措施

　　摘要
　　Privileges (1) CREATE ROUTINE
　　Privileges (2) EXECUTE
　　Privileges (3) GRANT SHOW ROUTINE?
　　Privileges (4) INVOKERS AND DEFINERS

　　這里我們要討論一些關(guān)于特權(quán)和安全相關(guān)的問題。但因為在MySQL安全措施的功能并沒有完全，所以我們不會對其進(jìn)行過多討論。

1. Privileges CREATE ROUTINE

GRANT CREATE ROUTINE
ON database-name . *
TO user(s)
[WITH GRANT OPTION];

　　現(xiàn)在用root就可以了

　　在這里要介紹的特權(quán)是CREATE ROUTINE，它不僅同其他特權(quán)一樣可以創(chuàng)建存儲過程和函數(shù)，還可以創(chuàng)建視圖和表。Root用戶擁有這種特權(quán)，同時還有ALTER ROUTINE特權(quán)。

2. Privileges EXECUTE

GRANT EXECUTE ON p TO peter
[WITH GRANT OPTION];

　　上面的特權(quán)是決定你是否可以使用或執(zhí)行存儲過程的特權(quán)，過程創(chuàng)建者默認(rèn)擁有這個特權(quán)。

3. Privileges SHOW ROUTINE?

GRANT SHOW ROUTINE ON db6.* TO joey
[WITH GRANT OPTION];

　　因為我們已經(jīng)有控制視圖的特權(quán)了：GRANT SHOW VIEW。所以在這個基礎(chǔ)上，為了保證兼容，日后可能會添加GRANT SHOW ROUTINE特權(quán)。這樣做是不太符合標(biāo)準(zhǔn)的，在寫本書的時候，MySQL還沒實現(xiàn)這個功能。

4. Privileges Invokers and Definers 特權(quán)調(diào)用者和定義者

CREATE PROCEDURE p26 ()
SQL SECURITY INVOKER
SELECT COUNT(*) FROM t //
CREATE PROCEDURE p27 ()
SQL SECURITY DEFINER
SELECT COUNT(*) FROM t //
GRANT INSERT ON db5.* TO peter; //

　　現(xiàn)在我們測試一下SQL SECURITY子句吧。Security是我們前面提到的程序特性的一部分。你root用戶，將插入權(quán)賦給了peter。然后使用peter登陸進(jìn)行新的工作，我們看peter可以怎么使用存儲過程，注意：peter沒有對表t的select權(quán)力，只有root用戶有。

5. Privileges Invokers and Definers

/* Logged on with current_user = peter */使用帳戶peter登陸

mysql> CALL p26();
ERROR 1142 (42000): select command denied to user
'peter'@'localhost' for table 't'
mysql> CALL p27();
+----------+
| COUNT(*) |
+----------+
| 1 |
+----------+
1 row in set (0.00 sec)

　　當(dāng)peter嘗試調(diào)用含有調(diào)用保密措施的過程p26時會失敗。那是因為peter沒有對表的select的權(quán)力。

　　但是當(dāng)petre調(diào)用含有定義保密措施的過程時就能成功。原因是root有select權(quán)力，Peter有root的權(quán)力，因此過程可以執(zhí)行。

上一篇：MySQL 5.0 新特性教程觸發(fā)器:第一講

下一篇：MYSQL出錯代碼列表