DB2 9.5 SQL Procedure Developer 认证考试 735 准备，第 2 部分

核心提示：开始之前关于本系列您正想获得 DB2 SQL Procedure Developer 认证（Exam 735）吗？如果是这样，那么您来对了地方，DB2 9.5 SQL Procedure Developer 认证考试 735 准备，第 2 部分，这个包含 6 篇 DB2 认证准备教程的系列涵盖了所有基础知识 &mdas

开始之前

关于本系列

您正想获得 DB2 SQL Procedure Developer 认证（Exam 735）吗？如果是这样，那么您来对了地方。这个包含 6 篇 DB2 认证准备教程的系列涵盖了所有基础知识 —— 在您看第一个考试题目之前需要理解的内容。即使您不打算马上获得认证，这套教程也非常适合您开始学习关于 DB2 V9.5 数据库开发的知识。

开始之前

关于本教程

在本教程中，您将学习 DB2 9.5 SQL 过程，包括存储过程的简介、使用存储过程的优点，以及 SQL 过程与外部过程之间的不同点。学习不同的 SQL 过程语句，并看看如何调用和共享嵌套的存储过程。测试和部署存储过程，并找到保证 SQL 过程安全的办法。

本教程是一个包含 6 篇教程的系列中的第二篇教程，该系列可用于准备 DB2 9.5 SQL Procedure Developer 735。本教程中的内容主要涵盖该测试第 2 部分 “SQL Procedures” 的目标。

先决条件

本教程是为初级到中级的 Linux®、UNIX® 或 Windows® 数据库开发人员或管理员编写的。您应该基本熟悉使用 UNIX 或 Windows 命令行 shell，并掌握关于 DB2 和 SQL 命令的一些实用知识。

系统需求

本教程中的例子专用于在 Windows 操作系统上运行的 DB2 9.5。但这里的概念和信息与运行在任何分布式平台上的 DB2 都是相关的。

无需安装 DB2 9.5 也可完成本教程。

存储过程

什么是存储过程？

存储过程（也称一个例程）是一个数据库对象，其中包含一系列直接在数据库服务器上执行的 SQL 语句和应用程序控制逻辑，可以制定业务逻辑。业务逻辑可以被封装在存储过程中，后者则可以被客户机应用程序、其他存储过程、用户定义函数或触发器通过一条 SQL 语句来调用。存储过程可以接受参数值，并根据输入数据更改业务逻辑的行为。然后，存储过程可以将输出值返回给调用者，或者将多个结果集返回给调用者。

存储过程可以使用 SQL Procedure Language（SQL PL）或编程语言（比如 Java™ 或 C）来实现。

存储过程可以从以下几个方面显著提高分布式应用程序（在远程系统上运行的应用程序）的性能：

减少网络传输量

减少应用程序开发人员编写代码的工作

为从分布式客户机调用远程存储过程提供一种简单的方式

图 1 显示了存储过程是有很用的：

图 1. 通过使用存储过程减少网络传输量

使用存储过程的优点

存储过程有以下优点：

简化代码重用、代码标准化和代码维护：

当一系列不同的应用程序都需要执行一项相似的任务时，通过编写一个执行这个任务的存储过程，让每个客户机应用程序调用该存储过程以执行任务，这样就可以很方便地实现这些应用程序。如果需要修改任务，那么只需修改受影响的存储过程。如果不使用存储过程的话，就需要修改每个应用程序。

对其他数据库对象的受控制的访问：

没有权限访问特定的数据库对象或一个数据库上的操作（例如创建一个表），但是又想在那个对象上执行动作或执行一个操作的用户，可以通过调用他有权限运行的一个存储过程来实现他的目的。这意味着权限管理得以简化。

提高应用程序的性能：

对于远程应用程序，需要逐条语句地通过网络传输每条发出的 SQL 语句。这可能导致大量的网络传输。通过将所有这些语句添加到一个存储过程中，客户机应用程序只需一次网络请求便可调用那个存储过程。这显然大大减少了所需的网络传输量，从而提高了应用程序的总体性能。

更有效的 SQL：

由于存储过程存放在数据库服务器上，实际上它们本身就是数据库对象，它们可以比客户机应用程序更有效地执行，因为 SQL 请求被更高效地传输。使用嵌入式 SQL 操作的存储过程还拥有已经存储在包中的访问计划，因而可以提高执行每条语句的速度。如果存储过程在创建时使用了 NOT FENCED 子句，那么它与数据库管理器是在同一个进程空间中运行的，因此可以在共享内存中进行通信。

