DB2 存储过程开发最佳实践

核心提示： 清单1：COALESCE 函数.---. (1) V |>>-COALESCE---(--expression,--expression-+--)--><COALESCE函数会依次检查输入的参数，返回第一个不是NULL的参数，DB2 存储过程开发最佳实践(2)，只有当传入COALESCE函数的

清单1：COALESCE 函数

.---------------. (1) V |>>-COALESCE-------(--expression----,--expression-+--)----------><

COALESCE函数会依次检查输入的参数，返回第一个不是NULL的参数，只有当传入COALESCE函数的所有的参数都是NULL的时候，函数才会返回NULL。例如， COALESCE(piName,'')，如果变量piName为NULL，那么函数会返回''，否则就会返回piName本身的值。

下面的例子展示了如何对参数进行检查何初始化。

Person表用来存储个人的基本信息，其定义如下：

表1： Person

下面是用于向表Person插入数据的存储过程的参数预处理部分代码：

SET poGenStatus = 0; SET piName = RTRIM(COALESCE(piName, '')); SET piRank = COALESCE(piRank, 0); -- make sure all required input parameters are not null IF ( piNum IS NULL OR piName = '' OR piAge IS NULL ) THEN SET poGenStatus = 34100; RETURN poGenStatus; END IF;

表Person中num、name和age都是非空字段。对于name字段，多个空格我们也认为是空值，所以在进行判断前我们调用RTRIM和COALESCE对其进行处理，然后使用 piName = '',对其进行非空判断;对于Rank字段，我们希望如果用户输入的NULL，我们把它设置成"0"，对其我们也使用COALESCE进行初始化;对于"Age"和"Num" 我们直接使用 IS NULL进行非空判断就可以了。

如果输入参数没有通过非空判断，我们就对输出参数poGenStatus设置一个确定的值(例子中为 34100)告知调用者：输入参数错误。

下面是对参数初始化规则的一个总结，供大家参考：

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

2. 输入类型为整型，且允许为空的，可以使用COALESCE(inputParameter,0)，把空转换成0;

3. 输入参数为字符类型，且是非空非空格的，可以使用COALESCE(inputParameter,'')把NULL转换成''，然后判断函数返回值是否为'';

4. 输入类型为整型，且是非空的，不需要使用COALESCE函数，直接使用IS NULL进行非空判断。

　　最佳实践 3：正确设定游标的返回类型

前面我们已经讨论了如何声明存储过程的返回结果集。这里我们讨论一下结果集返回类型的问题。结果集的返回类型有两种：调用者(CALLER) 和客户应用(CLIENT)。首先我们看一下声明这两种游标的例子：

CREATE PROCEDURE getPeople(IN piAge INTEGER)DYNAMIC RESULT SETS 2READS SQL DATALANGUAGE SQLBEGIN DECLARE rs1 CURSOR WITH RETURN TO CLIENT FOR SELECT name, age FROM person WHERE agepiAge; OPEN rs1; OPEN rs2;END

代码中rs1游标的DECLAER语句中包含WITH RETURN TO CLIENT子句，表示结果集返回给客户应用(CLIENT)。rs2游标的DECLARE语句中包含WITH RETURN TO CALLER子句，表示结果集返回给调用者(CALLER)。

游标返回给调用者(CALLER)表示由存储过程的调用者接收结果集，而不考虑调用者是否是另一个存储过程，还是客户应用。图(1)中存储过程PROZ如果声明为WITH RETURN TO CALLER，那么结果集会返回给存储过程PROY，Client Application是不会得到PROZ返回的结果集的。

图1：存储过程递归调用

游标返回给客户应用(CLIENT)表示由发出最初 CALL 语句的客户应用接收结果集，即使结果集由嵌套层次中的 15 层深的嵌套存储过程发出也是如此。图1中存储过程 PROZ 如果声明为 WITH RETURN TO CLIENT，那么结果集会返回给 Client Application。返回给客户应用(CLIENT)的游标声明是我们经常使用的，也是默认的结果集类型。

在声明返回类型时，我们要认真考虑一下，我们需要把结果集返回给谁，以免丢失返回集，导致程序错误。

最佳实践 4：异常(condition)处理

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

当存储过程中的语句返回的SQLSTATE值超过00000的时候，就表明在存储过程中产生了一个异常(condition)，它表示出现了错误、数据没有找到或者出现了警告。为了响应和处理存储过程中出现的异常，我们必须在存储过程体中声明异常处理器(condition handler)，它可以决定存储过程怎样响应一个或者多个已定义的异常或者预定义异常组。声明条件处理器的语法如下，它会位于变量声明和游标声明之后：