DB2 9 应用开发（733 考试）认证指南，第 9 部分: 用户定义的例程(3)-sdccf-ChinaUnix博客

function-name：函数名。
RETURNS type : 所创建的函数的类型。可用的类型有 scalar、row 和 table。在后面的“标量函数”、“行函数” 和 “表函数” 这几个小节中，您将更详细地学习这几种类型。欲指定一个标量函数，只需标识返回的数据类型（不需要使用关键字 SCALAR）。
SPECIFIC：可以为函数指定一个特定的名称，而不是让 DB2 为之指定一个系统生成的惟一名称。在使用重载（overloaded）函数 —— 即具有相同名称，但是所带参数的数量不同的函数时，这一点很有用。
DETERMINISTIC：指定是否每当以相同的一组输入参数执行函数时，都返回相同的结果。确定性（Deterministic）函数包括数学函数和不依赖于表中数据或变化数据源的函数。
EXTERNAL ACTION: 指定函数对外部程序是否有影响。
[READS|CONTAINS|MODIFIES] SQL: 指定函数如何通过 SQL 与数据库交互。
SQL-function-body: 这是函数的核心，其中包含逻辑。

本节提供很多代码实例，以展示 CREATE FUNCTION 语句中各子句的意义。

RETURNS 子句确定创建的函数的类型。主要的三种类型是 scalar、row 和 table。如清单 2 中的例子所示，标量函数返回单个数据类型值：

如清单 3 中的例子所示，行函数将一个用户定义类型分解到它的不同部分中：

如清单 4 中的例子所示，表函数返回一个表的 0 到多个行。表可以在 SQL 语句中创建，也可以在编程逻辑中创建。

SPECIFIC 子句用于为函数提供一个特定的标识符。当在函数中添加注释、删除注释或者将注释变为源代码时，可以使用这个标识符。当使用重载函数时，标识符也特别有用。清单 5 中的两个函数演示了函数重载。第一个函数将两个数相加。第二个函数将字符串 new_ 与一个输入字符串拼接起来。注意，这两个函数有相同的函数名，但是输入参数的数量不一样。

当必须对函数进行维护，例如为函数添加注释或删除函数时，提供 SPECIFIC 名称的好处就很明显了。在上述情况下，仅仅发出一条 DROP FUNCTION joinData 语句还不够。DB2 不知道您要引用哪个函数。这时需要提供完整的函数签名，例如 DROP FUNCTION joinData(int, int)，以便指定想要撤销的 joinData 函数。但是，如果为函数提供一个 SPECIFIC 名称，那么只需使用那个名称来引用该函数 —— 例如 DROP SPECIFIC FUNCTION join_int2。

DETERMINISTIC 子句用于指定一个函数是否总是返回相同的值。然后，DB2 可以使用该信息来优化调用函数的方式，如果之前该函数已经执行过一次，而返回的值又是确定的，那么 DB2 可以将函数的值缓存起来。如果函数使用了专用寄存器，或者调用了非确定性函数，那么该函数就是非确定性函数。

清单 6 展示了确定性标量函数的一个例子，清单 7 展示了非确定性标量函数的一个例子：

对于每个 (x,y) 输入对，清单 6 中函数的执行结果总是一样的。但是，清单 7 中的函数并不总是产生相同的值，因为它需要获取当前日期。

该子句指定一个函数是否更改数据库之外的任何对象。如果函数要执行会产生外部影响的动作，那么必须将该选项设置为 EXTERNAL ACTION。例如，对于修改文件系统中的文件或者更改外部源中的数据的函数，就需要使用该子句。

该选项让 DB2 知道一个函数如何与数据库交互。交互方式有以下几种选择：

CONTAINS SQL: 表明函数中可以使用既不读取也不修改 SQL 数据的 SQL 语句。
READS SQL DATA: 表明函数中可以使用不修改 SQL 数据的 SQL 语句。
MODIFIES SQL DATA: 表明函数中可以使用动态复合语句中所支持的所有 SQL 语句。

函数有一些限制需要特别注意：

