DB2 存储过程开发最佳实践2

  清单2：声明异常处理器

　　DECLARE handler-type HANDLER FOR condition handler-action

　　异常处理器类型(handler-type)有以下几种：

　　CONTINUE 在处理器操作完成之后，会继续执行产生这个异常语句之后的下一条语句。

　　EXIT 在处理器操作完成之后，存储过程 会终止，并将 控制 返回给调用者。

　　UNDO 在处理器操作执行之前，DB2会回滚存储过程中执行的SQL操作。在处理器操作完成之后，存储过程会终止，并将控制返回给调用者。

　　异常处理器可以处理基于特定SQLSTATE值的定制异常，或者处理预定义异常的类。预定义的3种异常如下所示：

　　NOT FOUND 标识导致SQLCODE值为+100或者SQLSATE值为02000的异常。这个异常通常在SELECT没有返回行的时候出现。

　　SQLEXCEPTIOIN 标识导致SQLCODE值为负的异常。

　　SQLWARNING 标识导致警告异常或者导致+100以外的SQLCODE正值的异常。

　　如果产生了NOT FOUND 或者SQLWARNING异常，并且没有为这个异常定义异常处理器，那么就会忽略这个异常，并且将控制流转向下一个语句。如果产生了SQLEXCEPTION异常，并且没有为这个异常定义异常处理器，那么存储过程就会失败，并且会将控制流返回调用者。

　　以下示例声明了两个异常处理器。 EXIT处理器会在出现SQLEXCEPTION 或者SQLWARNING异常的时候被调用。EXIT处理器会在终止SQL程序之前，将名为stmt的变量设为"ABORTED",并且将控制流返回给调用者。UNDO处理器会将控制流返回给调用者之前，回滚存储过程体中已经完成的SQL操作。

　　清单3：异常处理器示例

　　DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING SET stmt = 'ABORTED';

　　DECLARE UNDO HANDLER FOR NOT FOUND;

　　如果预定义异常集不能满足需求，就可以为特定的SQLSTATE值声明定制异常，然后再为这个定制异常声明处理器。语法如下：

　　清单4：定制异常处理器

　　DECLARE unique-name CONDITION FOR SQLSATE 'sqlstate'

　　处理器可以由单独的存储过程语句定义，也可以使用由BEGIN…END块界定的复合语句定义。注意在执行符合语句的时候，SQLSATE和SQLCODE的值会被改变，如果需要保留异常前的SQLSATE和SQLCODE，就需要在执行复合语句的第一个语句把SQLSATE和SQLCODE赋予本地变量或参数。

　　通常， 我们 会为存储过程定义一个执行状态的输出参数(例如：poGenStatus)。

　　根据这个输出状态，可以表明存储过程是否正确执行完毕。我们需要定义一些异常处理器为这个输出参数赋值。下面是一个例子：

　　清单5：定义为输出参数赋值的异常处理器

　　-- Generic Handler DECLARE CONTINUE HANDLER FOR SQLEXCEPTION, SQLWARNING, NOT FOUND BEGIN NOT ATOMIC -- Capture SQLCODE & SQLSTATE SELECT SQLCODE, SQLSTATE INTO hSqlcode, hSqlstate FROM SYSIBM.SYSDUMMY1; -- Use the poGenStatus variable to tell the procedure -- what type of error occurred CASE hSqlstate WHEN '02000' THEN SET poGenStatus=5000; WHEN '42724' THEN SET poGenStatus=3; ELSE IF (hSqlCode < 0) THEN SET poGenStatus=hSqlCode; END IF; END CASE; END;

　　上面的异常处理器会在出现SQLEXCEPTION, SQLWARNING, NOT FOUND异常的时候触发。异常处理器会取出当前的SQLCODE, SQLSTATE，然后根据它们的值来设置输出参数(poGenStatus)的值。

　　我们还可以定制一些异常处理器。例如，我们可以定义一些对参数进行初始化的异常处理器。这里，异常处理器可以看作是一个供存储过程自己调用的内部函数。下面是这种情况的一个例子：

　　清单6：供存储过程自己调用的内部函数

