欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

MySQL中的存儲過程異常處理

 更新時間：2022年09月26日 09:51:11   作者：浮華塵夢  
這篇文章主要介紹了MySQL中的存儲過程異常處理方式，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教

在使用MySQL存儲過程時，其中的代碼可能會出現(xiàn)運行錯誤從而導(dǎo)致異常，此時需要將存儲過程中產(chǎn)生的異常捕獲并打印出來

需要知道的概念：

  • condition
  • hanlder
  • diagnostics area（診斷區(qū)）

1. condition

存儲過程中出現(xiàn)的錯誤事件也就是異常都可以被稱為condition。

declare condition語法：

DECLARE condition_name CONDITION FOR condition_value 
condition_value:
mysql_error_code
| SQLSTATE [VALUE] sqlstate_value

declare condition語句的作用是給需要進(jìn)行處理的condition定義一個名稱，并提供給后續(xù)的declare handler進(jìn)行調(diào)用，從而使代碼清晰化。

例如：定義一個名稱為"no_such_table"的condition，并在declare handler中調(diào)用該名稱。

declare condition中可以使用error code(報錯的代碼)值或是sqlstate(5位的字符串)值。

使用error code值定義condition：

DECLARE no_such_table CONDITION FOR 1051;
DECLARE CONTINUE HANDLER FOR no_such_table
BEGIN
-- body of handler
END;

使用sqlstate值定義condition

DECLARE no_such_table CONDITION FOR SQLSTATE '42S02';
DECLARE CONTINUE HANDLER FOR no_such_table
BEGIN
-- body of handler
END;

開頭為’0’的error code或是開頭為’00’的sqlstate值不能用于定義condition，因為它們代表的是成功，而不是異常。

在SIGNAL或者是RESIGNAL中引用的condition，必須是使用sqlstate定義的condition，不能使用error code定義的condition。

存儲過程中的declare condition語句，必須出現(xiàn)在declare cursor或是declare handler之前，否則會報錯。

2.handler

handler就是用來處理condition的，當(dāng)定義的condition發(fā)生時，就執(zhí)行handler中定義的處理邏輯，handler可以處理多個condition。

declare handler語法：

    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

declare handler的語句必須在declare condition語句和定義變量語句之后出現(xiàn)

當(dāng)handler中定義的condition觸發(fā)時，可以采取以下三種處理方式：

  • 1.CONTINUE：發(fā)送錯誤時繼續(xù)執(zhí)行后續(xù)代碼
  • 2.EXIT：發(fā)生錯誤時退出該handler定義所在的代碼塊（可能是子代碼塊或者main代碼塊）
  • 3.UNDO：回滾所有的操作，目前還不支持，所以只有continue和exit可用。

示例：

1.使用error code定義handler

? ? DECLARE CONTINUE HANDLER FOR 1051
? ? BEGIN
? ? -- body of handler
? ? END;

2.使用sqlstate值定義handler

? ? DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02'
? ? BEGIN
? ? -- body of handler
? ? END;

3.SQLWARNING代表01開頭的sqlstate值

? ? DECLARE CONTINUE HANDLER FOR SQLWARNING
? ? BEGIN
? ? -- body of handler
? ? END;

4.NOT FOUND代表02開頭的sqlstate值，這通常用于具有游標(biāo)的上下文關(guān)系中，用來處理游標(biāo)走到數(shù)據(jù)集終點時的condition。

? ? DECLARE CONTINUE HANDLER FOR NOT FOUND
? ? BEGIN
? ? -- body of handler
? ? END;

5.SQLEXCEPTION代表所有其他不是以00,01,02開頭的sqlstate值

? ? DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
? ? BEGIN
? ? -- body of handler
? ? END;

注意1：

