分布式DBA：掌握SQL存储过程（二）

【转载】

来源：互联网

==================================================================

　　游标声明

　　条件处理程序声明

　　赋值，流程控制，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 ATOMIC
　　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 存储过程开发人员。