　　---------------------- -- CONDITION declaration ----------- -- (80100~80199) SQLCODE & SQLSTATE DECLARE sqlReset CONDITION for sqlstate '80100'; ----------------------------------------------------- -- EXCEPTION HANDLER declaration ----------------------------------------------------- -- Handy Handler DECLARE CONTINUE HANDLER FOR sqlReset BEGIN NOT ATOMIC SET hSqlcode = 0; SET hSqlstate = '00000'; SET poGenStatus = 0; END; ………… ----------------------------------------------------- -- Procedure Body ----------------------------------------------------- SIGNAL sqlreset; -- insert the record …………

　　上面定制的异常处理器负责对参数hSqlcode，hSqlstate和poGenStatus初始化。当我们在程序中需要对它们初始化时，我们只需要调用SIGNAL sqlreset就可以了。

　　最佳实践 5：合理使用临时表

　　我们在储存过程开发中经常使用临时表。合理的使用临时表可以简化程序的编写，提供执行效率，然而滥用临时表同样也会使得程序运行效率降低。

　　临时表一般在如下情况下使用：

　　1. 临时表用于存储程序运行中的临时数据。例如，如果在一个程序中第一条 查询 语句执行的结果会被后续的查询语句用到，那么我们可以把第一次查询的结果存储在一个临时表中供后续查询语句使用，而不是在后续查询语句中重新查询一次。如果第一条查询语句非常复杂和耗时，那么上面的策略是非常有效的。

　　2. 临时表可以用于存储在一个程序中需要返回多次的结果集。例如，程序中有一个很耗资源的多表查询，同时，该查询在程序中需要执行多次，那么就可以把第一次查询的结果集存储在临时保中，后续的查询只需要查临时表就可以了。

　　3. 临时表也可以用于让SQL访问非关系型数据库。例如，可以编写程序把非关系型数据库中的数据插入到一个全局临时表中，那么我们就可以对其数据进行查询。

　　我们可使用 DECLARE GLOBAL TEMPORARY TABLE 语句来定义临时表。DB2的临时表是基于会话的，且在会话之间是隔离的。当会话结束时，临时表的数据被删除，临时表被隐式卸下。对临时表的定义不会在SYSCAT.TABLES中出现 下面是定义临时表的一个示例：

清单7：定义临时表

　　DECLARE GLOBAL TEMPORARY TABLE gbl_temp LIKE person ON COMMIT DELETE ROWS NOT LOGGED IN usr_tbsp

　　此语句创建一个名为 gbl_temp 的用户临时表。定义此用户临时表 所使用的列的名称和说明与 person 的列的名称和说明完全相同。

　　清单8：创建有两个字段的临时表

　　DECLARE GLOBAL TEMPORARY TABLE SESSION.TEMP2 ( ID INTEGER default 3, NAME CHAR(30) ) --WITH REPLACE NOT LOGGED; --IN USER_TEMP_01;

　　此语句创建了一个有两个字段的临时表。

　　理论上临时表是不需要 显示 DROP的，因为它是基于会话的，当临时表基于的连接关闭的时候，临时表也就不存在了。但是在实际开发中会有一些情况需要 我们 对临时表加以注意。

　　一种情况就是被调用的存储过程的返回值是一个基于临时表的结果集。当存储过程执行完毕的时候，临时表并不会消失，因为返回的结果集相当于一个指针，指向临时表所在的内存地址，此时临时表是不会被DROP掉的。这种情况下，既不能在存储过程中删除这个临时表，也不应该由客户应用显示的删除临时表，这就容易出现一些问题。下面我们通过一个例子来说明这个问题。

　　下面示例代码是返回临时表的存储过程(get_temp_table)：

　　清单9：返回临时表的存储过程

　　----------------------------------------------------- -- TEMPORARY TABLE & CURSOR declaration ----------------------------------------------------- DECLARE GLOBAL TEMPORARY TABLE SESSION.TEMP ( ID INTEGER, NAME CHAR(30) ) --WITH REPLACE NOT LOGGED; P2: BEGIN DECLARE R_CRSR CURSOR WITH RETURN TO CLIENT FOR SELECT * FROM SESSION.TEMP FOR READ ONLY; INSERT INTO SESSION.TEMP VALUES(1,piName); OPEN R_CRSR; END P2;

　　存储过程中声明了有两个字段的临时表TEMP,声明了一个游标R_CRSR返回临时表中所有记录，最后在临时表中插入两条记录。

　　图2：程序第一次执行的结果

　

　　可以从图中看出，运行结果是我们期望的。那么如果我们再运行一次，会有什么结果呢?下图是其运行结果：

