DB2 存储过程开发最佳实践

核心提示： 清单2：声明异常处理器DECLARE handler-type HANDLER FOR condition handler-action异常处理器类型(handler-type)有以下几种：CONTINUE 在处理器操作完成之后，会继续执行产生这个异常语句之后的下一条语句，DB2 存储过程开发最佳实践(3)，EXIT

清单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中出现 下面是定义临时表的一个示例：