在存儲過程中，如果出現(xiàn)了一個condition，但是此時沒有定義相關(guān)的handler，那么處理該condition的方法取決于該condition的類型

  • SQLEXCEPTION類型的condition
  • 默認(rèn)使用EXIT handler來進(jìn)行處理，如果此時該存儲過程被另外一個存儲過程調(diào)用，那么將使用調(diào)用者中定義的handler來進(jìn)行處理。
  • SQLWARNING類型的condition
  • 默認(rèn)使用CONTINUE handler來進(jìn)行處理，存儲過程繼續(xù)執(zhí)行。
  • NOT FOUND類型的condition
  • 如果condition被正常拋出，那么存儲過程正常執(zhí)行，也就是continue的處理方式，如果是被SIGNAL或RESIGNAL拋出，那么存儲過程終止運行，也就是exit的處理方式

來看官網(wǎng)的一個SQLSTATE '23000’主鍵沖突的例子：

    mysql> CREATE TABLE test.t (s1 INT, PRIMARY KEY (s1));
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> delimiter //
    mysql> CREATE PROCEDURE handlerdemo ()
    -> BEGIN
    -> DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x2 = 1;
    -> SET @x = 1;
    -> INSERT INTO test.t VALUES (1);
    -> SET @x = 2;
    -> INSERT INTO test.t VALUES (1);
    -> SET @x = 3;
    -> END;
    -> //
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> CALL handlerdemo()//
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> SELECT @x//
    +------+
    | @x |
    +------+
    | 3 |
    +------+
    1 row in set (0.00 sec)

可以看到存儲過程是正常執(zhí)行的。

如果希望被handler對捕獲到condition不進(jìn)行任何處理，那么可以這樣定義handler：

DECLARE CONTINUE HANDLER FOR SQLWARNING BEGIN END;

注意2：

標(biāo)簽的代碼范圍不包括declare handler的代碼范圍，所以在declare handler中不能使用iterate和leave語句，即使標(biāo)簽的范圍包含了declare handler的范圍。

在下述例子中，標(biāo)簽retry的范圍是整個repeat循環(huán)的范圍，在這個范圍中使用了declare handler語句，表面上看retry包含了declare handler，但實際上retry的范圍只是IF語句的范圍，并不包括declare handler的范圍。

CREATE PROCEDURE p ()
BEGIN
DECLARE i INT DEFAULT 3;
retry:
    REPEAT
     BEGIN
        DECLARE CONTINUE HANDLER FOR SQLWARNING
         BEGIN
            ITERATE retry; # 我不屬于retry的作用范圍哦，所以我不能使用retry標(biāo)簽
         END;
        IF i < 0 THEN
         LEAVE retry; #我才屬于retry的范圍，我可以使用retry標(biāo)簽。
        END IF;
        SET i = i - 1;
    END;
    UNTIL FALSE END REPEAT;
END;

所以存儲過程執(zhí)行時會出現(xiàn)下述錯誤：

ERROR 1308 (42000): LEAVE with no matching label: retry

所以為了避免在handler的中引用外部標(biāo)簽，可以使用下述方法：

1.定義exit類型的handler

如果存儲過程遇到異常停止運行時，無需做一些cleanup操作，可以如下定義：

DECLARE EXIT HANDLER FOR SQLWARNING BEGIN END;

如果需要做一些cleanup操作，可以在begin…end中編寫相應(yīng)處理邏輯:

DECLARE EXIT HANDLER FOR SQLWARNING
BEGIN
block cleanup statements
END;

2.定義continue類型的handler，并使用一個狀態(tài)變量

CREATE PROCEDURE p ()
BEGIN
	DECLARE i INT DEFAULT 3;
	DECLARE done INT DEFAULT FALSE;
	retry:
	 REPEAT
		  BEGIN
			DECLARE CONTINUE HANDLER FOR SQLWARNING
			  BEGIN
				SET done = TRUE;
			  END;
			IF done OR i < 0 THEN
			LEAVE retry;
			END IF;
			SET i = i - 1;
		 END;
	 UNTIL FALSE END REPEAT;
END;

這里使用了一個名稱為done的變量，通過判斷這個變量的狀態(tài)，從而得知是否調(diào)用了continue handler。

3.diagnostics area

SQL語句的執(zhí)行會產(chǎn)生診斷信息，并存放于診斷區(qū)中