　　图3：程序再次执行的结果

　　

　　第二次执行的时候程序却出错了，这是因为在同一个连接中，临时表并没有被DROP掉，所以在第二次调用存储过程的时候就会出现临时表已经存在的错误。

　　另外一种情况，就是很多时候例如在websphere中通过JDBC连接数据库时使用了连接池的技术，这带来了一些效率的提升，同时在某些情况下也容易让人误解。客户应用程序中关闭了数据库连接，但是并不一定真正关闭了数据库连接，如果客户应用程序使用了临时表而数据库连接并没有关闭，那么临时表就不会被DROP。当连接池把这个连接分给另一个客户程序的时候，新的客户程序仍然可以使用旧的临时表，这不是我们希望的。如果想避免上述问题，可以在创建临时表时，加上WITH REPLACE;或者根据业务逻辑在合适的地方显示的DROP临时表。

　　下面是使用WITH REPLACE创建临时表的执行情况。

　　图4：使用WITH REPLACE创建临时表

　　

　　可以看出在一个连接里面，多次调用存储过程get_temp_table，也不会出现问题。临时表在某些情况下也是需要避免使用的。大家知道临时表是存放在内存中的，如果一个临时表有上万或者十几万条记录，同时程序的并发数很大，那么在内存中建立的临时表耗费的资源就很庞大，此时数据库的性能会急剧下降，甚至会导致数据库崩溃。因此，大家在使用临时表的时候，需要 考虑 它对资源的耗费，避免盲目使用临时表。

最佳实践 6：寻找并rebind 非法的存储过程

　　存储过程会因为其涉及和引用的对象发生了改变而导致其非法(invalid)，例如：修改了表结构，导致引用该表的存储过程非法，或者重新编译一个存储过程，会使调用这个存储过程的父存储过程非法。此时 我们 需要对非法的存储过程重新编译(rebind)。但是，对非法的存储过程进行rebind的时候，需要确定其引用的对象是合法的，否则非法的存储过程也不能rebind成功。

　　这里我们介绍一下发现和rebind非法存储过程的方法。我们是通过判断SYSCAT.routines中VALID字段的值来查找非法存储过程的。下面是查找非法存储过程的一段代码：

　　清单10：查找非法存储过程

　　SELECT RTRIM(r.routineschema) || '.' || RTRIM(r.routinename) AS spname , ' ( '|| RTRIM(r.routineschema) || '.' || 'P'||SUBSTR(CHAR(r.lib_id+10000000),2)||' )' FROM SYSCAT.routines r WHERE r.routinetype = 'P' AND ((r.origin = 'Q' AND r.valid != 'Y') OR EXISTS ( SELECT 1 FROM syscat.packages WHERE pkgschema = r.routineschema AND pkgname = 'P'||SUBSTR(CHAR(r.lib_id+10000000),2) AND valid !='Y' ) ) ORDER BY spname;

　　获得的结果如下：

　　清单11：查找非法存储过程的结果

　　SPNAME ---------------------------------- TEST.DEMO_INFO_8 (TEST. P3550884)

　　可以使用下面的命令rebind它们

　　清单12：Rebind 非法存储过程语法

　　rebind package packagename resolve any@

　　Packagename就是查询结果中括号里的值。例如，如果rebind上面查出来的存储过程。我们只需要执行下面语句

　　清单13：Rebind 非法存储过程

　　rebind package TEST.P3550884 resolve any@

　　当然，如果此存储过程程序本身有问题，需要先修改存储过程代码后再进行编译。

　　类似的，通过下面的代码可以获得非法的视图。

　　清单14：获得非法的视图

　　SELECT RTRIM(viewschema) || '.' || RTRIM(viewname) AS viewname FROM SYSCAT.views WHERE valid = 'X' ORDER BY viewname;

　　结束语

　　本文介绍了我们在 DB2 存储过程开发中经常用到的一些技巧。同时这些技巧也是编写优秀存储过程的基本要求。本文介绍的一些技巧只是揭开了高效使用 DB2 的冰山一角。DB2 为我们提供了丰富和强大的功能。在使用 DB2 的时候，我们应当深入理解其原理，找出更多的最佳实践与大家分享。

　　参考资料

　　Red book: IBM DB2 UDB Command Reference Version 8

　　Red Book：IBM DB2 UDB Application Development Guide