处理 DB2 数据 SQL 过程和用户定义的函数

创建和调用 SQL 过程

SQL 过程 是过程体用 SQL 编写的过程。过程体包含 SQL 过程的逻辑。它可以包含变量声明、条件处理、流控制语句和 DML。可以在复合语句（compound statement） 中指定多个 SQL 语句，复合语句将几个语句组合成一个可执行块。

当成功地调用 CREATE PROCEDURE (SQL) 语句时，就会创建一个 SQL 过程，这会在应用服务器上定义 SQL 过程。SQL 过程是一种定义比较复杂的查询或任务的简便方式，可以在需要时调用它们。

创建 SQL 过程的一种简便方法是在命令行处理程序（CLP）脚本中编写 CREATE PROCEDURE (SQL) 语句。例如，如果将下面的语句放在一个称为 createSQLproc.db2 的文件中，就可以执行这个文件来创建 SQL 过程：

1. 连接 SAMPLE 数据库。
2. 发出以下命令：

   db2 -td@ -vf createSQLproc.db2
                   

   
   

db2 命令指定 -td 选项标志，这让命令行处理程序使用 @ 作为语句终止字符（因为在过程体内已经使用分号作为语句终止字符）；-v 选项标志让命令行处理程序将命令文本回显到标准输出；-f 选项标志让命令行处理程序从指定的文件（而不是标准输入）读取命令输入。

 

CREATE PROCEDURE sales_status
            (IN quota INTEGER, OUT sql_state CHAR(5))
            DYNAMIC RESULT SETS 1
            LANGUAGE SQL
            BEGIN
            DECLARE SQLSTATE CHAR(5);
            DECLARE rs CURSOR WITH RETURN FOR
            SELECT sales_person, SUM(sales) AS total_sales
            FROM sales
            GROUP BY sales_person
            HAVING SUM(sales) > quota;
            OPEN rs;
            SET sql_state = SQLSTATE;
            END @
            

这个过程称为 SALES_STATUS，它接受一个输入参数 quota 并返回输出参数 sql_state。过程体中只有一个 SELECT 语句，它返回销售总量超过指定额度的销售人员的姓名和销售总量。

大多数 SQL 过程接受至少一个输入参数。在我们的示例中，输入参数包含一个值（quota），这个值用在过程体包含的 SELECT 语句中。

许多 SQL 过程返回至少一个输出参数。我们的示例包含一个输出参数（sql_state），这个参数用来报告 SQL 过程是成功还是失败。DB2 返回一个 SQLSTATE 值来响应条件，可以作为 SQL 语句的结果。因为返回的 SQLCODE 或 SQLSTATE 值属于过程体中发出的最后一个 SQL 语句，而且访问这些值会改变这些变量的后续值（因为访问它们需要使用 SQL 语句），所以应该将 SQLCODE 或 SQLSTATE 值赋值给一个局部定义的变量并通过它返回（比如我们示例中的 sql_state 变量）。

SQL 过程的参数列表可以指定零个或更多的参数，每个参数可以是三种类型之一：

• IN 参数将一个输入值传递给 SQL 过程；在过程体内不能修改这个值。
• OUT 参数从 SQL 过程返回一个输出值。
• INOUT 参数将一个输入值传递给 SQL 过程并从 SQL 过程返回一个输出值。

 

SQL 过程可以返回零个或更多的结果集。在我们的示例中，SALES_STATUS 过程返回一个结果集。返回结果集的方法是：

1. 在 DYNAMIC RESULT SETS 子句中声明 SQL 过程返回的结果集数量。
2. 在过程体中为返回的每个结果集声明一个游标（使用 WITH RETURN FOR 子句）。游标（cursor） 是一个命名的控制结构，应用程序使用它指向有序行集中的特定行。游标用来从行集中检索行。
3. 打开返回的每个结果集的游标。
4. 当 SQL 过程返回时，让游标打开着。

 

变量必须在 SQL 过程体的开头进行声明。要声明 一个变量，应该分配一个惟一的标识符并指定变量的 SQL 数据类型，还可以可选地分配一个初始值。

我们的 SQL 过程示例中的 SET 子句是一个流控制 子句。在 SQL 过程体中可以使用以下的流控制语句、结构和子句来进行有条件处理：

• CASE 结构根据对一个或多个条件的计算选择一个执行路径。
• FOR 结构对于表中的每一行执行一个代码块。
• GET DIAGNOSTICS 语句将关于前一个 SQL 语句的信息返回到一个 SQL 变量中。
• GOTO 语句将控制转移到一个有标签的块（一个或多个语句的块，由一个惟一的 SQL 名称和冒号来标识）。
• IF 结构根据对条件的计算选择一个执行路径。ELSEIF 和 ELSE 子句允许执行分支，或指定在其他条件不满足时执行的默认操作。
• ITERATE 子句将流控制传递到一个有标签的循环的开头。
• LEAVE 子句使程序控制离开一个循环或代码块。
• LOOP 子句多次执行一个代码块，直到 LEAVE、ITERATE 或 GOTO 语句使控制离开循环。
• REPEAT 子句重复执行一个代码块，直到指定的搜索条件返回真为止。
• RETURN 子句将控制从 SQL 过程返回给调用者。
• SET 子句将一个值赋值给一个输出变量或 SQL 变量。
• WHILE 在指定的条件为真时重复执行一个代码块。

 