增强的功能：

由于存储过程存放在数据库服务器上，因此很容易比应用程序获得更多可使用的内存和磁盘空间。而应用程序则可以访问安装在数据库服务器上的软件。

互操作性：

不同的程序员可以用不同的编程语言实现不同的代码模块。为了帮助实现具有逻辑互操作性的代码重用，存储过程本身可以用不同的语言编写。用不同语言编写的客户机应用程序可以调用用不同语言编写的存储过程（例如，以 C++ 编写的应用程序可以调用以 Java 编写的存储过程）。而且???以不同语言编写的存储过程可以相互调用（例如，以 C 编写的存储过程可以调用以 Java 编写的存储过程）。客户机的操作系统和存储过程所在的服务器的操作系统也可以不同（例如，一个 Windows 客户机应用程序可以调用一个在 AIX 上运行的存储过程）。

存储过程的局限性

调用存储时应具有互操作性，以使客户机应用程序可以兼容用于编写存储过程的不同的编程语言。但是，用特定语言编写的外部存储过程只能在某些平台上运行。例如，CLR 存储过程只能在基于 Windows 的平台上运行。虽然可以在任何地方调用这种存储过程，但是，如果要将服务器迁移到不同的平台（例如 Solaris 或 AIX），那么就需要重新编写这种存储过程。对于用 C、C++ 或 COBOL 编写的存储过程也是如此，在新的服务器上，这些存储过程需要重新编译。而 SQL 过程则不存在这种问题。虽然 Java 存储过程也较为灵活，但是新的数据库服务器需要安装一个 Java 虚拟机（JVM）才能执行这种存储过程。

惟一用于调用一个存储过程的 SQL 语句是 CALL 语句。CALL 语句可以在应用程序、存储过程、用户定义函数或触发器中使用。

存储过程可以嵌套，一个存储过程可以调用另一个存储过程，后者又可以调用其他存储过程。在 DB2 v9.5 中，一次最多可以嵌套 64 个存储过程。

存储过程不能在不同的调用之间保存状态。

存储过程的输出参数不能直接被另一条 SQL 语句使用。调用接口需要先将输出参数赋给某个变量，然后才可以在另一条 SQL 语句中使用该变量。

存储过程不支持可滚动游标。

外部存储过程与 SQL 存储过程之间的不同点

DB2 支持两种类型的存储过程。一种是外部存储过程，另一种是 SQL 过程。

外部存储过程在数据库之外的应用程序中用编程语言定义逻辑。这种类型的存储过程的可执行文件或库存在于数据库服务器所安装到的文件系统中。和 SQL 过程一样，外部存储过程也要注册到数据库，但是在注册过程中，需要指定存储过程可执行文件或库的位置。

DB2 支持用各种不同的编程语言编写的外部存储过程，包括 C、C++、COBOL、Java 和 .NET（也称 CLR 存储过程）。

以下是外部存储过程独有的特性：

外部存储过程允许访问非数据库接口，例如文件系统或应用程序。即使这些资源不是数据库系统的一部分，外部存储过程仍可以使用它们。例如，一个外部存储过程可以在一个 UNIX 数据库服务器上执行一个 shell 脚本，以执行一项特定的任务。

外部存储过程使用参数风格来确定用于该存储过程的编程语言如何使用输入、输出或输入/输出参数。有些参数风格允许在一个称作 dbinfo 的结构中使用对存储过程有用的传递元数据信息，例如数据库和存储过程属性信息。

外部存储过程可以被定义为 FENCED 或 NOT FENCED。这决定了存储过程应该与数据库管理器在同一个地址空间中运行（NOT FENCED），还是应该在它自己的进程中运行（FENCED）。被定义为 NOT FENCED 的存储过程可以执行得稍微快一点，因为不需要使用共享内存段通信；但是，这种存储过程的风险也更大。未隔离的存储过程如果遇到一个问题，则可能导致整个数据库服务器崩溃，因为它与 DB2 使用相同的地址空间。Java 存储过程必须被定义为 FENCED；不过，它们可以被定义为 THREADSAFE 或 NOT THREADSAFE。

