分布式DBA：SQL存储过程知识总结

DB2 存储过程：基础知识

您在客户端工作站上对远程服务器和位于该服务器上的数据库进行分类的任何时候，都存在一个简单的 DB2 客户端/服务器环境。在这种环境中，每次对远程服务器上的数据库执行 SQL 语句时，语句本身通过网络从客户端发送到服务器上的数据库。然后数据库处理语句，结果通过网络发送回客户端。这意味着，对于每条被执行的 SQL 语句，两条消息都必须经过网络。因此，执行大量 SQL 操作的应用程序将产生大量网络通信。

存储过程是编写并直接存储在数据库中的命名 SQL 语句组(在一些情况中，包括操作系统调用)。存储过程提供下列优势：

减少网络通信量。对于编码在存储过程中的 SQL 语句，消息不通过网络发送。如果存储过程设计正确，那么只有客户端应用程序需要的数据才通过网络发送。

提高服务器密集型工作的性能。因为较少数据通过网络发送，并且因为处理在服务器上完成，所以复杂查询和其它服务器密集型的工作可以更快地执行。

业务逻辑的分离和重用。当业务规则被合并到存储过程中时，可以仅仅根据需求调用存储过程来多次重用逻辑。此外，保证相同的业务规则逻辑在所有使用它的应用程序中一致实施。如果业务规则改变，那么只需要改变存储过程中的逻辑;不需要更改调用存储过程的应用程序。

访问服务器功能。因为存储过程在服务器工作站上直接运行，所以它们可以利用任何额外的内存、更快的处理器或数据库服务器可能具备的其它资源。另外，存储过程可以执行许多 DB2 的管理命令，这些命令只能在服务器上运行。最后，因为存储过程不仅仅限于执行数据库的活动，所以它们的优点是可以利用已经安装在服务器上的任何附加软件。

但是，有两个需要注意的地方。第一，所有输入数据都必须在调用时从应用程序传递到存储过程。第二，存储过程生成的结果数据集只有在存储过程完成执行后才返回给应用程序。换句话说，在存储过程运行期间，应用程序和存储过程之间不能发生任何交互。

创建 SQL 存储过程

对于 Linux、Unix 和 Windows 平台上的 DB2，可以使用三种不同类型的存储过程：SQL 的、外部的和来源(sourced)的存储过程。正如名称所示，SQL 存储过程完全由 SQL 语句和 SQL PL 对象组成。相反，外部存储过程使用 C、C++、Java 或 COBOL 等高级编程语言构成。来源存储过程是基于其它 SQL 或外部存储过程的存储过程。

存储过程通过执行 CREATE PROCEDURE SQL 语句创建。这个语句存在三种形式(每种类型的存储过程有一种);用来创建 SQL 存储过程的形式的基本语法如下： CREATE PROCEDURE [ProcedureName] ( [ParamType] [ParamName] [DataType] ,...)

CREATE PROCEDURE [ProcedureName] ( [ParamType] [ParamName] [DataType] ,...)

＜SPECIFIC [SpecificName]＞

＜DYNAMIC RESULT SETS 0 | DYNAMIC RESULT SETS [NumResultSets]＞

＜CONTAINS SQL | READS SQL DATA | MODIFIES SQL DATA＞

＜DETERMINISTIC | NOT DETERMINISTIC＞

＜CALLED ON NULL INPUT＞

＜LANGUAGE SQL＞

[ProcedureBody]

其中：

ProcedureName 标识指定给存储过程的名称。

ParamType 指示 ParamName 标识的参数是输入参数(IN)、输出参数(OUT)或两者(INOUT)都是。

ParamName 标识指定给存储过程参数的名称。

DataType 标识存储过程期望为 ParamName 标识的参数接收和/或发送的数据类型。

SpecificName 标识指定给存储过程的专用名。当一个专用名被指定给存储过程时，可以通过在特殊形式的 DROP SQL 语句(DROP SPECIFIC PROCEDURE [SpecificName])中引用专用名来删除存储过程。但是，如果没有指定任何专用名，那么必须同时提供存储过程名称和存储过程签名(也就是每个存储过程参数使用的数据类型的列表)来作为 DROP 语句的输入。专用名不能用来调用存储过程。

NumResultSets 指示存储过程返回结果数据集并标识返回多少数据集。

ProcedureBody 标识调用存储过程时要执行的单个 SQL 语句或者一个或多个复合 SQL 语句。

注意：方括号([])中显示的参数或选项必填写;尖括号(<>)中显示的参数/选项不是必填的。可以在 DB2 9 SQL Reference - 卷 2(参见参考资料，第 51 页)中找到 CREATE PROCEDURE 语句的完整语法。 子句用来标识编码在存储过程体中的 SQL 语句的类型。可用的值如下：

CONTAINS SQL。存储过程体包含既不读取数据也不修改数据的可执行 SQL 语句。

READS SQL DATA。存储过程体包含读取数据但不修改数据的可执行 SQL 语句。

MODIFIES SQL DATA。存储过程体包含既读取数据也修改数据的可执行 SQL 语句。

子句用来标识当传递相同(DETERMINISTIC)或不同(NOT DETERMINISTIC)的参数值时存储过程是否始终返回相同的结果。例如，对传递给它的任何值增加 15％ 的存储过程将被视为 DETERMINISTIC，而使用 TIMESTAMP_ISO() 函数生成唯一 ID 的存储过程将被视为 NOT DETERMINISTIC。

最后， 子句指示存储过程即使在为一个或多个输入参数提供空值进行调用时仍然被调用。

清单 1 显示一个由简单的 SQL 存储过程构成的 CREATE PROCEDURE 语句，设计用来将温度从华氏温度转换成摄氏温度。

清单 1. 创建简单的 SQL 存储过程

CREATE PROCEDURE conv_temp.f_to_c(IN temp_f REAL, OUT temp_c REAL)

DYNAMIC RESULT SETS 0

CONTAINS SQL

DETERMINISTIC

LANGUAGE SQL

BEGIN

DECLARE temp_value REAL;

SET temp_value = (temp_f - 32);

SET temp_c = (5 * temp_value) / 9;

END