要想成功地创建 SQL 过程，必须在数据库服务器上安装 DB2 Application Development Client。（关于 Application Development Client 的更多信息见 本系列中的第一个教程。）以前需要用 C 编译器来创建 SQL 过程，这种依赖性在 DB2 Universal Database Version 8 中已经消除了。以前依赖于 C 编译器的所有操作现在由驻留在虚拟机中的 DB2 生成的字节码执行。关于这个改进的更多信息见 参考资料。

使用 SQL CALL 语句从 DB2 命令行调用 SQL 过程。被调用的过程必须在系统编目中进行定义。用任何支持的语言编写的客户机应用程序都可以调用 SQL 过程。为了调用 SQL 过程 SALES_STATUS，执行以下步骤：

1. 连接 SAMPLE 数据库。
2. 发出以下语句：

   db2 CALL sales_status (25, ?)
                   

   
   因为圆括号对于基于 UNIX 的系统上的命令 shell 有特殊意义，所以在这些系统上必须在它们前面加上反斜线（\）字符，或者用双引号包围它们：

   db2 "CALL sales_status (25, ?)"
                   

   
   如果以交互输入模式使用命令行处理程序（CLP）（由 db2 => 输入提示表示），那么不必包含双引号。

 

在这个示例中，值 25 作为输入参数 quota 传递给 SQL 过程，并使用问号（?）作为输出参数 sql_state 的占位符。这个过程返回销售总量超过指定额度（25）的每个销售人员的姓名和销售总量。下面是这个语句返回的输出示例：

SQL_STATE: 00000

SALES_PERSON	TOTAL_SALES
GOUNOT	50
LEE	91
"SALES_STATUS" RETURN_STATUS: "0"

回页首

创建和使用 SQL 用户定义函数

可以创建用户定义函数来扩展内置的 DB2 函数。例如，创建计算复杂的算术表达式或操作字符串的函数，然后在 SQL 语句中像对待任何现有的内置函数一样引用这些函数。

假设需要一个返回圆的面积的函数，这个函数的输入参数是圆的半径。内置的 DB2 函数中没有这样的函数，但是可以创建一个用户定义的 SQL 标量函数 来执行这个任务，可以在 SQL 语句中支持标量函数的任何地方引用这个函数。

CREATE function ca (r DOUBLE)
            RETURNS DOUBLE
            LANGUAGE SQL
            CONTAINS SQL
            NO EXTERNAL ACTION
            DETERMINISTIC
            RETURN 3.14159 * (r * r);
            

NO EXTERNAL ACTION 子句指出这个函数不会对数据库管理程序不管理的对象的状态有任何影响。DETERMINISTIC 关键字指出这个函数对于给定的参数值总是返回相同的结果。在查询优化期间会使用这个信息。执行这个函数的简便方法是在一个查询中引用它。在下面的示例中，针对 SYSIBM.SYSDUMMY1 编目视图（其中只有一行）执行这个查询（可以选择任意的查询目标）：

db2 SELECT ca(96.8) AS area FROM sysibm.sysdummy1
            AREA
            ------------------------
            +2.94374522816000E+004
            1 record(s) selected.
            

还可以创建用户定义的表函数，它接受零个或更多的输入参数并以表的形式返回数据。表函数只能用在 SQL 语句的 FROM 子句中。

假设需要一个返回拥有特定工作的所有职员的姓名和职员号的函数，函数的参数是这个工作的头衔。下面是执行这个任务的表函数示例：

CREATE FUNCTION jobemployees (job VARCHAR(8))
            RETURNS TABLE (
            empno CHAR(6),
            firstname VARCHAR(12),
            lastname VARCHAR(15)
            )
            LANGUAGE SQL
            READS SQL DATA
            NO EXTERNAL ACTION
            DETERMINISTIC
            RETURN
            SELECT empno, firstnme, lastname
            FROM employee
            WHERE employee.job = jobemployees.job;
            

以下查询在 FROM 子句中引用这个新的表函数，并传递工作头衔 ‘CLERK’ 作为函数的参数。语法要求用关键字 AS 引入一个相关名称：

db2 SELECT * FROM TABLE(jobemployees('CLERK')) AS clerk
            EMPNO  FIRSTNAME    LASTNAME
            ------ ------------ ---------------
            000120 SEAN         O'CONNELL
            000230 JAMES        JEFFERSON
            000240 SALVATORE    MARINO
            000250 DANIEL       SMITH
            000260 SYBIL        JOHNSON
            000270 MARIA        PEREZ
            6 record(s) selected.