下面是 SQL 过程独有的特性：

SQL 过程只能使用一种称作 SQL Programming Language （SQL PL）的语言来编写。关于这种语言的更多信息，可以在本系列的前一篇教程中找到（参见 参考资料）。因此，外部存储过程与 SQL 过程主要的区别在于外部过程是使用一种特定的编程语言编写的，而 SQL 过程是只能使用 SQL 语句编写。

SQL 过程存放在真正的数据库中。外部存储过程依赖于文件系统中的一个外部库或可执行文件，而 SQL 过程是数据库的一部分。

构建 SQL 过程不需要编译器，也不需要对特定的编程语言有深入的理解。所以，SQL 过程的开发可能更快。

SQL 过程总是被定义为 NOT FENCED。这种类型的存储过程存在的风险更小，因为这种存储过程只能执行 SQL 操作，所以数据库服务器面临的风险是有限的。

SQL 过程的可移植性更好。由于它们不依赖于特定的编程语言，每个数据库服务器上也就不需要用于这种语言的编译器或解释器，所以必要时更容易在每个服务器上重新创建这些存储过程。

SQL 过程的结构

SQL 过程的结构由 CREATE PROCEDURE 语句、参数和复合语句组成。下面的伪图显示了 SQL 过程的结构：

清单 1. SQL 过程的结构

　　　　　　　　　　CREATE PROCEDURE proc_name
　 IN, OUT, INOUT parameters
　 optional clauses
　 SQL procedure body - compound statement

CREATE PROCEDURE 语句定义存储在 DB2 系统目录（例如 SYSCAT.PROCEDURES）中的存储过程的特征和逻辑。

清单 2. CREATE PROCEDURE 命令语法

CREATE PROCEDURE--procedure-name----------------------------->
>--+----------------------------------------------------+--?---->
　 '-(--+------------------------------------------+--)-'　　　
　　　　| .-,------------------------------------. |　　　　　
　　　　| V .-IN----.　　　　　　　　　　　　　　| |　　　　　
　　　　'---+-------+--parameter-name--data-type-+-'　　　　　
　　　　　　+-OUT---+　　　　　　　　　　　　　　　　　　　　　
　　　　　　'-INOUT-'　　　　　　　　　　　　　　　　　　　　　
>--+-------------------------+--?------------------------------->
　 '-SPECIFIC--specific-name-'　　　
　 .-DYNAMIC RESULT SETS 0--------.　　 .-MODIFIES SQL DATA-.　
>--+------------------------------+--?--+-------------------+--->
　 '-DYNAMIC RESULT SETS--integer-'　　 +-CONTAINS SQL------+　
　　　　　　　　　　　　　　　　　　　　'-READS SQL DATA----'　
　　　.-NOT DETERMINISTIC-.　　 .-CALLED ON NULL INPUT-.　　　
>--?--+-------------------+--?--+----------------------+--?----->
　　　'-DETERMINISTIC-----'　　　　　　　　　　　　　　　　　
　 .-INHERIT SPECIAL REGISTERS-.　　 .-OLD SAVEPOINT LEVEL-.　
>--+---------------------------+--?--+---------------------+---->
　　　　　　　　　　　　　　　　　　 '-NEW SAVEPOINT LEVEL-'　
　　　.-LANGUAGE SQL-.　　 .-EXTERNAL ACTION----.　　　
>--?--+--------------+--?--+--------------------+--?------------>
　　　　　　　　　　　　　 '-NO EXTERNAL ACTION-'　　　
>--+------------------------------+--?-------------------------->
　 '-PARAMETER CCSID--+-ASCII---+-'　　　
　　　　　　　　　　　'-UNICODE-'　　　　
>--| SQL-procedure-body |--------------------------------------><

这个图中最常用的部分是 procedure_name 和 parameters (IN/OUT/INOUT)。

procedure_name 是一个 SQL 标识符，可以用一个 SCHEMA NAME 限定，其最大长度为 128 个字符。

parameters (IN/OUT/INOUT) 用于提供一种机制，将特定的数据发送到存储过程中，或者从存储过程中返回数据。系统目录中定义的每个存储过程通过名称和参数数量（不论数据类型）加以区分。在同一个模式中，两个具有相同名称的过程不能具有相同数量的参数。IN 是默认的，不需要指定。例如，“CREATE PROCEDURE test1 (p1 INT, OUT p2 INT)” 定义一个具有一个输入参数和一个输出参数的存储过程。请注意，也可以不带参数创建存储过程，例如 “CREATE PROCEDURE test2”。

