DB2 9 应用开发（733 考试）认证指南，第 9 部分: 用户定义的例程(5)-sdccf-ChinaUnix博客

随着数据库的演化，出现了基于 SQL 的本地存储过程语言。DB2 使用一种称作 SQL PL 的语言，Oracle 使用 PL/SQL，SQL Server 和 Sybase 则使用 Transact-SQL (T-SQL)。这些语言各不相同，但是却遵循一个公共的关键字、控制语句和行为框架。SQL 标准中有一节就规定了存储过程语言所能接受的关键字和行为。每个 RDBMS 供应商以不同的方式实现该标准，所以应该清楚，在这些语言之间进行移植并非易事。

本节中谈到的大多数存储过程是用 DB2 的 SQL PL 编写的，因为这些存储过程更为常见，也更容易阐释所涉及的概念。也可以用很多不同类型的外部编程语言，包括 C/C++、CLR、Java 语言和 OLE，来编写存储过程。

您可能会出于以下原因需要在应用程序中使用存储过程：

减少网络传输： 由于存储过程只在网络上发送过程调用和最终的结果，因此不必将所有的临时数据集都发送回客户机。对于客户机和服务器的地理位置相距极远的应用程序，使用存储过程可以大大提高性能。
提高性能： 存储过程是在服务器上执行的，而不是在客户机上执行的。在大多数主流架构中，服务器是非常强大的机器，处理 SQL 的速度比客户机快得多。
降低复杂性：通过将所有数据库应用程序代码放入模块单元中，可以将应用程序与数据库的交互单独放到一个层中。这样更易于进行测试、调试和维护。
责任分明：将所有数据交互单独放在存储过程中，便于 DBA 控制交互。DBA 是最了解系统的人，DBA 能够最快地对任何性能问题做出响应。
易于使用：使用存储过程编写处理数据的应用程序非常简单，便于将业务逻辑转换成应用程序逻辑。让应用程序以特定的数据格式访问数据库，降低了对应用程序编程技能的要求。

前一节提到了使用存储过程的很多优点。但是，它们在使用上也有一些限制：

存储过程可以使用任何 DB2 系统数据类型。但是存储过程的返回值只能是整数。如果需要从存储过程中返回其他数据值，那么必须使用输出参数。
不能在 SQL 语句中调用存储过程。

存储过程在封装大块相关逻辑方面最为有效。您应该将存储过程看作一个程序。如果一块代码在应用程序或其他过程中经常重复出现，那么将它封装到它自己的存储过程中可能会很有用。但是，如果该逻辑只使用一次，那么无需将其封装为存储过程。

应该尽量避免编写执行多个任务的大型存储过程，除非您能够清晰地定义该存储过程。您也可能会忍不住将所有数据库逻辑放入到一个存储过程中。但是，仅仅使用一个存储过程来返回一个表中的结果集或一行数据的做法不是很妥当。更好的做法是直接在应用程序中打开一个游标，或者直接插入一个行。

SQL 存储过程的语法类似于创建函数的语法。清单 21 展示了创建 SQL 存储过程的简化语法图：

CREATE PROCEDURE 语句的主要组成部分有：

PARAMETER TYPE: 有三种参数类型：
- IN 用于输入参数。对这些参数的更改不会传回到调用该过程的应用程序。
- OUT 用于输出参数。对这些参数的更改会传回到调用该过程的应用程序。
- INOUT 用于输入和输出。对这些参数的更改以及它们的输入值会影响存储过程和调用该过程的应用程序。
SPECIFIC: 为了在内部标识一个过程而指定的一个特定名称。如果没有指定名称，则 DB2 会赋予一个系统生成的值。对于重载存储过程，这一点特别有用。
DYNAMIC RESULT SETS: 指定保持打开并传回给调用程序或应用程序的结果集的数量。
DETERMINISTIC: 指定对于给定的参数值，该过程是否总是返回相同的结果。
LANGUAGE: SQL 过程仅支持 DB2 SQL PL 语言。

和用户定义函数一样，存储过程有三种访问数据库的模式，这些模式决定了存储过程如何与数据库交互，以及使用什么类型的 SQL：

CONTAINS SQL: 表明存储过程可以执行既不读取也不修改 SQL 数据的 SQL 语句。
READS SQL: 表明存储过程中可以包括某些不修改 SQL 数据的 SQL 语句。
MODIFIES SQL: 表明存储过程可以执行除了存储过程中不支持的 SQL 语句之外的任何 SQL 语句。