通過GET DIAGNOSTICS語句獲取診斷區(qū)中的內(nèi)容，該語句不需要特殊的權(quán)限。

診斷區(qū)分為當(dāng)前診斷區(qū)和堆棧診斷區(qū)，通過CURRENT關(guān)鍵字來獲取當(dāng)前診斷區(qū)中的內(nèi)容，通過STACKED獲取堆棧診斷區(qū)中的內(nèi)容，堆棧診斷區(qū)只有在上下文為condition handler的情況下才可以使用，如果不指定關(guān)鍵字默認(rèn)從當(dāng)前診斷區(qū)獲取信息

在客戶端獲取診斷區(qū)中的數(shù)據(jù)

DROP TABLE test.no_such_table;
GET DIAGNOSTICS CONDITION 1
@p1 = RETURNED_SQLSTATE, @p2 = MESSAGE_TEXT;
SELECT @p1, @p2;

此時并不能使用GET STACKED DIAGNOSTICS堆棧診斷區(qū)中的內(nèi)容，

因為GET STACKED DIAGNOSTICS只能在condition handler中使用

診斷區(qū)匯總包含2種信息：

1.語句信息，例如conditions的數(shù)量和影響的行數(shù)

2. Condition信息，包括錯誤代碼和錯誤消息，如果SQL語句拋出多個 conditions，那么在這部分診斷區(qū)中，會為每一個condition分配一個condition區(qū)，如果沒有拋出condition那么就不會分配

如果語句產(chǎn)生了3個condition，那么診斷區(qū)包含的語句信息和condition信息類似：

Statement information:
row count
... other statement information items ...
Condition Handling
Condition area list:
Condition area 1:
error code for condition 1
error message for condition 1
... other condition information items ...
Condition area 2:
error code for condition 2:
error message for condition 2
... other condition information items ...
Condition area 3:
error code for condition 3
error message for condition 3
... other condition information items ...

GET DIAGNOSTICS語句可以獲取語句信息或者condition信息，但是一條GET DIAGNOSTICS無法同時獲取這2種信息。

獲取診斷區(qū)中語句信息并保存到p1和p2變量中，本例中獲取的是condition的數(shù)量和rows-affected數(shù)量

GET DIAGNOSTICS @p1 = NUMBER, @p2 = ROW_COUNT;

通過指定condition的編號獲取診斷區(qū)中相應(yīng)condition信息到p3和p4變量中，本例中獲取的是sqlstate值和錯誤消息。

GET DIAGNOSTICS CONDITION 1
@p3 = RETURNED_SQLSTATE, @p4 = MESSAGE_TEXT;

在SQL標(biāo)準(zhǔn)中，如果出現(xiàn)多個condition，那么第一個condition是關(guān)于前一個SQL語句返回的sqlstate值的，但是在MySQL中，無法保證這一點，為了得到主要的錯誤，不能使用下面的方法：

GET DIAGNOSTICS CONDITION 1 @errno = MYSQL_ERRNO;

而是先取回condition的數(shù)量，然后使用該值來指定要查看的condition

正確的方法：

GET DIAGNOSTICS @cno = NUMBER;
GET DIAGNOSTICS CONDITION @cno @errno = MYSQL_ERRNO;

關(guān)于診斷區(qū)，官網(wǎng)的例子：

CREATE PROCEDURE do_insert(value INT)
BEGIN
-- Declare variables to hold diagnostics area information
DECLARE code CHAR(5) DEFAULT '00000';
DECLARE msg TEXT;
DECLARE rows INT;
DECLARE result TEXT;
-- Declare exception handler for failed insert
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
GET DIAGNOSTICS CONDITION 1
code = RETURNED_SQLSTATE, msg = MESSAGE_TEXT;
END;
-- Perform the insert
INSERT INTO t1 (int_col) VALUES(value);
-- Check whether the insert was successful
IF code = '00000' THEN
GET DIAGNOSTICS rows = ROW_COUNT;
SET result = CONCAT('insert succeeded, row count = ',rows);
ELSE
SET result = CONCAT('insert failed, error = ',code,', message = ',msg);
END IF;
-- Say what happened
SELECT result;
END;

