DB2存储过程学习笔记

DECLARE LOC1 RESULT_SET_LOCATOR VARYING; DECLARE X INT DEFAULT 0 ;

SET RES =0; CALL TESTPROC1 ();

--将这个结果集定位符与调用者过程相关联

ASSOCIATE RESULT SET LOCATOR (LOC1) WITH PROCEDURE TESTPROC1 ; --分配从调用过程指向结果集的游标

ALLOCATE C1 CURSOR FOR RESULT SET LOC1 ;

FETCH FROM C1 INTO X; WHILE SQLCODE = 0 DO

SET RES = RES + X ; FETCH FROM C1 INTO X ; END WHILE; END!

9 迁移存储过程

--GET ROUTINE 是一个DB2 命令，它从数据库中提取一个 SQL 过程，并将它转换成一个 SAR（SQL Archive ）文件。

GET ROUTINE INTO FROM SPECIFIC PROCEDURE < routine_name> HIDE BODY

--PUT ROUTINE 是一个DB2 命令，它根据通过 GET ROUTINE 提取的SAR 文件，在数据库中创建 SQL 过程。

PUT ROUTINE FROM < file-name > OWNER USE REGISTERS

最佳实践 1：在创建存储过程语句中提供必要的参数

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

容许 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。

返回结果集个数（DYNAMIC RESULT SETS n）

存储过程能够返回 0 个或者多个结果集。为了从存储过程中返回结果集，需要执行如下步骤：

?

在 CREATE PROCEDURE 语句的 DYNAMIC RESULT SETS 子句中声明存储过程将要返回的结果集的数量（number-of-result-sets）。如果这里声明的返回结果集的数量小于存储过程中实际返回的结果集数量，在执行该存储过程的时候，DB2 会返回一个警告。

? ?

使用 WITH RETURN 子句，在存储过程体中声明游标。 为结果集打开游标。当存储过程返回的时候，保持游标打开。

在创建存储过程时指定返回结果集的个数可以帮助程序员验证存储过程是否返回了所期待数量的结果集，提高了程序的完整性。

最佳实践 2：对输入参数进行必要的的检查和预处理

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

?

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

?

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

在 DB2 储存过程开发中，如需要遇到对空(NULL)进行初始化，我们可以使用 COALESCE 函数。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 )