------------------------------------------------------------
--脚本说明：测试存储过程异常捕获
--作者：彭建军
--DB2 VERSION ： IBM UDB V 9.1
--OS ： Windows XP Professional SP2
--最新文档更新时间：2006-8-11 14:13
------------------------------------------------------------



说明：存储过程在执行过程中，不可避免一些出错的情况出现，无论是DBMS本身出错，还是数据的出错都可能引发存储过程调用失败。捕获出错的原因，无疑对增强存储过程的健壮性是有益的。

测试步骤：

Step1：创建测试表 TestTable (见测试表建表脚本)
Step2：创建测试存储过程 TestProc (见存储过程脚本)
Step3：编译存储过程
Step4：插入测试数据，注意插入的数据要使存储过程出错
Step5：执行存储过程 (见测试捕获存储过程错误脚本)

脚本文件附后.

------------------------------------------------------------
--测试表建表脚本
------------------------------------------------------------



--业务表
DROP TABLE TestTable;
CREATE TABLE TestTable
(
  T_ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY 
       (START WITH 0, INCREMENT BY 1, NO CACHE ),
  T_HalfYear VARCHAR(12) NOT NULL,--格式形如 '2006年上半年'
  T_Year SMALLINT,
  PRIMARY KEY (T_ID)
);--存储过程出错日志记录表
DROP TABLE TestLog;
CREATE TABLE TestLog
(
  T_ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY
       (START WITH 0, INCREMENT BY 1, NO CACHE ),
  T_Message VARCHAR(100) NOT NULL,--记录存储过程出错信息
  PRIMARY KEY (T_ID)
);


------------------------------------------------------------
--存储过程脚本
------------------------------------------------------------


------------------------------------------------------------
--功能： 捕获存储过程异常测试
--作者： 彭建军
--最新更新时间： 2006-8-11 11:16
--编译存储过程 CLP "db2 -td@ -vf TestProc.db2"
--运行存储过程 CLP "db2 call TestProc"
------------------------------------------------------------

------------------------------------------------------------
--测试捕获存储过程错误脚本
------------------------------------------------------------

--删除该存储过程
DROP PROCEDURE TestProc()@--创建该存储过程
CREATE PROCEDURE TestProc()
LANGUAGE SQL
BEGIN  --变量定义  
  DECLARE sqlcode INTEGER DEFAULT 0;
  DECLARE sqlstate CHAR(5);
  DECLARE v_Message CHAR(100);
  
  --SQL异常处理，插入到错误日志表中
  DECLARE exit handler FOR sqlexception
  BEGIN    --开始异常处理
    VALUES ('存储过程 TestProc 调用失败,SQLCode：' || CHAR(sqlcode) || 'SQLState：' || CHAR(sqlstate))
      INTO v_Message;    INSERT INTO TestLog(T_Message)
      VALUES (v_Message);    --显式提交事务
    COMMIT;    --设置存储过程返回值
    SIGNAL SQLSTATE '99999'
    SET MESSAGE_TEXT = 'TestProc throw error';
  END;  --开始实现业务逻辑
  UPDATE TestTable
    SET T_Year = INTEGER(SUBSTR(T_HalfYear,1,4));END@


--插入正常的数据，观察存储过程执行情况
INSERT INTO TestTable (T_HalfYear)
  VALUES ('2006年上半年');--执行存储过程
CALL TestProc;
------------------------------------------------------------
--CLP
CALL TestProc  返回状态 = 0
SQLCODE: 0
--------------------------------------------------------------查询业务表
SELECT * FROM TestTable;
------------------------------------------------------------
T_ID  T_HalfYear    T_Year
0     2006年上半年  2006
--------------------------------------------------------------插入脏数据，观察存储过程执行情况
INSERT INTO TestTable (T_HalfYear)
  VALUES ('200年上半年');--执行存储过程
CALL TestProc;
------------------------------------------------------------
--CLP
CALL TestProc
SQL0438N  应用程序发生错误，诊断文本："TestProc throw error"。SQLSTATE=99999
SQL0438N  应用程序发生错误，诊断文本："TestProc throw error"
--------------------------------------------------------------查询业务表
SELECT * FROM TestTable;
------------------------------------------------------------
T_ID  T_HalfYear    T_Year
0     2006年上半年  2006
1     200年上半年 
--------------------------------------------------------------查询存储过程错误日志表
SELECT * FROM TestLog;
------------------------------------------------------------
T_ID  T_Message
0     存储过程 TestProc 调用失败,SQLCode：-420 SQLState：22018                                      
------------------------------------------------------------ 
------------------------------------------------------------
--删除测试环境
------------------------------------------------------------ 

  DROP TABLE TestTable; 
 
DROP TABLE TestLog; 
 
DROP PROCEDURE TestProc;


编后：关于 DB2 存储过程错误捕获还有很多实用的功能，限于篇幅，未收录入本文，争取下次收录补完。