DB2 提供的强大功能可以让开发人员创建出非常高效稳定的存储过程。但对于初学者来说，开发出这样的程序并不轻易。本文主要讨论开发高效稳定的 DB2 存储过程的一些常用技巧和方法。读者定位为具有一定开发经验的 DB2 开发经验的开发人员。读者可以从本文学习到如何编写稳定、高效的存储过程。并可以直接使用文章中提供的 DB2 代码，从而节省他们的开发和调试时间，提高效率。本文以 DB2 开发人员的角度介绍了在 DB2 存储过程开发中需要注重的事项和技巧。新手假如能够按照本文介绍的最佳实践来开发存储过程，可以避免一些常见的错误，从而编写出高效的程序。本文从初始化参数、游标、异常处理、临时表的使用以及如何寻找并 rebind 非法存储过程等常见问题进行了着重讨论,并且给出了示例代码。在存储过程中，开发人员能够声明和设置 SQL 变量、实现流程控制、处理异常、能够对数据进行插入、更新或者删除。同时，客户应用（这里指调用存储过程的应用程序，它可以是 JDBC 的调用，也可以是 ODBC 和 CLI 等）和存储过程之间可以传递参数，并且从存储过程中返回结果集。其中，使用 SQL 编写的 DB2 存储过程是在开发中常见的一种存储过程。本文主要讨论此类存储过程。最佳实践 1：在创建存储过程语句中提供必要的参数创建存储过程语句（CREATE PROCEDURE）可以包含很多参数，虽然从语法角度讲它们不是必须的，但是在创建存储过程时提供它们可以提高执行效率。下面是一些常用的参数容许 SQL （allowed－SQL）容许 SQL （allowed－SQL）子句的值指定了存储过程是否会使用 SQL 语句，假如使用，其类型如何。它的可能值如下所示：NO SQL： 表示存储过程不能够执行任何 SQL 语句。
12345678910下一页
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 中对输入参数的验证和处理也是很重要的。正确的验证和预处理操作包括：
上一页1234567下一页
假如输入参数错误，存储过程应返回一个明确的值告诉客户应用，然后客户应用可以根据返回的值进行处理，或者向存储过程提交新的参数，或者去调用其他的程序。 根据业务逻辑，对输入参数作一定的预处理，如大小写的转换，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
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;
上一页12345678下一页
表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 2
READS SQL DATA
LANGUAGE SQL
BEGIN
DECLARE rs1 CURSOR WITH RETURN TO CLIENT FOR
SELECT name, age FROM person
WHERE age<piAge;
DECLARE rs2 CURSOR WITH RETURN TO CALLER FOR
SELECT NAME, age FROM person
WHERE age>piAge;
OPEN rs1;
OPEN rs2;
END
上一页123456789下一页
代码中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：存储过程递归调用
上一页12345678910下一页
清单4：声明异常处理器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操作。清单5：异常处理器示例DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING
SET stmt = 'ABORTED';
DECLARE UNDO HANDLER FOR NOT FOUND;
上一页234567891011下一页
假如预定义异常集不能满足需求，就可以为特定的SQLSTATE值声明定制异常，然后再为这个定制异常声明处理器。语法如下：清单6：定制异常处理器DECLARE unique-name CONDITION FOR SQLSATE 'sqlstate'处理器可以由单独的存储过程语句定义，也可以使用由BEGIN…END块界定的复合语句定义。注重在执行符合语句的时候，SQLSATE和SQLCODE的值会被改变，假如需要保留异常前的SQLSATE和SQLCODE，就需要在执行复合语句的第一个语句把SQLSATE和SQLCODE赋予本地变量或参数。通常，我们会为存储过程定义一个执行状态的输出参数（例如：poGenStatus）。根据这个输出状态，可以表明存储过程是否正确执行完毕。我们需要定义一些异常处理器为这个输出参数赋值。下面是一个例子：清单7：定义为输出参数赋值的异常处理器-- Generic Handler
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION, SQLWARNING, NOT FOUND
BEGIN NOT ATOMIC
-- Capture SQLCODE & SQLSTATE
SELECTSQLCODE, SQLSTATE
INTOhSqlcode, hSqlstate
FROMSYSIBM.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;
上一页3456789101112下一页
上面的异常处理器会在出现SQLEXCEPTION, SQLWARNING, NOT FOUND异常的时候触发。异常处理器会取出当前的SQLCODE, SQLSTATE，然后根据它们的值来设置输出参数（poGenStatus）的值。我们还可以定制一些异常处理器。例如，我们可以定义一些对参数进行初始化的异常处理器。这里，异常处理器可以看作是一个供存储过程自己调用的内部函数。下面是这种情况的一个例子：清单8：供存储过程自己调用的内部函数-----------------------------------------------------
-- 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
…………
上一页456789101112下一页
上面定制的异常处理器负责对参数hSqlcode，hSqlstate和poGenStatus初始化。当我们在程序中需要对它们初始化时，我们只需要调用SIGNAL sqlreset就可以了。最佳实践 5：合理使用临时表我们在储存过程开发中经常使用临时表。合理的使用临时表可以简化程序的编写，提供执行效率，然而滥用临时表同样也会使得程序运行效率降低。临时表一般在如下情况下使用：1. 临时表用于存储程序运行中的临时数据。例如，假如在一个程序中第一条查询语句执行的结果会被后续的查询语句用到，那么我们可以把第一次查询的结果存储在一个临时表中供后续查询语句使用，而不是在后续查询语句中重新查询一次。假如第一条查询语句非常复杂和耗时，那么上面的策略是非常有效的。2. 临时表可以用于存储在一个程序中需要返回多次的结果集。例如，程序中有一个很耗资源的多表查询，同时，该查询在程序中需要执行多次，那么就可以把第一次查询的结果集存储在临时保中，后续的查询只需要查临时表就可以了。3. 临时表也可以用于让SQL访问非关系型数据库。例如，可以编写程序把非关系型数据库中的数据插入到一个全局临时表中，那么我们就可以对其数据进行查询。我们可使用 DECLARE GLOBAL TEMPORARY TABLE 语句来定义临时表。DB2的临时表是基于会话的，且在会话之间是隔离的。当会话结束时，临时表的数据被删除，临时表被隐式卸下。对临时表的定义不会在SYSCAT.TABLES中出现 下面是定义临时表的一个示例： 清单9：定义临时表DECLARE GLOBAL TEMPORARY TABLE gbl_temp
LIKE person
ON COMMIT DELETE ROWS
NOT LOGGED
IN usr_tbsp此语句创建一个名为 gbl_temp 的用户临时表。定义此用户临时表 所使用的列的名称和说明与 person 的列的名称和说明完全相同。
上一页56789101112下一页
清单10：创建有两个字段的临时表DECLARE GLOBAL TEMPORARY TABLE SESSION.TEMP2
(
IDINTEGER default 3,
NAMECHAR(30)
)
--WITH REPLACE
NOT LOGGED;
--IN USER_TEMP_01;此语句创建了一个有两个字段的临时表。理论上临时表是不需要显示DROP的，因为它是基于会话的，当临时表基于的连接关闭的时候，临时表也就不存在了。但是在实际开发中会有一些情况需要我们对临时表加以注重。一种情况就是被调用的存储过程的返回值是一个基于临时表的结果集。当存储过程执行完毕的时候，临时表并不会消失，因为返回的结果集相当于一个指针，指向临时表所在的内存地址，此时临时表是不会被DROP掉的。这种情况下，既不能在存储过程中删除这个临时表，也不应该由客户应用显示的删除临时表，这就轻易出现一些问题。下面我们通过一个例子来说明这个问题。下面示例代码是返回临时表的存储过程（get_temp_table）：清单11：返回临时表的存储过程-----------------------------------------------------
-- TEMPORARY TABLE & CURSOR declaration
-----------------------------------------------------
DECLARE GLOBAL TEMPORARY TABLE SESSION.TEMP
(
ID INTEGER,
NAMECHAR(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;
上一页6789101112下一页
