db2- Procedure 異常處理

2011-05-17

5413
0
DB2
2011-06-15

摘要:db2- Procedure 異常處理

DB2條件處理器對於存儲過程來說，有著不可替代的作用。在DB2中，SQL存儲過程可以利用DB2條件處理器(Condition Handler)來處理存儲過程運行過程中的SQL異常(SQLEXCEPTION)、SQL警告(SQLWARNING)、沒有資料(NOT FOUND)三種常見情況以及你自己定義的觸發條件，你可以使用包括退出(EXIT)、繼續(CONTINUE)、撤銷(UNDO)在內的三種條件處理器。

在SQL存儲過程運行過程中，如果出現了SQLEXCEPTION、SQLWARNING和NOT FOUND三種情況，SQL存儲過程將會自動將執行SQL語句後的SQLCODE和SQLSTATE存儲在你事先定義好的變數SQLCODE和SQLSTATE中，並觸發你在存儲過程中定義的條件處理器。

在SQL存儲過程處理錯誤，您需要做如下兩步：聲明SQLCODE和SQLSTATE變數、定義條件處理器。在SQL存儲過程中，您通過下列語句聲明SQLCODE和SQLSTATE變數：

DECLARE SQLCODE INTEGER DEFAULT 0;
DECLARE SQLSTATE CHAR(5) DEFAULT ‘00000’;

當存儲過程執行時，DB2會自動將該SQL語句的返回碼付給這兩個變數，你可以在調試程式的時候，將這兩個值插入到調試表中，或者利用條件處理器將這兩個值返回給調用者。這樣可以方便SQL存儲過程的調試。注意：當SQLCODE和SQLSTATE被賦予錯誤返回碼後，接下來又成功的執行其他SQL語句，則DB2會自動將SQLCODE和SQLSTATE置為零。

可以通過下列語句定義DB2條件處理器：
DECLARE handler-type HANDLER FOR condition handler-action

異常處理器類型(handler-type) 有以下幾種：
CONTINUE：SQL存儲過程在執行完條件處理器中的SQL語句後，繼續執行出錯SQL語句後面的SQL語句。
EXIT： SQL存儲過程在執行完條件處理器中的SQL語句後，退出存儲過程的執行，並將控制權返回給調用者。
UNDO：不詳，待查證。

處理器行動條件(condition handler-action) 包括如下三種常見情況：
SQLEXCEPTION：執行過程中SQLCODE返回任何負值。
SQLWARNING：執行過程中SQLCODE返回 +100以外的正值。
NOT FOUND：SQLCODE返回值為 +100，或者SQLSTATE返回值為 ’02000’，這個異常通常在SELECT沒有返回行的時候出現。

如果產生了NOT FOUND 或者SQLWARNING異常，並且沒有為這個異常定義異常處理器，那麼就會忽略這個異常，並且將控制流轉向下一個語句。如果產生了SQLEXCEPTION異常，並且沒有為這個異常定義異常處理器，那麼DB2 存儲過程就會失敗，並且會將控制流返回調用者。

程式碼請參考如下：


CREATE PROCEDURE SchemaName.ProcedureName
(
	IN "STRUSER" VARCHAR(8),
	IN "STRFUNID" VARCHAR(12),
	OUT "FORS" VARCHAR(1),
	OUT "ErrMessage" VARCHAR(200)
)
BEGIN

	DECLARE SQLERRM VARCHAR(255);
	
	-- 在DB2中，如果要使用SQLCODE、SQLSTATE 那麼必須先 DECLARE 
	-- 當存儲過程執行時，DB2會自動將該SQL語句的返回碼付給這兩個變數
	DECLARE SQLCODE INTEGER DEFAULT 0;
	DECLARE SQLSTATE CHAR(5) DEFAULT ‘00000’;

	-- 自行設定的變數
	DECLARE m_SQLCODE INTEGER;
	DECLARE m_SQLSTATE CHAR(5);


	-- 處理器可以由單獨的存儲過程語句定義
	DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET m_SQLSTATE = SQLSTATE;
	DECLARE CONTINUE HANDLER FOR SQLCODE 100 SET m_SQLCODE = SQLCODE;


	-- 也可以使用由BEGIN…END塊界定的複合語句定義。
	DECLARE EXIT HANDLER FOR SQLSTATE '02000'
	BEGIN
			SET FORS = 'F';
			SET ErrMessage = '：查無符合資料';
	END;

	DECLARE EXIT HANDLER FOR SQLSTATE '21000'
	BEGIN
		-- 異常處理代碼 ---
		SET FORS = 'F';
		SET ErrMessage = '：SELECT INTO回傳值超過一筆';
	END;

	DECLARE EXIT HANDLER FOR SQLSTATE '23505'
	BEGIN
		-- 異常處理代碼 ---
		SET FORS = 'F';
		SET ErrMessage = '：存入資料已存在';
	END;

	DECLARE EXIT HANDLER FOR SQLSTATE '22018', SQLSTATE '38700'
	BEGIN
		-- 異常處理代碼 ---
		SET FORS = 'F';
		SET ErrMessage = '：字串無法轉成數值,或數值不正確';
	END;

	DECLARE EXIT HANDLER FOR SQLSTATE '08003'
	BEGIN
		-- 異常處理代碼 ---
		SET FORS = 'F';
		SET ErrMessage = '：尚未連結至資料庫';
	END;

	DECLARE EXIT HANDLER FOR SQLSTATE '22012'
	BEGIN
		-- 異常處理代碼 ---		
		SET FORS = 'F';
		SET ErrMessage = '：不允許除以0';
	END;

	DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING, NOT FOUND
	BEGIN
		-- 異常處理代碼 ---
		GET DIAGNOSTICS EXCEPTION 1 SQLERRM = MESSAGE_TEXT;
		SET FORS = 'F';
		SET ErrMessage = SQLERRM;
	END;


	-- SQL-STATEMENT 執行出現錯誤，系統會將錯誤代碼填入 SQLCODE、SQLSTATE 這兩個變數
	-- 承上，SQLCODE、SQLSTATE 得到錯誤代碼後，若下句 SQL-STATEMENT 又執行成功，而 SQLCODE、SQLSTATE 又會被填入 000、'00000'
	-- 故欲保留異常的 SQLSATE、SQLCODE 值，則需要在執行其他SQL語句前，把 SQLSATE、SQLCODE 存入其他參數。	
	SELECT APP_CLASS_CODE INTO CLASSCODE FROM KF_APP_CLASS WHERE APP_NAME='無效紀錄';  
	SET SQL_CODE = SQLCODE; -- 在這裡 SQLCODE 的值為100
	SET SQL_CODE = SQLCODE; -- 這裡的 SQLCODE 變成了0，因為上一條語句執行成功了，那麼 SQLCODE 變成了0
	
	
	-- 出現 NOT FOUND 狀況 ---
	-- 若有 NOT FUOND 異常處理器，則會跳至該處理器執行相關 SQL語句
	-- 繼上，若無也可利用下方SQL語句來處理相關 SQL語句
	SELECT * FROM EMPLOYEE WHERE NAME = '無效名字';		
	IF SQLCODE < 0 OR SQLCODE = 100 THEN
		-- 異常處理代碼 ---		
		SET FORS = 'F';
		SET ErrMessage = '發生SQLException、Not Found 狀況！';
		RETURN;	
	END IF;

	
END
@

回首頁