SPECIFIC--specific-name 为存储过程赋予一个特定的名称，而不是让 DB2 为它赋予一个系统生成的惟一的名称。当使用具有相同名称和不同参数数量的重载存储过程时，这一点很有用。当删除存储过程时，可以使用这个特定的名称。但是这个特定的名称不能用于调用该存储过程。限定形式是一个模式名称后面跟上一个时间和一个 SQL 标识符（长度限制为 18 个字符）。如果没有指定特定的名称，则由数据库管理器生成一个惟一的名称。这个惟一的名称是 'SQL' 后面加上一个字符时间戳：'SQLyymmddhhmmssxxx'。

CONTAINS SQL, READS SQL DATA, MODIFIES SQL DATA 表明存储过程对数据的访问权的级别。如果在存储过程中执行任意的数据操作，例如使用 GLOBAL TEMPORARY TABLES，那么需要以 MODIFIES SQL DATA 选项指定该存储过程。如果在一个复合 SQL 过程中使用 BEGIN ATOMIC 子句，那么创建该存储过程时必须指定 MODIFIES SQL DATA。

SQL procedure body 是存储过程的主体。其核心是一个复合语句。复合语句由关键词 BEGIN 和 END 包围。这些语句可以是 ATOMIC 或 NOT ATOMIC 的。默认情况下，它们是 NOT ATOMIC 的。SQL Procedures 要求复合语句中的声明和可执行语句符合特定的顺序。

图 2 说明了 SQL 过程中复合语句的构造格式：

图 2. 复合语句的构造格式

ATOMIC 和 NOT ATOMIC 复合 SQL 语句

有两种类型的复合语句：NOT ATOMIC（默认）和 ATOMIC。

NOT ATOMIC

如果发生一个未处理的错误条件，不会回滚 SQL 语句。清单 3 演示了这种语句。

清单 3. NOT ATOMIC 复合语句

CREATE TABLE t1 (c1 INT, c2 CHAR(5))!
CREATE PROCEDURE my_proc1 ()
SPECIFIC not_atomic_example　　　　
P1: BEGIN NOT ATOMIC
　 INSERT INTO t1 VALUES(1, 'FIRST');　　--(1)
　 -- SIGNAL SQLSTATE TO INFORCE ERROR
　 SIGNAL SQLSTATE '70001' SET MESSAGE_TEXT = 'INFORCE ERROR';
　 INSERT INTO t1 VALUES (2,'SECND');　　--(2)
END P1!

如果 INSERT (1) 被成功地执行，那么就会发生一个错误，这个 SQL 过程还没有执行 INSERT (2) 就被终止。由于这个 SQL 过程是用 NOT ATOMIC 语句创建的，第一条 INSERT 语句不会被回滚。如果在该存储过程执行之后查询表 T1，则会返回：

C1	C2
1	FIRST

ATOMIC

在一条 ATOMIC 复合语句的执行期间，如果其中发生任何未处理的错误条件，那么在此之前已执行的所有语句都被回滚。ATOMIC 语句不能被嵌套在其他 ATOMIC 复合语句中。

清单 4 显示了包含 ATOMIC 复合语句的过程：

清单 4. 包含 ATOMIC 复合语句的过程

CREATE PROCEDURE my_proc2 ()
SPECIFIC atomic_example　　　　
P1: BEGIN ATOMIC
　 INSERT INTO t1 VALUES(3, 'THIRD');　　--(1)
　 -- SIGNAL SQLSTATE TO INFORCE ERROR
　 SIGNAL SQLSTATE '70001' SET MESSAGE_TEXT = 'INFORCE ERROR';　　　　　　　　　　　　　　
　 INSERT INTO t1 VALUES (4,'FOUR');　　　--(2)
END P1

如果 INSERT (1) 已成功执行，然后发生一个错误，则 SQL 过程还没有执行 INSERT (2) 即被终止。由于这个存储过程创建时使用了 ATOMIC 语句，因此第一条 INSERT 被回滚。如果在该 SQL 过程执行后查询表 T1，结果只会返回前一个例子插入的一行：

C1	C2
1	FIRST