如果一个 SQL 函数多处引用一个日期或时间寄存器，那么所有引用都要返回相同的值。
SQL 函数的主体不能包含对其本身或者调用它的其他函数或方法的递归调用。
SQL 函数使用的语言实际上是存储过程使用的 SQL PL 语言的一个子集。因此，在存储过程中可以使用的某些语言结构在函数中不能使用。

复合 SQL 语句是包含在一个 BEGIN...END 块中的一组语句。这个块中的 SQL 语句被当作一个单元。

清单 8 显示了动态复合 SQL 块的语法图：

在以下的几个小节中，将重点介绍复合语句的一些重要的组成部分。

DECLARE 允许您在块内声明变量。其数据类型可以是除了 XML 数据类型之外的任何用户定义的类型或标准的 SQL 数据类型。如果未给定数据类型的默认值，当声明它时将自动地设置为空。以下是一些示例：

The CONDITION HANDLING：目前，函数尚不能使用该选项。

注意，并不是 SQL 存储过程中支持的所有语句在 UDF 中都受支持。而且，上面语法图中的某些语句只在表函数中受支持。还有一些语句，例如 CALL 语句，在函数中使用它们时也有一些限制。

既然过程语句在函数中的使用与在存储过程中的使用存在很多差异，下面的小节“存储过程”将讨论 SQL 复合语句的更高级的用法，并提供一些例子。

SQL 标量函数是最常见的一种 SQL 函数。它返回单个受支持的 DB2 数据类型的值。清单 9 中的简单例子演示了如何将逻辑嵌入到一个函数中，而不是嵌入到一个客户机应用程序中。函数 CHANGESAL 是使用一行过程代码创建的： RETURN sal * 2。其他部分则构成了函数的定义。该函数以一个雇员的薪水（一个 DOUBLE 值）作为输入。它也可以接受其他数字型值，例如一个 INTEGER，因为 DB2 会隐式地进行类型强制转换。

清单 10 展示了如何将函数作为 SQL 语句的一部分执行：

标量函数通常比这个例子更复杂一些，一般会包含更复杂的逻辑和其他 SQL 语句。清单 11 展示了一个更复杂的标量函数，该函数返回达到所要求的教育程度的雇员数量，要求的教育程度是在函数的输入部分指定的：

然后，可以在一条 SQL 语句中使用该函数，如下面的清单 12 所示：

在后台，当调用 SQL 函数时，DB2 接受函数逻辑，并将其内联（in-line）到 SQL 语句中。这意味着，SQL 语句中的函数调用实际上被函数逻辑替代。于是，DB2 优化器会根据整个语句，而不是语句的一部分来创建最佳访问计划。这样可以得到更好的总体访问计划。例如，清单 13 显示了根据清单 10 重新编写的 SQL 语句：

与原先简单的 SQL 语句相比，清单 13 中显示的 SQL 语句的内联要更复杂一些。清单 14 显示了重新编写的语句：

行函数并不是只返回一行数据，所以不能望文生义。实际上，行函数用于将一个结构化数据类型转换成它的各个组件。用户定义的结构化类型（UDST）是用户定义的包含对一个或多个 DB2 数据类型的引用的数据类型。因此，如果在数据库中使用 UDST，那么只能使用行函数。行函数只能被定义为 SQL 函数。

清单 15 中的 PERSON 对象就是一个 UDST 的例子。它包含一个 lastName 字段和一个 firstName 字段。行函数 FROMPERSON 可以用于从 PERSON 类型的实例中提取特定的字段。

DB2 函数一个更为强大的方面是它们能够返回整个数据表，而非单个值。这将打开您可在 SQL 语句中使用的许多信息源。您不用指向一个数据库表，而是可以编写 C 函数以指向实时数据流，例如股票市场的数据。

表函数实际上很容易编写。表函数不像标量函数那样只返回一个数据值，而是返回一个表中的多行数据，如清单 16 所示：