假設(shè)上述存儲過程中的t1表的字段類型int，并且not null,那么在進(jìn)行下述操作分別向表t1中插入非空值和空值，各自得到的結(jié)果如下：

#插入非空值
mysql> CALL do_insert(1);
+---------------------------------+
| result                          |
+---------------------------------+
| insert succeeded, row count = 1 |
+---------------------------------+
##插入null
mysql> CALL do_insert(NULL);
+---- ------------------------------------------------------------+
| result                                                          |
+-----------------------------------------------------------------+
insert failed, error = 23000, message = Column 'int_col' cannot be null
+-----------------------------------------------------------------+

當(dāng)存儲過程中的condition handler被激活時，會發(fā)生一個向診斷區(qū)堆棧推送的事件：

1.當(dāng)前診斷區(qū)(第一診斷區(qū))會變?yōu)槎褩Ｔ\斷區(qū)(第二診斷區(qū))，并且創(chuàng)建一個新的診斷區(qū)作為當(dāng)前診斷區(qū)。

2.在condition Handler中可以使用 GET [CURRENT] DIAGNOSTICS 和 GET STACKED DIAGNOSTICS來獲取當(dāng)前診斷區(qū)或堆棧診斷區(qū)中的內(nèi)容。

3.在開始的時候，當(dāng)前診斷區(qū)和堆棧診斷區(qū)會返回相同的結(jié)果,所以有可能從當(dāng)前診斷區(qū)獲取到被激活的Handler的condition的相關(guān)信息，只要此時handler中沒有其他SQL語句去改變當(dāng)前診斷區(qū)中的內(nèi)容。

4.隨著Handler中語句的執(zhí)行，會根據(jù)一定的規(guī)則對當(dāng)前診斷區(qū)的內(nèi)容進(jìn)行清空或者修改。

所以更可靠地獲取被激活condition handler中信息的方法是從堆棧診斷區(qū)中獲取相關(guān)信息，因為堆棧診斷區(qū)中的內(nèi)容不會被condition handler中的語句所修改，除了RESIGNAL語句。

通過下面例子來說明，在condition中如何通過 GET STACKED DIAGNOSTICS語句來獲取關(guān)于handler異常的信息，盡管此時當(dāng)前診斷區(qū)已經(jīng)被清空或修改。

DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (c1 TEXT NOT NULL);
DROP PROCEDURE IF EXISTS p;
delimiter //
CREATE PROCEDURE p ()
BEGIN
-- Declare variables to hold diagnostics area information
    DECLARE errcount INT;
    DECLARE errno INT;
    DECLARE msg TEXT;
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
-- Here the current DA is nonempty because no prior statements
-- executing within the handler have cleared it
GET CURRENT DIAGNOSTICS CONDITION 1
errno = MYSQL_ERRNO, msg = MESSAGE_TEXT;
SELECT 'current DA before mapped insert' AS op, errno, msg;
GET STACKED DIAGNOSTICS CONDITION 1
errno = MYSQL_ERRNO, msg = MESSAGE_TEXT;
SELECT 'stacked DA before mapped insert' AS op, errno, msg;
-- Map attempted NULL insert to empty string insert
INSERT INTO t1 (c1) VALUES('');
-- Here the current DA should be empty (if the INSERT succeeded),
-- so check whether there are conditions before attempting to
-- obtain condition information
GET CURRENT DIAGNOSTICS errcount = NUMBER;
IF errcount = 0
THEN
SELECT 'mapped insert succeeded, current DA is empty' AS op;
ELSE
GET CURRENT DIAGNOSTICS CONDITION 1
errno = MYSQL_ERRNO, msg = MESSAGE_TEXT;
SELECT 'current DA after mapped insert' AS op, errno, msg;
END IF ;
GET STACKED DIAGNOSTICS CONDITION 1
errno = MYSQL_ERRNO, msg = MESSAGE_TEXT;
SELECT 'stacked DA after mapped insert' AS op, errno, msg;
END;
INSERT INTO t1 (c1) VALUES('string 1');
INSERT INTO t1 (c1) VALUES(NULL);
END;
//
delimiter ;
CALL p();
SELECT * FROM t1;