复合 SQL 语句与变量的作用域

在 SQL 过程中可以有一个或多个复合语句。那些复合语句可以是嵌套的，也可以是一个接一个的。每个复合语句引入一个新的本地变量的作用域，那些变量只能在此作用域中使用。正是由于这个原因，当一个存储过程中有不止一个复合语句时，我们建议使用标签。

看看清单 5 中的例子：

清单 5. 嵌套的复合块

CREATE PROCEDURE VAR_SCOPE (　)　　　　　　　
L1:BEGIN
　　DECLARE v_outer1 INT;
　　DECLARE v_outer2 INT;
　　L2:BEGIN
　　　　DECLARE v_inner1 INT;
　　　　DECLARE v_inner2 INT;
　　　　SET v_outer1 = 100;　 --(1) -- success
　　　　SET v_inner1 = 200;
　　END L2;
　　SET v_outer2 = 300;
　　SET v_inner2 = 400;　　--(2)　-- fail
END L1

构建这个 SQL 过程时，会收到以下错误消息：DB2ADMIN.VAR_SCOPE: 12: "V_INNER2" is not valid in the context where it is used.. SQLCODE=-206, SQLSTATE=42703, DRIVER=3.50.152

注意，语句 (1) 成功地通过，因为 v_outer1 是在复合语句外声明的，但是语句 (2) 失败了，因为 v_inner2 不是在复合语句外声明的。

嵌套的存储过程

调用嵌套过程

DB2 支持使用 CALL 语句在一个过程中调用另一个过程（即嵌套存储过程）。

下面的图说明了 CALL 语句的语法：

清单 6. CALL 语句的语法

>>-CALL--procedure-name--+--------------------------+----------><
　　　　　　　　　　　　 |　　.-,--------------.　　|
　　　　　　　　　　　　 |　　V　　　　　　　　|　　|
　　　　　　　　　　　　 '-(----+-expression-+-+--)-'
　　　　　　　　　　　　　　　　'-NULL-------'

注意，DB2 是强类型的，CALL 语句中的参数类型需要与调用存储过程的 CREATE PROCEDURE 语句中的参数兼容。CALL 语句中的第 n 个参数对应于定义过程的 CREATE PROCEDURE 语句中定义的第 n 个参数。

清单 7 演示了这种关系：

清单 7. CALL 语句与 CREATE PROCEDURE 语句的相互关系

CREATE PROCEDURE NESTA (p1 int, p2 char(10), OUT p3 INT)
BEGIN
SQL Statements
END

现在，从另一个过程中调用这个过程：

清单 8. 调用过程

DECLARE v_v1 varchar(10);
DECLARE v_res INT default 0;
--- SQL statements and variable assignments
CALL nesta(10, v_v1, v_res);

归纳起来，将参数传递给存储过程有以下规则：

变量和参数是强类型的（它们必须匹配）

本地变量按所在的位置匹配存储过程

所有参数必须有一个值

重载的过程由参数的数量决定

获取返回码

在嵌套的存储过程中，RETURN 语句用于通过将控制流返回给存储过程的调用者来立即终止一个 SQL 过程。RETURN 语句还可以将一个 INTEGER 值传回给调用者存储过程。如果没有提供值，默认值为 0。

DB2 支持通过 GET DIAGNOSTIC 语句获取关于之前执行的 CALL 语句的信息，如下面的清单所示。

GET DIAGNOSTICS ret_code = DB2_RETURN_STATUS;

下面的例子演示如何使用 RETURN 和 GET DIAGNOSTICS 语句。

例如，假设有 2 个不同的存储过程：

清单 9. 不同的存储过程

CREATE PROCEDURE TEST1(out v1 int)
begin
　set v1 = 10;
　return;　
end
CREATE PROCEDURE TEST2(out v1 int)
begin
　set v1 = 5;
　return 2;　
end

现在，调用这些存储过程，并检查各自的 DB2_RETURN_STATUS：

清单 10. 调用存储过程并检查 DB2_RETURN_STATUS

CREATE PROCEDURE NEST_DIAGN (out ret_code1 int, out ret_code2 int　)
P1: BEGIN
　　DECLARE val1 INT default 0;
　　call test2(val1);
　　GET DIAGNOSTICS ret_code1 = DB2_RETURN_STATUS;
　　call test1(val1);
　　GET DIAGNOSTICS ret_code2 = DB2_RETURN_STATUS;　　
END P1

