READ：分布式 DBA:掌握 SQL 存储过程

分布式 DBA:掌握 SQL 存储过程

 

Roger E. Sanders, 咨询企业系统工程师, EMC Corporation

 

简介： 要成为认证的 DB2 9.5 SQL 存储过程开发人员，应确保您了解创建和部署的各个方面。
来自IBM Database Magazine中文版。

 

 

最近，我与几个其它主题的专家合作开发一门新的 DB2 9.5 版本认证考试 “Exam 735”。参加并通过这门考试（其前提是通过 “Exam 730” DB2 9 基础知识）的人员将获得 “IBM 认证解决方案开发人员 - DB2 9.5 SQL 存储过程开发人员” 的资格。

Exam 735 设计用来测试您构建和部署存储过程、用户定义函数、完全由 SQL 语句组成的触发器和 SQL 存储过程语言（SQL PL）对象的能力。在本栏目中，我将帮助您总体复习存储过程，特别是 SQL 存储过程。此外，还向您演示如何编写 SQL 存储过程以及如何从 DB2 命令行处理器（CLP）和嵌入的 SQL 应用程序调用 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] ,...)
<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 中找到 CREATE PROCEDURE 语句的完整语法。 <CONTAINS SQL | READS SQL DATA | MODIFIES SQL DATA> 子句用来标识编码在存储过程体中的 SQL 语句的类型。可用的值如下：

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

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

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

清单 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 

 

SQL 存储过程格式

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

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

<标签：>  BEGIN <ATOMIC | NOT ATOMIC>
    变量声明
    条件声明
    游标声明
    条件处理程序声明
    赋值，流程控制，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 存储过程中的逻辑可以单独修改，所以当业务规则改变时不必重新编写应用程序。