在上述存儲過程中，定義了一個condition handler，在這個handler的開頭處分別獲取當(dāng)前診斷區(qū)和堆棧診斷區(qū)中的內(nèi)容，然后執(zhí)行一條insert語句，之后再分別查詢當(dāng)前診斷區(qū)和堆棧診斷區(qū)的內(nèi)容。

在handler定義結(jié)束后，是這個存儲過程的主體，也就是2條insert語句，其中一條insert語句為非空值字符串，另外一條insert插入的值為null，

所以該存儲過程執(zhí)行順序如下：

1.首先成功執(zhí)行INSERT INTO t1 (c1) VALUES(‘string 1’);

2.執(zhí)行INSERT INTO t1 (c1) VALUES(NULL);因為t1表中禁止插入空值，所以會拋出異常。

3.拋出的異常被condition handler捕獲，condition handler被激活從而觸發(fā)其中的處理邏輯,并打印condition handler中當(dāng)前診斷區(qū)和堆棧診斷區(qū)的內(nèi)容，二者內(nèi)容相同。

4.condition handler中的INSERT INTO t1 (c1) VALUES(’’);語句執(zhí)行，該語句的執(zhí)行會清空當(dāng)前診斷區(qū)中的內(nèi)容。

+---------------------------------+-------+----------------------------+
| op                              | errno |             msg            |
+---------------------------------+-------+----------------------------+
| stacked DA before mapped insert | 1048 | Column 'c1' cannot be null |
+---------------------------------+-------+----------------------------+

5.再次打印condition handler中當(dāng)前診斷區(qū)和堆棧診斷區(qū)的內(nèi)容，此時因為當(dāng)前診斷區(qū)的內(nèi)容被清空，所以打印’mapped insert succeeded, current DA is empty’，接著打印堆棧診斷區(qū)中內(nèi)容，因為堆棧診斷區(qū)中的內(nèi)容不會隨著語句的執(zhí)行而被清空掉，所以堆棧診斷區(qū)顯示的內(nèi)容依舊是：

+--------------------------------+-------+----------------------------+
| op                             | errno |  msg                       |
+--------------------------------+-------+----------------------------+
| stacked DA after mapped insert | 1048 | Column 'c1' cannot be null |
+--------------------------------+-------+----------------------------+

需要注意的是

1.GET DIAGNOSTICS語句也會清空當(dāng)前診斷區(qū)中的內(nèi)容，所以上述代碼中把condition handler中的insert語句去掉，得到的結(jié)果也是一樣的

2.如果將上述存儲過程進(jìn)行如下修改，也就是將3條declare變量的語句放到declare handler中，實際的結(jié)果將取決于MySQL的版本，如果是在MySQL-5.7.2之前的版本，下述修改后并不會影響診斷區(qū)中的內(nèi)容，實際結(jié)果與上述結(jié)果相同，如果實在MySQL-5.7.2及之后的版本，declare變量語句會清空當(dāng)前診斷區(qū)中的內(nèi)容。

CREATE PROCEDURE p ()
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
-- Declare variables to hold diagnostics area information
DECLARE errcount INT;
DECLARE errno INT;
DECLARE msg TEXT;
GET CURRENT DIAGNOSTICS CONDITION 1
errno = MYSQL_ERRNO, msg = MESSAGE_TEXT;
SELECT 'current DA before mapped insert' AS op, errno, msg;
GET STACKED DIAGNOSTICS CONDITION 1
errno = MYSQL_ERRNO, msg = MESSAGE_TEXT;
SELECT 'stacked DA before mapped insert' AS op, errno, msg;
...

所以在需要獲取診斷區(qū)中的內(nèi)容時，一定要從堆棧診斷區(qū)中獲取，而不是當(dāng)前診斷區(qū)。

以上為個人經(jīng)驗，希望能給大家一個參考，也希望大家多多支持腳本之家。 

相關(guān)文章

最新評論