如果从 DB2 命令行执行 NEST_DIAGN 存储过程，将得到以下结果：

清单 11. 从 DB2 命令行执行 NEST_DIAGN 存储过程的结果

C:Program FilesIBMSQLLIBBIN>db2 call nest_diagn(?,?)
　Value of output parameters
　--------------------------
　Parameter Name　: RET_CODE1
　Parameter Value : 2
　Parameter Name　: RET_CODE2
　Parameter Value : 0
　Return Status = 0

请注意，这里需要 DECLARE 一个变量，用于接受 DB2_RETURN_STATUS 的值。

在存储过程之间共享数据

前面的例子展示了存储过程如何使用参数和 RETURN 语句来共享数据。现在，我们来看看 2 个（或更多）存储过程如何共享来自同一个游标的相同的结果集。

过程 result_from_cursor 从 STAFF 和 ORG 表获取某个特定部门的每个工人的姓名、工作描述、佣金和位置：

清单 12. 返回结果集的过程的例子

CREATE PROCEDURE result_from_cursor (deptin int)
　　DYNAMIC RESULT SETS 1
P1: BEGIN
　　-- Declare cursor
　　DECLARE cursor1 CURSOR WITH RETURN FOR
　　　　SELECT a.name, a.job, COALESCE(a.comm,0), b.location
　　　　 FROM staff a, org b
　　　　 where a.dept = b.deptnumb
　　　　　AND　a.dept = deptin;
　　OPEN cursor1;
END P1

例如，对于部门 51，可得到以下结果集：

清单 13. 部门 51 的结果集

　NAME　　　JOB　 COMMISSION　　　LOCATION　　　
　--------- ----- --------------- -------------
　Fraye　　 Mgr　　　　　　　0.00 Dallas　　　
　Williams　Sales　　　　　637.65 Dallas　　　　
　Smith　　 Sales　　　　　992.80 Dallas　　　　
　Lundquist Clerk　　　　　189.65 Dallas　　　　
　Wheeler　 Clerk　　　　　513.30 Dallas　　　　

现在，您想在另一个存储过程中使用来自这个存储过程的结果集（不是将它存储在临时或永久表中）。

DB2 允许一个外部存储过程使用来自一个内部存储过程的结果集。下面是相应的步骤：

使用以下语法声明一个结果集定位符：

DECLARE rs_locator_var1 RESULT_SET_LOCATOR VARYING;

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

ASSOCIATE RESULT SET LOCATOR( rs_locator_var1) WITH PROCEDURE proc_called;

分配从调用过程指向结果集的游标：

ALLOCATE cursor1 CURSOR FOR RESULT SET rs_locator_var1;

下面的例子演示了所有这些方法：

清单 14. 使用来自嵌套过程的结果集

CREATE PROCEDURE Use_nested_cursor (deptin int, OUT tot_dept_comm DEC(12,2))
　BEGIN
　
　　DECLARE sqlcode int default 0;
　　DECLARE v_comm DECIMAL(12,2) DEFAULT 0.0;
　　DECLARE v_name, v_location varchar(20);
　　DECLARE v_job char(6);
　　
　　DECLARE LOC1 RESULT_SET_LOCATOR VARYING;　
　　
　　SET tot_dept_comm = 0;　
　　CALL result_from_cursor(deptin);
　　ASSOCIATE RESULT SET LOCATOR( LOC1) WITH PROCEDURE result_from_cursor;
　　ALLOCATE C1 CURSOR FOR RESULT SET LOC1;
　　FETCH FROM C1 INTO v_name,v_job,v_comm,v_location;
　　WHILE sqlcode = 0 DO
　　　　　　SET tot_dept_comm = tot_dept_comm + v_comm;　　　　　　
　　　FETCH FROM C1 INTO v_name,v_job,v_comm,v_location;
　　END WHILE;
END

现在，如果以部门 “51” 作为输入参数执行这个存储过程，将得到整个部门的佣金：

清单 15. 部门 51 作为输入参数时的结果

> call use_nested_cursor (51,?)
Value of output parameters
--------------------------
　Parameter Name: TOT_DEPT_COMM
　Parameter Value: 2333.40

通过全局变量共享数据

