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

SQL 存储过程格式

与单个 SQL 语句不同，大部分情况中，SQL 存储过程体由一个或多个复合 SQL 语句组成。复合 SQL 语句只是由关键字 BEGIN 和 END 封装的两个或多个 SQL 语句或 SQL PL 对象，并且以分号结尾。一条 * IC 复合 SQL 语句可以认为是单个的整体吗?如果在其中产生任何未处理的错误条件，所有执行到该点的语句都被认为已经失败，并且回滚对数据库所做的任何更改。

当复合语句用来创建 SQL 存储过程体时，它可以包含几个逻辑部分。为了正确地开发一个 SQL 存储过程，使用的每个部分都必须以非常特定的顺序实现。每个逻辑部分必须依据的实现顺序如下所示： <标签：> BEGIN

变量声明

条件声明

游标声明

条件处理程序声明

赋值，流程控制，SQL语句和其它复合语句

END <标签>

正如这个格式结构所示，可选的变量、条件和条件处理程序声明必须在存储过程逻辑(使用 SQL PL 流程控制语句实现)和 SQL 语句之前。游标可以在任何地方声明，但是最好在任何条件处理程序声明之前定义。

SQL 存储过程可以由遵循此格式的一个或多个复合语句(或块)组成，这些块可以嵌套或依次执行。为了清晰地显示流程控制，每个块都可以加上标签，从而可以包含许多 SQL 语句。这使进行控制转移语句引用时更加容易实现精确性。

清单 2 显示一个其存储过程体由几个嵌套复合 SQL 语句组成的 SQL 存储过程，它们遵循刚才所述的格式。可以在 DB2 9 SQL Reference(卷 2)中的标题 “复合 SQL(存储过程)” 下找到关于这种格式的更多信息，以及如何对每个部分进行编码的详细信息和例子。

清单 2. 饱含多个子句的存储过程

CREATE PROCEDURE hr.adjust_salary

(IN empid INTEGER, IN rating INTEGER, OUT msg VARCHAR(128))

DYNAMIC RESULT SETS 1

MODIFIES SQL DATA

DETERMINISTIC

LANGUAGE SQL

main: BEGIN

DECLARE SQLSTATE CHAR(5) DEFAULT '00000';

DECLARE SQLCODE INTEGER DEFAULT 0;

DECLARE not_found CONDITION FOR SQLSTATE '02000';

DECLARE c1 CURSOR WITH RETURN FOR SELECT * FROM hr.employees;

error_handler: BEGIN

DECLARE EXIT HANDLER FOR not_found

SIGNAL SQLSTATE '20000' SET MESSAGE_TEXT = 'Employee ID not found';

work: BEGIN * IC

IF (rating = 1) THEN

UPDATE hr.employees SET salary = salary * 1.10

WHERE emp_id = empid;

ELSEIF (rating = 2) THEN

UPDATE hr.employees SET salary = salary * 1.05

WHERE emp_id = empid;

ELSEIF (rating = 3) THEN

UPDATE hr.employees SET salary = salary * 1.03

WHERE emp_id = empid;

ELSE

UPDATE hr.employees SET put_on_plan = 'Y'

WHERE emp_id = empid;

END IF;

SET msg = 'Updated record for employee with ID = ' || CHAR(empid);

END work;

END error_handler;

OPEN c1;

END main

调用 SQL 存储过程

创建 SQL 存储过程之后，就可以从另一个 SQL 存储过程或从一个客户端应用程序交互式地调用它(使用命令行编辑器或 CLP 等工具)。通过执行 CALL 语句调用 SQL 存储过程;这个语句的基本语法如下： CALL [ProcedureName] ( <[ParameterValue] | [OutputValue] | NULL> ,...)

其中：

ProcedureName 标识指定给要调用的存储过程的名称。记住，调用存储过程时必须使用存储过程名，而不是专用名。

ParameterValue 标识要传递给所调用的存储过程的一个或多个参数值。

OutputValue 标识一个或多个接收由所调用存储过程返回的值的参数标记或主机变量。

您可以从 CLP 调用清单 1 中所示的 SQL 存储过程(通过连接到合适的数据库和执行类似以下的 CALL 语句)： CALL conv_temp.f_to_c(98.6, ?)

当这个语句被执行时，值 98.6 通过名称为 TEMP_F 的输入参数传递给存储过程，问号(?)被用作一个占位符，用于将通过名称为 TEMP_C 的输出参数所返回的值。

可以从嵌入的 SQL 应用程序使用如下的 CALL 语句来调用相同的存储过程： EXEC SQL CALL conv_temp.f_to_c(98.6, :TempC)

在这种情况，TempC 是主机变量的名称，该变量使用与 REAL DB2 数据类型兼容的特定于编程语言的数据类型来声明。

效率和性能

SQL 存储过程提供有效的方法将业务规则逻辑从应用程序移动到数据库。通常，这种移动带来极大的性能提升，因为在服务器上完成处理，并且必须通过网络传输的消息更少。使用 SQL 存储过程保证在访问数据库的所有应用程序中一致地实施业务规则。并且因为 SQL 存储过程中的逻辑可以单独修改，所以当业务规则改变时不必重新编写应用程序。

不管是设计新的数据库应用程序还是只想简化日常操作，都可以寻找机会使用 SQL 存储过程。如果您发现 SQL 存储过程的开发和部署在您的工作中很有用，那么您可能想成为一名 IBM 认证的 DB2 9.5 SQL 存储过程开发人员。