提高DB2存储过程性能和健壮性

最佳实践1：在创建存储过程语句中提供必要的参数
创建存储过程语句（CREATE PROCEDURE）可以包含很多参数，虽然从语法角度讲它们不是必须的，但是在创建存储过程时提供这些参数可以提高执行效率。下面是一些常用的参数:

l 容许SQL（allowed－SQL）
容许SQL（allowed－SQL）子句的值指定了存储过程是否会使用SQL语句，如果使用，其类型如何。它的可能值如下所示：
NO SQL： 表示存储过程不能够执行任何SQL语句。
CONTAINS SQL： 表示存储过程可以执行SQL语句，但不会读取SQL数据，也不会修改SQL数据。
READS SQL DATA： 表示在存储过程中包含不会修改SQL数据的SQL语句。也就是说该储存过程只从数据库中读取数据。
MODIFIES SQL DATA： 表示存储过程可以执行任何SQL语句。即可以对数据库中的数据进行增加、删除和修改。
如 果没有明确声明allowed-SQL，其默认值是MODIFIES SQL DATA。不同类型的存储过程执行的效率是不同的，其中NO SQL效率最 好，MODIFIES SQL DATA最差。如果存储过程只是读取数据，但是因为没有声明allowed-SQL类型，它会被当作对数据进行修改的存储 过程来执行，这显然会降低程序的执行效率。因此创建存储过程时，应当明确声明其allowed-SQL类型。

l 返回结果集个数（DYNAMIC RESULT SETS n）
存储过程能够返回0个或者多个结果集。为了从存储过程中返回结果集，需要执行如下步骤：
在CREATE PROCEDURE 语句的DYNAMIC RESULT SETS子句中声明存储过程将要返回的结果集的数量。如果这里声明的返回结果集的数量小于存储过程中实际返回的结果集数量，在执行该存储过程的时候，DB2会返回一个警告。
使用WITH RETURN子句，在存储过程体中声明游标。
为结果集打开游标。当存储过程返回的时候，保持游标打开。
在创建存储过程时指定返回结果集的个数可以帮助程序员验证存储过程是否返回了所期待数量的结果集，提高了程序的完整性。

最佳实践2：对输入参数进行必要的的检查和预处理
无论使用哪种编程语言，对输入参数的判断都是必须的。正确的参数验证是保证程序良好运行的前提。同样的，在DB2存储过程中对输入参数的验证和处理也是很重要的。正确的验证和预处理操作包括：

如果输入参数错误，存储过程应返回一个明确的值告诉客户应用，然后客户应用可以根据返回的值进行处理，或者向存储过程提交新的参数，或者去调用其他的程序。

根据业务逻辑，对输入参数作一定的预处理，如大小写的转换，NULL与空字符串或0的转换等。

在DB2 储存过程开发中，如需要遇到对空(NULL)进行初始化，我们可以使用COALESCE函数。该函数返回第一个非NULL的参数。例如，COALESCE (piName,'')，如果变量piName为NULL，那么函数会返回''，否则就会返回piName本身的值。因此，可以用下面的代码检查 piName是否非NULL并且非空字符串：

SET poGenStatus = 0;

SET piName =RTRIM(COALESCE(piName, ''));

IF (piName ='')

THEN

SET poGenStatus = 34100;

RETURN poGenStatus;

ENDIF;
 

同理，使用COALESCE可以对任何类型的输入参数进行初始化或验证。下面是对参数初始化规则的一个总结：

1. 输入参数为字符类型，且允许为NULL的，如希望缺省值为空字符串，可以使用COALESCE(inputParameter, '')把NULL转换成空字符串;

2. 输入类型为整型，且允许为NULL的，如希望缺省值为0，可以使用COALESCE(inputParameter,0)，把NULL转换成0；

3. 输入参数为字符类型，且不允许是NULL或空字符串的，可以使用RTRIM(COALESCE(inputParameter, ''))把NULL转换成空字符串，然后验证函数返回值是否为空字符串；

4. 输入类型为整型，且不允许是NULL的，不需要使用COALESCE函数，直接使用IS NULL进行验证。

最佳实践3：异常（condition）处理

在存储过程执行的过程中，经常因为数据或者其他问题产生异常（condition）。根据业务逻辑，存储过程应该对异常进行相应处理或直接返回给调用者。此处暂且将condition译为异常以方便理解。实际上有些异常（condition）并非是由于错误引起的。

当 存储过程中的语句返回的SQLSTATE值超过00000的时候，就表明在存储过程中产生了一个异常（condition），它表示出现了错误、数据没有 找到或者出现了警告。为了处理存储过程中出现的异常，我们必须在存储过程体中声明异常处理器（condition handler），它可以决定存储过程 怎样响应一个或者多个系统定义的异常或者自定义异常。

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

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

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

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

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

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

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

l SQLWARNING: 标识导致警告异常或者导致正100以上的SQLCODE值的异常。

如 果产生了NOT FOUND 或者SQLWARNING异常，并且没有为这个异常定义异常处理器，系统就会忽略这个异常，并且将控制流转向下一个语句。如 果产生了SQLEXCEPTION异常，并且没有为这个异常定义异常处理器，那么存储过程就会失败，系统会将控制流返回调用者。因此如果开发人员想改变这 种异常处理流程，必须自定义异常处理器。例如，希望在出现SQLWARNING异常时也终止存储过程，并将名为stmt的变量设为“ABORTED”，可 以定义下面语句定义异常处理器：
DECLAREEXIT HANDLER FOR SQLEXCEPTION, SQLWARNING
SET stmt ='ABORTED';

如果预定义异常集仍不能满足需求，可以为特定的SQLSTATE值声明自定义异常，然后再为这个异常定制异常声明处理器。为特定的SQLSTATE值声明自定义异常的语法如下：
DECLARE condition-name CONDITION FOR SQLSATE