DB2 支持会话全局变量。会话全局变量 与一个特定的会话相关联，它对于这个会话中的每个存储过程都是全局的，并包含一个对于这个会话是惟一的值。

下面的图展示了会话全局变量的语法：

清单 16. CREATE VARIABLE 语法

CREATE VARIABLE var_name DATATYPE [DEAFULT value];

请注意，会话全局变量是在存储过程之外声明的，就像任何其他数据库对象一样。

下面的脚本演示了全局变量的使用：

清单 17. 全局变量的使用

CREATE VARIABLE global_var_count INTEGER default 0;
CREATE PROCEDURE project_count (IN var_respemp CHAR(6))　
BEGIN
　SELECT COUNT(*)
　　INTO global_var_count
　　FROM　 project
　　WHERE　respemp = var_respemp;
END
CREATE PROCEDURE PROJECT_STATUS (IN p_respemp CHAR(6),OUT p_new_status CHAR(20))
BEGIN
CALL project_count(p_respemp);
IF　global_var_count > 2
THEN
　　　SET p_new_status = 'Maximum projects' ;　　
ELSE
　　　SET　p_new_status　= 'Available';
END IF;
END

测试和部署存储过程

DB2 支持命令行处理器（CLP），它是一个部署（构建）和测试（执行）存储过程的界面。

构建存储过程的用户应该满足以下需求：

必须有执行 CREATE PROCEDURE 语句的权限

必须有执行存储过程中所有 SQL 语句的权限

这个 SQL 过程中引用的所有数据库对象（例如表、视图、函数和其他过程）都应该存在数据库中。

必须成功地建立从 CLP 到数据库的连接（这可以通过使用以下 db2 命令来完成：db2 connect to sample user userid using password ）

SQL 过程中的每个语句都需要一个语句终止符。默认的语句终止符是分号（;）。此外，为了能在一个脚本中创建 SQL 过程，并让 CLP 知道 SQL 过程在哪里结束，还需要另一个终止字符。最常用的终止字符是 “at”（_cnnew1@）和惊叹号（!）字符。

接下来，我们编写一个脚本，其中包含一个简单的 SQL 过程，并以 “@” 终止字符结束：

清单 18. 以 @ 终止符结束的 SQL 过程

CREATE PROCEDURE NUMBER_OF_ORDERS ( in_status varchar(10), in_date DATE,
　　　　　　　　　　　　　　　　　 out num_of_order int)
　　
------------------------------------------------------------------------
-- SQL Procedure
------------------------------------------------------------------------
P1: BEGIN
　　
　　declare v_number INTEGER DEFAULT 0;
　　
　　　　SELECT count(poid)
　　　　INTO v_number
　　　　　FROM PURCHASEORDER
　　　　　where ucase(status) = ucase(in_status)
　　　　　　and orderdate < in_date;
　　　　　
　　SET　num_of_order = v_number;　　　　　
END P1 @

这个脚本被保存为文件 myscript.db2。为了从 DB2 CLP 构建存储过程 number_of_orders，需要执行以下命令：

db2 -td@ -vf myscript.db2

这个 CLP 命令的一般语法如下：

db2 -td <terminating-character> -vf <CLP-script-name>

注意，选项 -td 表明默认的 CLP 终止符将被相应的终止字符重置。-vf 选项表明将使用 CLP 的可选 verbose（-v）选项，这将导致该脚本中每个 SQL 语句或命令在执行时都会显示在屏幕上，同时还显示这些执行的输出。-f 选项表明命令的目标是一个文件。

现在可以从 CLP 中执行（或测试）这个存储过程。为此，需要运行以下命令：

db2 call number_of_orders('Shipped',current date, ?)

图 3. 从 CLP 中测试过程

请注意，对于每个输入参数，需要提供一个值，对于每个输出参数，需要放置一个 “?”（问号）。

但是，如何知道返回值 5 是正确的呢？由于这个存储过程很简单，只有 1 个 SQL 语句，因此可以通过将输入参数直接放到 WHERE 子句中，从 CLP 中执行该语句，如清单 19 所示：

清单 19. 过程中的 SQL 语句

SELECT count(poid)
FROM PURCHASEORDER
　WHERE ucase(status) = 'SHIPPED'
　 AND orderdate < CURRENT DATE;

图 4. 从 CLP 中运行这个 SQL 查询