在 DB2 Information Center（见参考资料）中的 “SQL statements allowed in routines” 专题中，有一个表列出了一个 SQL 语句是否能够在具有指定 SQL 数据访问指示的例程中执行。

本教程不讨论创建存储过程的所有语义。最重要的是理解如何从命令行创建存储过程。通常，在命令行中可以这样执行包含存储过程 DLL 的脚本：

在调用包含 SQL 语句的脚本时，有以下几种不同的选项：

t: 表明使用 ; 表示一行的结束。这样一来，在脚本中，一个 SQL 语句可以写成多行。
v: 打开 verbose 模式。在该模式下，脚本输出中会再次显示所有脚本命令。这样便于发现失败的命令。当脚本文件较大时，如果没有打开 verbose 模式，那么调试起来非常花时间。
f: 指定输入文件的名称。
td: 指定使用跟在这个标志后面的字符（而不是一个分号）来界定命令的结束。

清单 22 显示了一个执行简单运算的存储过程的例子，该存储过程带有一个输入参数，并将结果存储在输出参数中：

假设该脚本存放在一个名为 myprocs.db2 的文件中，那么可以在 DB2 Command Line Processor (CLP) 中使用以下命令执行该脚本：

DB2 将在 (1) 处停下来，将其当作一个命令，并产生一个错误。为了避免这个问题，需要指定所使用的命令终止符 —— 在这里是 @ 字符。为了让上面的脚本能成功地执行，需要使用以下命令：

清单 23 和清单 24 显示了 SQL 存储过程的其他例子。第一个例子也可以编写成函数，因为它只返回具有某种教育程度的雇员的人数。

清单 24 是一个更复杂的过程，它扩展了清单 23 中的例子。它确定一个部门有多少雇员，如果部门人数少于 4 人，则给该部门中每个雇员增加薪水。

下面是关于清单 24 中的过程要注意的几点。第一点是它使用了异常和条件处理。在执行一条语句后，可以检查 SQLCODE 和 SQLSTATE 的值。为此，必须在过程的开头处将它们声明为变量。每当出现 SQL 错误（换句话说，每当语句返回负的 SQLCODE 时），就会激活条件处理程序。这个处理程序被定义为一个 EXIT 处理程序，这意味着一旦执行该处理程序的逻辑，过程即告终止。该处理程序将之前语句（即产生错误的语句）的 SQLCODE 值复制到关联的输出变量中，以便存储过程的调用者必要时可以查看这个值。如果没有定义任何类型的 SQL 异常处理程序，那么遇到错误时存储过程会立即终止。还可以定义其他类型的处理程序，例如 CONTINUE 和 UNDO 处理程序，也可以定义定制的 SQLCODE 或 SQLSTATE 以便在需要时进行处理。

另外还需要注意的一点是在复合块（BEGIN...END）中变量、游标、条件处理程序等等的声明顺序。这个顺序很重要。如果没有按预期的顺序声明各项，那么过程就不能编译。由于可以嵌套复合块，所以只要变量已在嵌套块中声明，就可以在一个过程靠后的位置声明该变量。记住，嵌套块中的项目的引用范围仅限于那个块的范围内。例如，不能在外部块中引用一个内部复合块中声明的变量。

这个过程还演示了原子复合块的使用。之前的教程也提到，对于一个原子复合块，如果块中一个子语句失败，那么整个块就被视为失败，该块中对数据库的任何更改都将回滚。而对于非原子复合块，块中每个子语句独立执行，而不管之前的子语句是否成功完成。过程最后的 COMMIT 语句提交复合块中做出的更改。ROLLBACK 语句将回滚复合块中的所有更改。

这个过程体现了 SQL PL 的一些更高级的方面，包括使用错误和条件处理、游标、循环和变量。在这么短的教程中不可能覆盖 SQL PL 语言的方方面面。但是，我强烈推荐一本书，这个书的书名是 DB2 SQL PL: Essential Guide for DB2 UDB on Linux, UNIX, Windows, i5/OS, and z/OS, 2nd Edition （见参考资料）。这本书是 SQL PL 的可靠指南，包含了对该语言各组成部分的详细解释，并提供了一些有用的例子来演示其用法。

阅读(775257) | 评论(0) | 转发(0) |

上一篇：DB2 9 应用开发（733 考试）认证指南，第 9 部分: 用户定义的例程(4)

下一篇：DB2 9 应用开发（733 考试）认证指南，第 9 部分: 用户定义的例程(6)

给主人留下些什么吧！~~

感谢所有关心和支持过ChinaUnix的朋友们

16024965号-6

存储过程