该函数枚举一个部门中的一群雇员。它接收一个 VARCHAR 类型的输入参数。该函数返回的表由 4 个列组成，第一列是 INTEGER 类型，其余列是 VARCHAR 类型。该函数返回 SELECT 语句所定义的一组行。 SELECT 语句的第一个列是一个特殊表达式，它使用 DB2 的聚合函数。该表达式为每一行返回一个整数值，这个值从 1 开始，逐行加 1。其他列的值是从 EMPLOYEE 表中提取的，但是只适用于部门编号与输入参数的值匹配的行。可以看到， ROW_NUMBER() OVER() 表达式非常便于为结果集生成一个连续的数字序列 —— 实际上，是为结果集中的每一行编号。

欲调用一个表函数，必须在查询的 FROM 子句中引用它，并将它包装在名为 TABLE 的函数中。清单 17 演示了如何调用清单 16 中所示的表函数：

当使用表函数时，要记住一些限制。首先，必须知道函数将返回的表中的列数和数据类型。如果一个函数引用一个表的所有列，但是后来那个表又增加了列，那么该函数可能不会按预期运行。例如，假设创建了清单 18 中所示的表和函数，然后又使用一个 ALTER 语句为那个表添加了一列：

在这种情况下，对该函数的调用不再按预期的那样返回包含所有三个列的表，而是只返回该表创建时定义的两个列。之所以会出现这种情况，是因为函数的定义中使用了 *，该符号是在创建时解析的，而不是在运行时解析的。

阅读(372281) | 评论(0) | 转发(0) |

上一篇：DB2 9 应用开发（733 考试）认证指南，第 9 部分: 用户定义的例程(2)

下一篇：DB2 9 应用开发（733 考试）认证指南，第 9 部分: 用户定义的例程(4)

给主人留下些什么吧！~~

感谢所有关心和支持过ChinaUnix的朋友们

16024965号-6

用户定义的 SQL 函数

                    
>>-CREATE FUNCTION--function-name------------------------------->
 
>--(--+--------------------------------+--)--*------------------>
      | .-,--------------------------. |
      | V                            | |
      '---parameter-name--data-type1-+-'
 
>--RETURNS--+-data-type2-----------------+--*------------------->
            '-+-ROW---+--| column-list |-'
              '-TABLE-'
 
                                   .-LANGUAGE SQL-.
>--+-------------------------+--*--+--------------+--*---------->
   '-SPECIFIC--specific-name-'
 
   .-NOT DETERMINISTIC-.     .-EXTERNAL ACTION----.
>--+-------------------+--*--+--------------------+--*---------->
   '-DETERMINISTIC-----'     '-NO EXTERNAL ACTION-'
 
   .-READS SQL DATA---------.     
>--+------------------------+--*--+-----------------+--*-------->
   +-CONTAINS SQL-----------+
   |                        |
   '-MODIFIES SQL DATA------'
 
>--| SQL-function-body |--------------------------------------->
 
column-list:
 
      .-,-----------------------.
      V                         |
|--(----column-name--data-type3-+--)----------------------------|
 
SQL-function-body:
 
|--+-RETURN Statement-----------+-------------------------------|
   '-dynamic-compound-statement-'

CREATE FUNCTION 语句的最常见的子句是：

function-name：函数名。
RETURNS type : 所创建的函数的类型。可用的类型有 scalar、row 和 table。在后面的“标量函数”、“行函数” 和 “表函数” 这几个小节中，您将更详细地学习这几种类型。欲指定一个标量函数，只需标识返回的数据类型（不需要使用关键字 SCALAR）。
SPECIFIC：可以为函数指定一个特定的名称，而不是让 DB2 为之指定一个系统生成的惟一名称。在使用重载（overloaded）函数 —— 即具有相同名称，但是所带参数的数量不同的函数时，这一点很有用。
DETERMINISTIC：指定是否每当以相同的一组输入参数执行函数时，都返回相同的结果。确定性（Deterministic）函数包括数学函数和不依赖于表中数据或变化数据源的函数。
EXTERNAL ACTION: 指定函数对外部程序是否有影响。
[READS|CONTAINS|MODIFIES] SQL: 指定函数如何通过 SQL 与数据库交互。
SQL-function-body: 这是函数的核心，其中包含逻辑。