正如您看到的那样，得到的结果是相同的。对于更复杂的存储过程，测试起来则需要更多时间。不过可以使用 IBM Data Studio 工具来帮助调试存储过程。

保证 SQL 过程的安全

授权

对于 SQL 过程，基本上有 2 种类型的授权需要考虑：

存储过程的定义者 是真正创建存储过程的用户。

存储过程的调用者 是调用存储过程的用户。

要创建一个 SQL 过程，执行该任务的 userid 需要有数据库上的 BINDADD 授权以及数据库上的 IMPLICIT_SCHEMA 授权（如果存储过程的模式还不存在）或者模式上的 CREATE_IN 授权（如果存储过程的模式已经存在）。他们还需要执行在存储过程主体中定义的 SQL 所需的所有权限。

要调用一个 SQL 过程，执行该任务的 userid 需要有这个存储过程上的 EXECUTE 权限。

创建 SQL 过程的 userid 自动获得 EXECUTE 权限和 GRANT EXECUTE 权限。此外，拥有 DBADM 或 SYSADM 权限的用户也可以创建或调用一个 SQL 过程。通常建议由数据库管理员（DBA）创建存储过程，而由需要它的应用程序开发人员来调用它。

SQL 过程中的 SQL 访问级别

对于 SQL 语句，有 4 种 SQL 访问级别，它们控制 SQL 过程可以定义什么类型的 SQL 语句。它们用于向数据库管理器提供信息，以便数据库管理器安全地执行语句。

可以使用的 SQL 访问级别是：

NO SQL：存储过程中不能有 SQL 语句

CONTAINS SQL：存储过程中不能有可以修改或读数据的 SQL 语句

READS SQL：存储过程中不能有可以修改数据的 SQL 语句

MODIFIES SQL：存储过程中的 SQL 语句既可以修改数据，也可以读数据

SQL 过程的默认设置是 MODIFIES SQL。

SQL 访问级别还可以确定可以从该存储过程中调用什么类型的存储过程。一个存储过程不能调用具有更高 SQL 数据访问级别的其他存储过程。例如，被定义为 CONTAINS SQL 的存储过程可以调用被定义为 CONTAINS SQL 或 NO SQL 的存储过程。但是这个存储过程不能调用被定义为 READS SQL DATA 或 MODIFIES SQL 的其他存储过程。

SQL 访问级别可以在 CREATE PROCEDURE 语句中指定。

加密 SQL 过程

将 SQL 过程从一个服务器转移到另一个服务器是一项常见的任务。例如，一个供应商可能想将他们的 SQL 过程打包，并将那个包发送给他们的客户。如果供应商想对他们的客户隐藏或加密存储过程的内容，那么可以通过 PUT ROUTINE 和 GET ROUTINE 命令来实现。

GET ROUTINE 是一个 DB2 命令，它从数据库中提取一个 SQL 过程，并将它转换成一个 SAR（SQL Archive）文件，后者可以发送给客户。

清单 20. GET ROUTINE 命令语法

>>-GET ROUTINE--INTO--file_name--FROM--+----------+------------->
　　　　　　　　　　　　　　　　　　　 '-SPECIFIC-'　
>----PROCEDURE----routine_name--+-----------+------------------><
　　　　　　　　　　　　　　　　'-HIDE BODY-' 　　

GET ROUTINE 命令上的 HIDE BODY 子句确保 SQL 过程的主体不被提取，从而加密存储过程。

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

清单 21. PUT ROUTINE 命令语法

>>-PUT ROUTINE--FROM--file-name--------------------------------->
>--+-------------------------------------+---------------------><
　 '-OWNER--new-owner--+---------------+-'　
　　　　　　　　　　　 '-USE REGISTERS-'　　　

结束语

本教程介绍了很多您将在 DB2 9.5 SQL Procedure Developer Certification Exam（735）上见到的概念。本教程中的内容主要涵盖该考试第 2 部分 “SQL Procedures” 中的目标。

在本教程中，您学习了 DB2 存储过程，以及应用程序如何受益于 SQL 过程。您看到了如何使用 SQL PL 设计存储过程，以及如何构建和测试它们。本教程还介绍了嵌套过程的概念，并展示了如何调用它们以及如何在过程之间共享数据。通过学习如何使用这些存储过程，您将可以将复杂的业务逻辑集成到整个数据库应用程序中。