from http://www.devshed.com/c/a/MySQL/Error-Handling-Examples/
Error Handler Examples
Here are some examples of handler declarations:
If any error condition arises (other than a NOT FOUND ), continue execution after setting l_error=1 :
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
SET l_error=1;
If any error condition arises (other than a NOT FOUND ), exit the current block or stored program after issuing a ROLLBACK statement and issuing an error message:
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
SELECT 'Error occurred – terminating';
END;
If MySQL error 1062 (duplicate key value) is encountered, continue execution after executing the SELECT statement (which generates a message for the calling program):
DECLARE CONTINUE HANDER FOR 106 2
SELECT 'Duplicate key in index';
If SQLSTATE 23000 (duplicate key value) is encountered, continue execution after executing the SELECT statement (which generates a message for the calling program):
DECLARE CONTINUE HANDER FOR SQLSTATE '23000'
SELECT 'Duplicate key in index';
When a cursor fetch or SQL retrieves no values, continue execution after setting l_done=1 :
DECLARE CONTINUE HANDLER FOR NOT
FOUND
SET l_done=1;
Same as the previous example, except specified using a SQLSTATE variable rather than a named condition:
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000 '
SET l_done=1;
Same as the previous two examples, except specified using a MySQL error code variable rather than a named condition or SQLSTATE variable:
DECLARE CONTINUE HANDLER FOR 1329
SET l_done=1;

錯(cuò)誤處理例子
有幾種錯(cuò)誤處理的聲明形式：
§ 如果任何錯(cuò)誤（不是 NOT FOUND ） , 設(shè)置 l_error 為 1 后繼續(xù)執(zhí)行：
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
SET l_error=1;
§ 如果發(fā)生任何錯(cuò)誤（不是 NOT FOUND）， 執(zhí)行 ROLLBACK和產(chǎn)生一條錯(cuò)誤消息后退出當(dāng)前塊或存儲(chǔ)過程。
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
SELECT 'Error occurred – terminating';
END;
§ 如果 MySQL 1062錯(cuò)誤 (重復(fù)的健值 )發(fā)生，執(zhí)行 SELECT語句（向調(diào)用程序發(fā)一條消息）后繼續(xù)執(zhí)行
DECLARE CONTINUE HANDER FOR 106 2
SELECT 'Duplicate key in index';
§ 如果 SQLSTATE 2300錯(cuò)誤 (重復(fù)的健值 )發(fā)生，執(zhí)行 SELECT語句（向調(diào)用程序發(fā)一條消息）后繼續(xù)執(zhí)行
DECLARE CONTINUE HANDER FOR SQLSTATE '23000'
SELECT 'Duplicate key in index';
§ 當(dāng)游標(biāo)或者 SQL 選擇語句沒有返回值時(shí)，設(shè)置 l_done=1 后繼續(xù)執(zhí)行
DECLARE CONTINUE HANDLER FOR NOT
FOUND
SET l_done=1;
§ 此例除了用 SQLSTATE 變量而不是命名條件以外，跟前一個(gè)例子一樣
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000 '
SET l_done=1;
§ 此例除了用 MySQL 的錯(cuò)誤碼變量而不是命名條件或者 SQLSTATE 變量以外，跟前兩個(gè)例子一樣
DECLARE CONTINUE HANDLER FOR 1329
SET l_done=1;

最新評(píng)論