DB2 9.5 SQL Procedure Developer 认证考试 735 准备，第 3 部分

核心提示：开始之前关于本系列这六个 DB2 SQL Procedure Developer 教程讨论 SQL Procedural Language 的所有基本构造和方法，讲解如何在存储过程、UDF 和触发器中使用 SQL Procedural Language，DB2 9.5 SQL Procedure Developer 认

开始之前

关于本系列

这六个 DB2 SQL Procedure Developer 教程讨论 SQL Procedural Language 的所有基本构造和方法，讲解如何在存储过程、UDF 和触发器中使用 SQL Procedural Language，包括错误处理和部署。还讨论一些 DB2 9.5 高级特性，比如乐观锁、层次化查询和声明的全局临时表。本系列讨论如何调用存储过程、UDF 和触发器，以及如何在过程和函数之间共享数据。它介绍 DB2 开发工具，包括 IBM Data Studio。这些教程为您准备考试的每个部分提供坚实的基础。但是，您不应该仅仅依靠这些教程准备考试。

关于本教程

本教程深入讲解 UDF，重点讨论 SQL 函数。这是共分六部分的系列中的第三个教程，本系列帮助您准备 IBM DB2 9.5 SQL Procedure Developer 认证考试（考试 735）。

目标

在完成本教程之后，您应该能够：

掌握函数的正确使用方法

使用 CREATE FUNCTION 语句创建 SQL 函数

掌握 SQL 函数体的正确结构

从 SQL 函数返回值和表

调用函数

前提条件

要想参加 DB2 9.5 SQL Procedure Developer 考试，您必须先通过 DB2 9 基础（730 考试）。可以使用 "DB2 9 基础" 教程系列准备此考试。

本教程是为初级和中级 DB2 程序员撰写的。您应该基本了解关系数据库的工作方式以及数据库和数据库编程构造。此外，还应该熟悉 DB2 Command Line Processor (CLP) 的使用方法，了解 SQL 的基本知识。

系统需求

要想运行本教程中的示例，需要访问 DB2 9.5 数据库服务器和 DB2 提供的 SAMPLE 数据库。（可以通过 DB2 Command Line Processor 执行命令 db2sampl 来创建 SAMPLE 数据库）。

什么是函数？

在 DB2 中，函数是一组封装的指令，用来执行一个特定的操作；可以使用一个或多个输入参数来定制操作，可以使用一个或多个输出参数来返回结果。有四种函数类型：

标量函数

聚合函数

表函数

行函数

DB2 提供许多健壮的内置函数，这些函数在 SYSIBM 模式中定义。可用的内置函数包括标量函数（比如 UCASE()）、聚合函数（比如 AVG()）、操作符函数（比如 “+”）和转换函数（比如 DECIMAL()）。通常在查询的选择列表和 FROM 子句中调用函数。

标量函数

标量函数 是返回一个标量值的函数。可以使用标量函数执行简单任务，也可以在标量函数中使用通过函数输入参数提供的值进行复杂的数学计算。标量函数的示例包括内置函数 LENGTH() 和 SUBSTR()。

在 SQL 语句中支持表达式的任何地方都可以引用标量函数。当在查询谓词中使用时，标量函数可以改进总体性能，因为函数的逻辑作为引用它的 SQL 语句的组成部分在服务器上直接执行。另外，当在服务器上对一组候选行应用标量函数时，它可以起到过滤器的作用，限制必须返回给客户机的行数。但是，标量函数也有其局限性。例如，根据设计，标量函数只能返回一个值，无法返回多个值和结果集。另外，在标量函数中不支持事务管理。因此，在标量函数体中无法执行提交和回滚操作。

标量函数通常用来执行基本的数学计算和操作字符串。

聚合函数

聚合函数（也称为列函数）也返回一个标量值，这个值是一组输入值的计算结果。通常情况下，这组输入值来自表中的一列，或来自 VALUES 子句中的元组。聚合函数的示例包括内置函数 MAX() 和 MIN()。

表函数

表函数 向引用它的 SQL 语句返回一个表。只能在 SELECT 语句的 FROM 子句中引用表函数。但是，在能够应用于只读视图的任何操作（例如，联结、分组操作以及 UNION 和 INTERSECT 等集操作）中，都可以使用表函数返回的表。

表函数可以发出操作系统调用、从文件中读取数据以及通过网络访问数据。另外，SQL 表函数还可以用来封装修改表数据的 SQL 语句。（外部表函数不能封装 SQL 语句）。但是，与标量函数一样，在表函数中不支持事务管理。因此，在表函数体中无法执行提交和回滚操作。

表函数通常用来封装复杂但常用的子查询，以及为非关系数据提供表格式接口。例如，用户定义的外部表函数可以读取电子表格并生成一个数据表，然后可以把生成的数据表直接插入基表或者通过查询访问它。

行函数

行函数 返回单一表行。行函数只能供用户定义的结构化类型使用；不能单独使用行函数，也不能在抽象数据类型的上下文之外的 SQL 语句中使用。

通常，行函数用于把结构化类型属性映射到由内置数据类型值组成的行，以便能够在查询或 SQL 操作中访问结构化类型属性。例如，假设数据库有一个用户定义的结构化数据类型 'Manager'，此类型扩展另一个结构化数据类型 'Employee'，其中包含 'Employee' 和 'Manager' 特有的属性。如果希望在查询中引用属性值，那么可以创建一个行函数，它把属性值转换为查询可以引用的数据行。

用户定义的函数

UDF 是用来扩展和增强 DB2 内置函数提供的功能的特殊对象。顾名思义，UDF 是由具有适当权力和/或特权的数据库用户创建的。与 DB2 内置函数不同，UDF 可以使用系统调用和 DB2 的管理 API，因此能够在系统、应用程序和数据库之间进行更多的协作。可以创建五种 UDF：

有源（或模板）

SQL 标量、表或行

外部标量

外部表

OLE DB 外部表

通过执行 CREATE FUNCTION SQL 语句创建（或重新创建）UDF。这个语句有几种形式，应该使用的形式由要创建的函数的类型决定。

有源（或模板）函数

有源函数是从一个已经向数据库注册的函数（称为源函数）构造出的 UDF。有源函数在本质上可以是标量、列或表函数，还可以用它们覆盖 +、-、* 和 / 等操作符。在调用有源函数时，传递给它的所有参数被转换为底层源函数所需的数据类型，然后执行源函数本身。执行完成时，源函数对产生的结果执行必要的转换，并把控制返回给调用 UDF 的 SQL 语句。有源函数最常见的用途是，让用户定义的不同数据类型有选择地继承内置数据类型的某些语义。

通常，如果以现有函数作为源的 UDF 的目的是向用户定义数据类型提供该函数的支持，那么 UDF 和有源函数使用相同的名称。这使用户能够用一个用户定义的不同类型调用相同的函数，而不需要额外的函数定义。一般情况下，多个函数可以使用相同的函数名，只要每个函数的签名都有些差异即可。

用来创建有源函数的 CREATE FUNCTION 语句形式的基本语法如下：

清单 1. 用来创建有源函数的 CREATE FUNCTION 语句

　CREATE FUNCTION [FunctionName] ( <<[ParameterName]> [InputDataType] ,...> )
　　RETURNS [OutputDataType]
　　<SPECIFIC [SpecificName]>
　　SOURCE [SourceFunction] <([DataType] ,...)>
　　<AS TEMPLATE>

其中：

FunctionName 指定要创建的有源函数的名称。

ParameterName 指定一个或多个函数参数的名称。

InputDataType 指定 ParameterName 所识别的参数所需的数据类型。

OutputDataType 指定函数返回的数据的类型。

SpecificName 指定分配给这个 UDF 的特定名称。这个名称可以用来引用或删除函数；但是，不能用来调用函数。

SourceFunction 指定用来创建这个有源函数的现有函数。

DataType 指定现有函数的每个参数期望接收的数据类型。

在使用这种形式的 CREATE FUNCTION 语句时，如果指定了 SPECIFIC 子句，就会给创建的 UDF 分配一个惟一的名称。创建函数之后，可以通过在特殊形式的 DROP SQL 语句 (DROP SPECIFIC FUNCTION [SpecificName]) 中引用这个特定名称来删除函数。但是，如果不给 UDF 分配特定名称，那么必须在 DROP FUNCTION 语句中提供函数名和函数的签名（即放在圆括号中的所有函数参数的数据类型列表）。

如果指定 AS TEMPLATE 子句，那么会生成一个函数模板。函数模板是一个不完整的函数，它定义了要返回的数据类型，但是不包含可执行代码。在联邦系统中，函数模板被映射到一个数据源（例如 Oracle 或 SQL Server）函数，这样就可以从联邦数据库调用数据源函数。只能在指定为联邦服务器的数据库服务器上注册函数模板。

因此，如果基于内置数据类型 INTEGER 创建了一个名为 YEAR 的数据类型，希望创建一个有源函数 AVG()，它应该接收并返回 YEAR 值，而且它基于内置函数 AVG()（此函数接收并返回 INTEGER 值），那么可以执行下面的 CREATE FUNCTION 语句：

　CREATE FUNCTION AVG(YEAR) RETURNS YEAR
　　SOURCE SYSIBM.AVG(INTEGER)

SQL 函数

SQL 函数是只使用过程式 SQL 语句构造的 UDF。SQL 函数可以是标量函数，也可以返回一行或整个表。

用来创建 SQL 函数的 CREATE FUNCTION 语句形式的基本语法如下：

清单 2. 用来创建 SQL 函数的 CREATE FUNCTION 语句

　CREATE FUNCTION [FunctionName] ( <<[ParameterName]> [InputDataType] ,...> )
　　RETURNS [[OutputDataType] |
　　　 TABLE ( [ColumnName] [ColumnDataType] ,... ) |
　　　 ROW ( [ColumnName] [ColumnDataType] ,... )]
　　<SPECIFIC [SpecificName]>
　　<LANGUAGE SQL>
　　<DETERMINISTIC | NOT DETERMINISTIC>
　　<EXTERNAL ACTION | NO EXTERNAL ACTION>
　　<CONTAINS SQL | READS SQL DATA | MODIFIES SQL DATA>　　
　　<STATIC DISPATCH>　　
　　<CALLED ON NULL INPUT>　　　　
　　[SQLStatements] | RETURN [ReturnStatement]　　　

其中：

FunctionName 指定要创建的 SQL 函数的名称。

ParameterName 指定一个或多个函数参数的名称。

InputDataType 指定 ParameterName 所识别的参数所需的数据类型。

OutputDataType 指定函数返回的数据的类型。

ColumnName 指定函数返回的一列或多列的名称（如果此函数返回表或行的话）。

ColumnDataType 指定 ColumnName 所识别的列返回的数据类型。

SpecificName 指定分配给这个 UDF 的特定名称。这个名称可以用来引用或删除函数；但是，不能用来调用函数。

SQLStatements 指定在调用函数时执行的一个或多个 SQL 语句。这些语句组合成一个动态复合 SQL 语句。

ReturnStatement 指定用于返回调用函数的应用程序的 RETURN SQL 语句。（如果 SQL 函数体由动态复合语句组成，那么它必须包含至少一个 RETURN 语句；在调用函数时，必须执行一个 RETURN 语句。如果函数是表函数或行函数，那么只能包含一个 RETURN 语句，而且此语句必须是使用的最后一个语句）。

可以看到，这种形式的 CREATE FUNCTION 语句包含几个在前一种形式中没有出现的子句。在许多情况下，这些子句传达的信息不太直观，所以我们先详细讨论这些子句，然后再看一个示例。

<LANGUAGE SQL> 指定函数是用 SQL 编写的。

<DETERMINISTIC | NOT DETERMINISTIC> 表示在用相同的参数值调用函数时，函数是否总是返回相同的标量值、表或行（DETERMINISTIC 代表确定性函数，NOT DETERMINISTIC 表示非确定性函数）。如果没有指定这两个子句，那么在用相同的参数值调用函数时函数可能返回不同的结果。

<EXTERNAL ACTION | NO EXTERNAL ACTION> 表示函数执行的操作是否会改变不由 DB2 管理的对象的状态（EXTERNAL ACTION 表示会改变，NO EXTERNAL ACTION 表示不改变）。外部操作包括发送电子邮件或在外部文件中写记录等。如果没有指定这两个子句，那么意味着函数可能执行某种外部操作。

<CONTAINS SQL | READS SQL DATA | MODIFIES SQL DATA> 表示在 UDF 体中编写的 SQL 语句的类型。有三个值可用：

CONTAINS SQL：UDF 体包含的可执行 SQL 语句既不读数据，也不修改数据。

READS SQL DATA：UDF 体包含的可执行 SQL 语句读数据，但是不修改数据。

MODIFIES SQL DATA：UDF 体包含的可执行 SQL 语句既可以读数据，也可以修改数据。

<STATIC DISPATCH> 表示在函数解析时 DB2 根据函数参数的静态类型（声明的类型）选择函数。

<CALLED ON NULL INPUT> 表示无论任何参数是否包含 null 值，都调用此函数。

因此，如果希望创建一个名为 JULIAN_DATE() 的标量 SQL 函数，用于将 DB2 日期转换为儒略日期（自公元前 4713 年 1 月 1 日以来的天数），那么可以执行下面的 CREATE FUNCTION 语句：

清单 3. 标量 SQL 函数

　CREATE FUNCTION julian_date(in_date DATE)
　　RETURNS CHAR(7)
　　LANGUAGE SQL
　　RETURN RTRIM(CHAR(YEAR(in_date))) ||
　　　SUBSTR(DIGITS(DAYOFYEAR(in_date)), 8)　　　　

外部标量函数

外部标量函数是用 C、C++ 或 Java™ 等高级编程语言编写的返回单一值的函数。创建和实现有源函数或 SQL 函数的过程非常简单，但创建和使用外部标量函数（或任何外部函数）的过程就复杂多了。要想创建任何外部函数，必须执行以下步骤：

使用支持的高级编程语言编写 UDF 体。

编译 UDF。

链接 UDF 以创建库（或动态链接库）。

调试 UDF 并重复第 2 到第 4 步，直到解决所有问题。

把包含 UDF 的库存储在服务器工作站上。另外，必须修改包含 UDF 的库文件的系统权限，让所有用户都可以执行它。例如，在 UNIX 环境中，使用 chmod 命令让一个文件可由所有用户执行；在 Windows 环境中，使用 attrib 完成此任务。

使用适当形式的 CREATE FUNCTION SQL 语句向 DB2 数据库注册 UDF。

完成这些步骤之后，就可以按照使用任何标量函数或表函数的方式使用生成的 UDF。

一定要注意一点：因为外部标量函数是用高级编程语言编写的，而不是 SQL，所以提供给函数参数的每个值必须由 SQL 数据类型转换为适当的高级编程语言数据类型，然后才能使用。（如果函数的参数之一需要用户定义的数据类型，那么应该先把参数值转换为适当的内置数据类型，然后再传递给外部函数）。同样，函数返回的任何值必须由高级编程语言数据类型转换为适当的 SQL 数据类型，然后才能返回它。另外，如果在外部函数中动态地分配了内存，那么应该在函数返回之前释放内存。

用来注册（已经按以上说明创建的）外部标量函数的 CREATE FUNCTION 语句形式的基本语法如下：

清单 4. 用来注册外部标量函数的 CREATE FUNCTION 语句

　CREATE FUNCTION [FunctionName] ( <<[ParameterName]> [InputDataType] ,...> )
　　RETURNS [OutputDataType]
　　<SPECIFIC [SpecificName]>
　　EXTERNAL <NAME [ExternalName] | [Identifier]>　　
　　LANGUAGE [C | JAVA | CLR | OLE]
　　PARAMETER STYLE [DB2GENERAL | JAVA | SQL]
　　<DETERMINISTIC | NOT DETERMINISTIC>
　　<FENCED | NOT FENCED>
　　<RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT>
　　<NO SQL | CONTAINS SQL | READS SQL DATA>　　
　　<STATIC DISPATCH>　　
　　<EXTERNAL ACTION | NO EXTERNAL ACTION>
　　<SCRATCHPAD <[100 | [SPSize]]> | NO SCRATCHPAD>　　
　　<DBINFO | NO DBINFO>　　

其中：

FunctionName 指定要创建的外部标量函数的名称。

ParameterName 指定一个或多个函数参数的名称。

InputDataType 指定 ParameterName 所识别的参数所需的数据类型。

OutputDataType 指定函数返回的数据的类型。

SpecificName 指定分配给这个 UDF 的特定名称。这个名称可以用来引用或删除函数；但是，不能用来调用函数。

ExternalName 指定包含要注册的外部函数的可执行代码的库和函数。（稍后详细讨论这个名称的构造方式）。

Identifier 指定包含要注册的外部函数的可执行代码的库，但是这只适用于用 C 或 C++ 编写的函数。DB2 Database Manager 会寻找与库同名的函数。

SPSize 指定用作暂存（scratchpad）区域的内存量（以字节为单位）。

可以看到，这种形式的 CREATE FUNCTION 语句包含几个前面没有出现过的子句。同样，在许多情况下，这些子句传达的信息不太直观，所以我们先详细讨论这些新子句，然后再看一个示例。

EXTERNAL <NAME [ExternalName] | [Identifier]> 子句指定两个信息：包含要注册的 UDF 的可执行代码的库，以及库中的函数。用来编写外部 UDF 体的高级编程语言决定如何提供这两个名称。例如，如果外部 UDF 是用 C 或 C++ 编程语言开发的，那么可以以四种方式指定包含函数体的库和函数：

'LibraryName'

'LibraryName ! FunctionName'

'AbsolutePath'

'AbsolutePath ! FunctionName'

如果提供库名而不是绝对路径，那么 DB2 会在 /sqllib/function 和 /sqllib/function/unfenced 子目录中寻找指定的库名。（在 Windows 操作系统上，DB2 会在 LIBPATH 或 PATH 环境变量指定的目录路径中寻找函数）。另一方面，如果提供了绝对路径，DB2 就在指定的位置寻找库。（如果既没有提供库名，也没有提供绝对路径，DB2 就会在默认子目录中寻找与要注册的 UDF 同名的库和函数）。如果提供了函数名，DB2 就会在指定的库中寻找指定的函数名；如果没有提供函数名，DB2 就会寻找与指定的库同名的函数。

LANGUAGE [C | JAVA | CLR | OLE] 子句用来指定 UDF 体遵守的高级编程语言约定。有四个值可用：

C：DB2 按照处理 C 函数的方式调用 UDF。（这个 UDF 必须遵守由标准 ANSI C 定义的 C 语言调用和链接约定）。

JAVA：DB2 按照处理 Java 类中的方法的方式调用 UDF。

CLR：DB2 按照处理 .NET 类中的方法的方式调用 UDF。（目前，只对在 Windows 操作系统上运行的 UDF 支持 LANGUAGE CLR）。

OLE：DB2 按照处理 OLE 自动化对象公开的方法的方式调用 UDF。（这个 UDF 必须遵守 OLE Automation Programmer's Reference 中描述的 OLE 自动化数据类型和调用机制）。

PARAMETER STYLE [DB2GENERAL | JAVA | SQL] 子句指定执行调用的应用程序在向 UDF 传递值时应该采用的参数传递方式。有三种参数传递方式：

DB2GENERAL：按照调用 Java 方法的调用约定传递和返回值。（只能对用 Java 编写的外部 UDF 使用此方式）。

JAVA：按照符合 Java 语言和 SQL 规范的调用约定传递和返回值。（只能对用 Java 编写的外部 UDF 使用此方式）。

SQL：按照符合 C 语言调用和链接约定、OLE 自动化对象公开的方法或 .NET 对象的公共静态方法的调用约定传递和返回值。（只能对用 C/C++、OLE 或 .NET 编写的外部 UDF 使用此方式）。

<FENCED | NOT FENCED> 子句指定外部 UDF 是否足够 “可靠”，可以在 DB2 Database Manager 操作环境的进程/内存空间中运行（NOT FENCED 表示可以，FENCED 表示不可以）。如果指定 FENCED 子句（或这两个子句都未指定），那么 DB2 Database Manager 就不允许函数访问它的内部资源。

<NO SQL | CONTAINS SQL | READS SQL DATA> 子句指定在外部 UDF 体中编写的 SQL 语句的类型。有三个值可用：

NO SQL：外部 UDF 体不包含任何 SQL，或者包含不可执行的 SQL 语句。（不可执行的 SQL 语句包括 INCLUDE 和 WHENEVER 语句等）。

CONTAINS SQL：UDF 体包含的可执行 SQL 语句既不读数据，也不修改数据。

READS SQL DATA：UDF 体包含的可执行 SQL 语句读数据，但是不修改数据。

<SCRATCHPAD <[100 | [SPSize]]> | NO SCRATCHPAD> 子句指定是否为 UDF 分配作为持久存储区域的内存（SCRATCHPAD 表示分配，NO SCRATCHPAD 表示不分配）。如果指定了 SCRATCHPAD 子句，DB2 就会在首次调用此函数时分配适当数量的内存。在创建和填充暂存区域之后，会在各次函数调用之间保留它的内容 —— UDF 在一次调用中对暂存区域所做的任何修改都会保留到下一次调用。

<DBINFO | NO DBINFO> 子句表示在调用函数时是否把 DB2 掌握的信息作为额外参数传递给 UDF（DBINFO 表示传递，NO DBINFO 表示不传递）。如果指定了 DBINFO 子句，那么 DB2 会传递一个数据结构，其中包含的信息包括当前连接的数据库的名称、应用程序运行时授权 ID、调用此函数的数据库服务器的版本、发布版本和修订级别以及服务器使用的操作系统。

因此，如果希望注册一个名为 CENTER() 的外部标量函数，它接受两个值（一个 INTEGER 值和一个 DOUBLE 值）并返回一个 DOUBLE 值，它存储在 /home/db2inst1/myfuncs 目录中的 “double” 库中，那么可以执行下面的 CREATE FUNCTION 语句：

清单 5. 外部标量函数

　CREATE FUNCTION center(INT, DOUBLE)
　　RETURNS DOUBLE
　　EXTERNAL NAME '/home/db2inst1/myfuncs/double'
　　LANGUAGE C
　　PARAMETER STYLE SQL
　　DETERMINISTIC
　　NO SQL

外部表函数

与外部标量函数一样，外部表函数是用高级编程语言编写的。外部标量函数返回一个标量值，而外部表函数在每次调用时返回一个数据集。外部表函数的强大之处在于，它们能够让几乎任何数据源看起来像是 DB2 基表。另外，在联结操作、分组操作、集操作（例如 UNION）和能够应用于只读视图的任何其他操作中，都可以使用外部表函数返回的数据集。

用来注册外部表函数的 CREATE FUNCTION 语句形式的基本语法如下：

清单 6. 用来注册外部表函数的 CREATE FUNCTION 语句

　CREATE FUNCTION [FunctionName] ( <<[ParameterName]> [InputDataType] ,...> )
　　RETURNS TABLE ( [ColumnName] [ColumnDataType] ,... )
　　<SPECIFIC [SpecificName]>
　　EXTERNAL <NAME [ExternalName] | [Identifier]>　　
　　LANGUAGE [C | JAVA | CLR | OLE]
　　PARAMETER STYLE [DB2GENERAL | SQL]
　　<DETERMINISTIC | NOT DETERMINISTIC>
　　<FENCED | NOT FENCED>
　　<RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT>
　　<NO SQL | CONTAINS SQL | READS SQL DATA>　　
　　<STATIC DISPATCH>　　
　　<EXTERNAL ACTION | NO EXTERNAL ACTION>
　　<SCRATCHPAD <[100 | [SPSize]]> | NO SCRATCHPAD>　　
　　<DBINFO | NO DBINFO>　

其中：

FunctionName 指定要创建的外部表函数的名称。

ParameterName 指定一个或多个函数参数的名称。

InputDataType 指定 ParameterName 所识别的参数所需的数据类型。

ColumnName 指定函数返回的一列或多列的名称。

ColumnDataType 指定 ColumnName 所识??的列返回的数据类型。

SpecificName 指定分配给这个 UDF 的特定名称。这个名称可以用来引用或删除函数；但是，不能用来调用函数。

ExternalName 指定包含要注册的外部函数的可执行代码的库和函数。

Identifier 指定包含要注册的外部函数的可执行代码的库，但是这只适用于用 C 或 C++ 编写的函数。DB2 Database Manager 会寻找与库同名的函数。

SPSize 指定用作 scratchpad 区域的内存量（以字节为单位）。

因此，如果希望注册一个名为 EMPDATA() 的外部表函数，它接受两个长度可变的字符串值，返回一个包含职员信息的数据集（信息取自一个 ASCII 文件），它存储在 /home/db2inst1/myfuncs 目录中的 “EMPDATA” 库中，那么可以执行下面的 CREATE FUNCTION 语句：

清单 7. 外部表函数

　CREATE FUNCTION empdata (VARCHAR(30), VARCHAR(255))
　　RETURNS TABLE (empid INT, lname CHAR(20), fname CHAR(20))
　　EXTERNAL NAME '/home/db2inst1/myfuncs/EMPDATA'
　　LANGUAGE C
　　PARAMETER STYLE SQL
　　DETERMINISTIC
　　NOT FENCED　　
　　NO SQL
　　NO EXTERNAL ACTION

OLE DB 外部表函数

Microsoft OLE DB 是一组应用程序编程接口 (API)，用于访问各种数据源。数据源由数据本身、相关联的数据库管理系统 (DBMS)、运行 DBMS 的平台以及用来访问此平台的网络组成。OLE DB 可以为 OLE Component Object Model (COM) 环境中的所有数据源类型提供访问。

除了 CLI/ODBC 提供的功能之外，OLE DB 定义的接口还可以访问无法通过 SQL 访问的数据。OLE DB 通过定义一组标准接口来促进应用程序集成，这些接口包含与语义相关的函数，应用程序可以通过这些函数访问其他应用程序的服务。接口是用于组件-对象交互的二进制标准，每个接口包含一组函数，它们为实现接口的对象（提供者）和使用接口的客户机（消费者）之间的交互定义一个 “合同”。有两类 OLE DB 提供者：OLE DB 数据提供者，它们本身拥有数据并以行集的形式公开表格式的数据；OLE DB 服务提供者，它们本身不拥有数据，而是通过 OLE DB 接口产生和消费数据来封装某些服务。

与外部表函数一样，外部 OLE DB 表函数是用高级编程语言编写的。但是，对于 OLE DB 表函数，可以使用一个通用的内置 OLE DB 消费者访问任何 OLE DB 提供者来访问数据；只需注册一个 OLE DB 表函数并作为数据源引用适当的 OLE DB 提供者。不需要编写额外的代码。与外部表函数一样，在联结操作、分组操作、集操作（例如 UNION）和能够应用于只读视图的任何其他操作中，都可以使用外部 OLE DB 表函数返回的表。例如，可以定义一个 OLE DB 表函数，它返回来自 Microsoft Access 数据库或 Microsoft Exchange 地址簿的表；然后创建一个报告，报告无缝地组合来自这个 OLE DB 表和 DB2 数据库的数据。

一定要注意一点：要想对 DB2 数据库使用 OLE DB 表函数，必须安装 OLE DB 2.0 或更高版本，这可以从 Microsoft 获得。关于系统需求和特定数据源可用的 OLE DB 提供者的更多信息，请查阅数据源的文档。

用来注册 OLE DB 外部表函数的 CREATE FUNCTION 语句形式的基本语法见清单 8。

清单 8. 用来注册 OLE DB 外部表函数的 CREATE FUNCTION 语句

　CREATE FUNCTION [FunctionName] ( <<[ParameterName]> [InputDataType] ,...> )
　　RETURNS TABLE ( [ColumnName] [ColumnDataType] ,... )
　　<SPECIFIC [SpecificName]>
　　EXTERNAL <NAME [ExternalName]>　　
　　LANGUAGE OLEDB
　　<DETERMINISTIC | NOT DETERMINISTIC>
　　<RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT>　
　　<EXTERNAL ACTION | NO EXTERNAL ACTION>
　　<CARDINALITY [NumRows]>　　

其中：

FunctionName 指定要创建的 OLE DB 外部表函数的名称。

ParameterName 指定一个或多个函数参数的名称。

InputDataType 指定 ParameterName 所识别的参数所需的数据类型。

ColumnName 指定函数返回的一列或多列的名称。

ColumnDataType 指定 ColumnName 所识别的列返回的数据类型。

SpecificName 指定分配给这个 UDF 的特定名称。这个名称可以用来引用或删除函数；但是，不能用来调用函数。

ExternalName 为要注册的函数指定引用的外部表和 OLE DB 提供者。指定外部表和 OLE DB 提供者的语法如下：

　'[Server]!<Rowset>'　

或

　'!<Rowset>![ConnectString] <!COLLATING_SEQUENCE = [N | Y]>'　

其中：

Server 指定由 CREATE SERVER SQL 语句定义的数据源的本地名称。

Rowset 指定 OLE DB 提供者公开的行集（表）。

ConnectString 指定一个连接字符串，其中包含通过 OLE DB 提供者连接数据源所需的初始化属性。此字符串由一系列关键字=值 对组成，与 CLI/ODBC 函数 SQLDriverConnect() 使用的连接字符串相似。

注意：<!COLLATING_SEQUENCE = [N | Y]> 子句指定是否使用与 DB2 相同的比较次序访问数据源。

NumRows 指定函数返回的行数的估计值。（此值只用于优化）。

因此，如果希望注册一个名为 ORDERS() 的 OLE DB 外部表函数，从一个 Microsoft Access 数据库获取订单信息，那么可以执行清单 9 所示的 CREATE FUNCTION 语句：

清单 9. OLE DB 外部表函数

　CREATE FUNCTION orders()
　　RETURNS TABLE (orderid　　　INTEGER,
　　　　　　　　　 customerid　 CHAR(5),
　　　　　　　　　 employeeid　 INTEGER,
　　　　　　　　　 orderdate　　TIMESTAMP,
　　　　　　　　　 requiredate　TIMESTAMP,
　　　　　　　　　 shipdate　　 TIMESTAMP,
　　　　　　　　　 shipcharges　DECIMAL(19,4))
　　LANGUAGE OLEDB
　　EXTERNAL NAME '!orders!Provider=Microsoft.Jet.OLEDB.3.51;
　　　　Data Source=c:sqllibsamplesoledbnwind.mdb'

SQL 函数的结构

在前面，您已经看到 SQL 函数由以下部分组成：

函数名

一系列参数声明（如果有参数的话）

函数返回值的声明

一个或多个函数选项

函数体

图 1 给出用来定义每个部分的 CREATE FUNCTION 语句元素。

图 1. SQL 函数的结构

每个 SQL 函数的定义以关键字 “CREATE FUNCTION” 开头，然后是函数名（在 图 1 中由 CREATE FUNCTION 语句中的第一项表示）。完全限定的函数名加上所有参数的名称和基本数据类型，就构成了函数签名。在同一个数据库模式中，不能有两个函数签名完全相同的函数。

接下来是参数声明（在 图 1 中由第二项表示）。可以在这里定义在调用函数时用来传递数据的一个或多个变量。（函数也可以没有参数）。

在参数声明后面，必须指定函数将返回的信息的种类（在 图 1 中由第三项表示）。SQL 函数可以返回任何内置数据类型，但是 LONG VARCHAR、LONG VARGRAPHIC 和 XML 除外。它们还可以返回表或单一行。

接下来，指定函数的属性（在 图 1 中由第四项表示）。最重要的属性是用来编写函数体的语言，在这里就是 SQL。在这里可以指定的其他属性包括：在用相同的参数调用函数时函数是否总是返回相同的值（DETERMINISTIC），是否执行任何外部操作（EXTERNAL ACTION），数据访问级别（CONTAINS SQL、READS SQL DATA 或 MODIFIES SQL DATA）。

最后是函数体（在 图 1 中由第五项表示）。函数体可以是单一 SQL 语句，也可以是复合 SQL 语句。可以在函数体中使用的语句由函数的属性决定。表 1 列出在不同的数据访问级别上可以在 SQL 函数体中使用的 SQL 语句。（以粗体显示的 SQL 语句是 SQL PL 语句）。

表 1. 可以在 SQL 函数体中使用的 SQL 语句

SQL 语句	CONTAINS SQL	READS SQL DATA	MODIFIES SQL DATA
CALL	Yes	Yes	Yes
DELETE	No	No	Yes
FOR	Yes	Yes	Yes
GET DIAGNOSTICS	Yes	Yes	Yes
IF	Yes	Yes	Yes
INSERT	No	No	Yes
ITERATE	Yes	Yes	Yes
LEAVE	Yes	Yes	Yes
MERGE	No	No	Yes
SELECT	No	Yes	Yes
SET variable	Yes	Yes	Yes
SIGNAL	Yes	Yes	Yes
VALUES	No	Yes	Yes
WHILE	Yes	Yes	Yes

下面通过一些示例解释这个结构。

单语句 SQL 标量函数

顾名思义，单语句 SQL 函数的函数体只包含一个 SQL 语句。单语句 SQL 标量函数是返回一个标量值的单语句 SQL 函数。

图 2 给出的单语句 SQL 标量函数用来显示当前的月份名称。

图 2. 单语句 SQL 标量函数

组成 图 2 中的 CREATE FUNCTION 语句的元素如下：

函数名，即 SQLFUNC.THIS_MONTH()。

函数的返回值，这是一个长度可变的字符串（VARCHAR），最大长度为 25 个字符。

分配给函数的惟一名称，在这里是 SQLFUNC.THIS_MONTH。如果在同一个数据库模式中有多个名称相同但签名不同的函数，这个惟一名称就非常有用。

指出用来编写函数体的语言是 SQL。

指出在用相同的参数调用时函数不一定总是返回相同的值。

指出函数不执行任何外部操作。

指出函数包含的 SQL 语句既不读数据也不修改数据。

函数体以及 RETURN 语句，此语句把控制返回给调用函数的用户或应用程序。在此示例中，函数体使用当前时间调用内置函数 MONTHNAME()，从而获得当前月份的名称。

图 3 给出一个单语句 SQL 标量函数，用于删除字符串前后的空格。此函数与 图 2 所示函数的不同之处是它接受一个输入参数值。

图 3. 单语句 SQL 标量函数，有一个输入参数

组成 图 3 中的 CREATE FUNCTION 语句的元素如下：

函数名，即 SQLFUNC.STRIP_LTBLANKS()。

函数所需的输入参数，在这里是一个长度可变的字符串（VARCHAR），最大长度为 1024 个字符。

函数的返回值，这是一个长度可变的字符串（VARCHAR），最大长度为 1024 个字符。

分配给函数的惟一名称，在这里是 SQLFUNC.STRIP_LTBLANKS。

函数的属性，比如用来构造函数体的语言、在用相同的参数值调用时函数是否总是返回相同的值、函数是否执行任何外部操作以及所需的数据访问级别。

函数体以及 RETURN 语句。在此示例中，函数体调用内置函数 LTRIM() 和 RTRIM()，从而删除字符串前后的空格。

单语句 SQL 表函数

单语句 SQL 表函数的函数体由一个 SQL 语句组成，它向引用它的 SQL 语句返回一个表。单语句 SQL 表函数与单语句 SQL 标量函数的差异是 RETURNS 子句的编写方式不同；并不声明要返回一个标量数据类型的值，而是定义作为函数结果的临时表的列。图 4 给出一个单语句 SQL 表函数，它返回的结果集包含男性职员的工资信息。

图 4. 单语句 SQL 表函数

组成 图 4 中的 CREATE FUNCTION 语句的元素如下：

函数名，即 SQLFUNC.MALE_SALARIES()。

函数的返回值，这是一个包含职员编号、姓氏和工资信息的临时表。

分配给函数的惟一名称，在这里是 SQLFUNC.MALE_SALARIES。

函数的属性，比如用来构造函数体的语言、在用相同的参数值调用时函数是否总是返回相同的值、函数是否执行任何外部操作以及所需的数据访问级别。

函数体以及 RETURN 语句，此语句把控制返回给调用函数的用户或应用程序。在此示例中，函数体是一个查询，它从 EMPLOYEE 表中获取所有男性职员的职员编号、姓氏和工资信息。

图 5 给出一个单语句 SQL 表函数，它返回的结果集包含工资高于指定工资的所有职员的工资信息。此函数与 图 4 所示函数的不同之处在于它接受一个输入参数值。

图 5. 单语句 SQL 表函数，有一个输入参数

组成 图 5 中的 CREATE FUNCTION 语句的元素如下：

函数名，即 SQLFUNC.TOP_SALARIES()。

函数所需的输入参数，在这里是一个小数值（DECIMAL），其刻度为 9，精度为 2。

函数的返回值，这是一个包含职员编号、姓氏和工资信息的临时表。

分配给函数的惟一名称，在这里是 SQLFUNC.TOP_SALARIES。

函数的属性，比如用来构造函数体的语言、在用相同的参数值调用时函数是否总是返回相同的值、函数是否执行任何外部操作以及所需的数据访问级别。

函数体以及 RETURN 语句。在此示例中，函数体是一个查询，它从 EMPLOYEE 表中获取工资高于指定的最低工资的所有职员的职员编号、姓氏和工资信息。

复合语句 SQL 标量函数

复合 SQL 语句是一种可以把多个 SQL 语句组合成一个可执行块的特殊机制。尽管复合语句中使用的每个 SQL 语句都可以单独执行，但是把它们组合在一起会减少处理它们所需的开销；DB2 把复合语句编译为单一 SQL 语句。复合 SQL 语句本身适合用来创建简短的脚本，执行控制流非常简单但数据流很大的小型逻辑工作单元。在 SQL 函数体中使用（与支持的 SQL PL 语句结合使用）时，可以执行更复杂的逻辑。图 6 给出一个 SQL 标量函数，其函数体包含一个复合 SQL 语句。此函数用于查明本月的最后一天是星期几。

图 6. 复合语句 SQL 标量函数

组成 图 6 中的 CREATE FUNCTION 语句的元素如下：

函数名，即 SQLFUNC.LAST_DAY_OF_MONTH()。

函数的返回值，这是一个长度可变的字符串（VARCHAR），最大长度为 24 个字符。

分配给函数的惟一名称，在这里是 SQLFUNC.LAST_DAY_OF_MONTH。

函数的属性，比如用来构造函数体的语言、在用相同的参数值调用时函数是否总是返回相同的值、函数是否执行任何外部操作以及所需的数据访问级别。

关键字 BEGIN ATOMIC，它表示一个复合 SQL 语句块的开头。

函数体，它由构成复合 SQL 语句块的几个 SQL 语句组成。

RETURN 语句，此语句把控制返回给调用函数的用户或应用程序。在此示例中，RETURN 语句使用在函数体中构造的一个日期字符串调用内置函数 DAYNAME() 并返回结果。

关键字 END，它表示一个复合 SQL 语句块结束。

图 7 给出一个复合语句 SQL 标量函数，它用于把华氏温度转换为摄氏温度。此函数与 图 6 所示函数的不同之处在于它接受一个输入参数值。

图 7. 复合语句 SQL 标量函数，有一个输入参数

组成 图 7 中的 CREATE FUNCTION 语句的元素如下：

函数名，即 SQLFUNC.CONV_F_TO_C()。

函数所需的输入参数，在这里是一个单精度浮点值，它代表一个华氏温度值。

函数的返回值，在这里是一个单精度浮点值，它代表一个摄氏温度值。

分配给函数的惟一名称，在这里是 SQLFUNC.CONV_F_TO_C。

函数的属性，比如用来构造函数体的语言、在用相同的参数值调用时函数是否总是返回相同的值、函数是否执行任何外部操作以及所需的数据访问级别。

关键字 BEGIN ATOMIC，它表示一个复合 SQL 语句块的开头。

函数体，它由构成复合 SQL 语句块的几个 SQL 语句组成。

RETURN 语句。在此示例中，RETURN 语句返回在函数体中生成的摄氏温度值。

关键字 END，它表示一个复合 SQL 语句块结束。

复合语句 SQL 表函数

复合语句 SQL 表函数的函数体由一个复合 SQL 语句组成，它向引用它的 SQL 语句返回一个表。与单语句 SQL 表函数一样，复合语句 SQL 表函数与 SQL 标量函数的差异是 RETURNS 子句的编写方式不同；并不声明要返回一个标量数据类型的值，而是定义作为函数结果的临时表的列。图 8 给出一个复合语句 SQL 表函数，它返回的结果集包含工资高于所有职员平均工资的所有职员的工资信息。

图 8. 复合语句 SQL 表函数

组成 图 8 中的 CREATE FUNCTION 语句的元素如下：

函数名，即 SQLFUNC.TOP_SALARIES()。

函数的返回值，这是一个包含职员编号、姓氏和工资信息的临时表。

分配给函数的惟一名称，在这里是 SQLFUNC.TOP_SALARIES。

函数的属性，比如用来构造函数体的语言、在用相同的参数值调用时函数是否总是返回相同的值、函数是否执行任何外部操作以及所需的数据访问级别。

关键字 BEGIN ATOMIC，它表示一个复合 SQL 语句块的开头。

函数体，它由构成复合 SQL 语句块的几个 SQL 语句组成。

RETURN 语句，此语句把控制返回给调用函数的用户或应用程序。在此示例中，RETURN 语句返回一个查询的结果，此查询从 EMPLOYEE 表中获取工资高于表中平均工资的所有职员的职员编号、姓氏和工资信息。

关键字 END，它表示一个复合 SQL 语句块结束。

图 9 给出一个复合语句 SQL 表函数，它返回的结果集包含在指定部门中工作的本月过生日的所有职员的信息。此函数与 图 8 所示函数的不同之处在于它接受一个输入参数值。

图 9. 复合语句 SQL 表函数，有一个输入参数

组成 图 9 中的 CREATE FUNCTION 语句的元素如下：

函数名，即 SQLFUNC.BDAY_THIS_MONTH()。

函数所需的输入参数，在这里是一个长度可变的字符串（VARCHAR），最大长度为 3 个字符。

函数的返回值，这是一个包含职员编号、姓氏、部门和生日信息的临时表。

分配给函数的惟一名称，在这里是 SQLFUNC.BDAY_THIS_MONTH。

函数的属性，比如用来构造函数体的语言、在用相同的参数值调用时函数是否总是返回相同的值、函数是否执行任何外部操作以及所需的数据访问级别。

关键字 BEGIN ATOMIC，它表示一个复合 SQL 语句块的开头。

函数体，它由构成复合 SQL 语句块的几个 SQL 语句组成。

RETURN 语句。在此示例中，RETURN 语句返回一个查询的结果，此查询从 EMPLOYEE 表中获取在指定部门中工作的本月过生日的所有职员的职员编号、姓氏、部门和生日信息。

关键字 END，它表示一个复合 SQL 语句块结束。

SQL Procedural Language (SQL PL) 语句

SQL Procedural Language (SQL PL) 是在 DB2 Version 7 中引入的一组 SQL 语句，它们提供围绕传统 SQL 查询和操作实现控制流逻辑所需的过程式构造。在此之后，SQL PL 又有所改进，当前的 SQL PL 语句集和语言特性支持用 SQL 进行全面的高级编程。

SQL PL 的语法很简单，支持变量、条件语句、循环语句、控制转移语句、错误管理语句和结果集操作语句。另外，在复合语句 SQL 函数中可以使用 SQL PL 语句的一个子集。可以在复合语句 SQL 函数体中使用的 SQL PL 语句包括：

与变量相关的语句

DECLARE [Variable] DEFAULT [Value]

DECLARE [Condition]

SET (assignment-statement)

条件语句

IF

循环语句

FOR

WHILE

控制转移语句

CALL

ITERATE

LEAVE

RETURN

错误管理语句

SIGNAL

图 10 给出的复合语句 SQL 标量函数包含几个 SQL PL 语句。

图 10. 包含 SQL PL 语句的 SQL 标量函数

在 图 10 所示 SQL 函数中使用的 SQL PL 语句如下：

DECLARE [Variable] 语句，用于创建将在函数体中使用的一个或多个局部变量。

SET 语句，用于给变量赋值。

一个用户定义的标签（名为 loop_label），在这里此标签与一个 WHILE 循环相关联，在 WHILE 循环中用来实现分支。

WHILE 语句，用于实现循环。

IF 语句，它与 ELSEIF 和 ELSE 语句结合使用以执行条件处理。

ITERATE 语句，用于转到指定的标签。

LEAVE 语句，用于跳出循环。

RETURN 语句，用于向调用函数的用户或应用程序返回一个标量值或结果值。

SQL 函数中的错误处理

在理想情况下，SQL 函数中的错误处理，它捕捉和处理在函数范围内可能发生的任何错误和/或警告，然后把错误的相关信息返回给调用函数的用户或应用程序。最容易的错误处理方法是使用 SIGNAL SQL 语句。此语句的基本语法如下：

　SIGNAL [Condition_Value]
　　SET MESSAGE_TEXT = [Message]　　　

或

　SIGNAL SQLSTATE <VALUE> [SQLSTATE_Value]
　　SET MESSAGE_TEXT = [Message]　　　

其中：

Condition_Value 表示在包含 SIGNAL 语句的复合语句中声明的一个条件。

SQLSTATE_Value 指定一个包含五个字符的字符串常量，它代表 SQLSTATE 值。

Message 指定一个最多 70 个字符的字符串，它描述遇到的错误或警告。

例如，图 11 给出一个复合语句 SQL 标量函数，它把一个 DECIMAL 值转换为 DATE 值，其中包含捕捉和处理错误的代码。在此函数中，为 SQLSTATE 22007 定义一个条件 —— 这个 SQLSTATE 值表示日期无效（由图 11 中的第一项表示），如果输入值是 0，就使用 SIGNAL 语句把警告或错误告知调用函数的用户或应用程序（由图 11 中的第二项表示）。

图 11. 包含流控制语句的 SQL 标量函数

调用 SQL 函数

调用 SQL 函数的方式在一定程度上取决于函数返回值的类型。如果函数是标量函数，就可以在 SELECT、VALUES 或 SET 语句中调用它。例如，可以通过执行下面的 VALUES 语句调用 图 3 所示的单语句 SQL 标量函数：

VALUES sqlfunc.strip_ltblanks(' This is a test. '),

在执行这个 VALUES 语句时，提供的字符串前后的空格被删除，返回 “This is a test” 值。

另一方面，如果函数是表函数，那么必须在 SELECT 语句的 FROM 子句中引用它。例如，可以通过执行下面的 SELECT 语句调用 图 9 所示的复合语句 SQL 表函数：

SELECT * FROM TABLE(sqlfunc.bday_this_month('A00'))

在执行这个 SELECT 语句时，返回一个结果集，其中包含在 'A00' 部门中工作的本月过生日的所有职员的职员编号、姓氏、部门和生日信息。

结束语

在本教程中，您学习了如何使用 UDF 扩展 DB2 提供的内置功能。还了解了四种可用的函数类型（标量、聚合、表和行）以及可以创建的五种 UDF 类型（有源、SQL、外部标量、外部表和 OLE DB 表）。还了解了 SQL 函数是只使用 SQL 构造的，根据定义方式不同，它们可以返回标量值、单一行或整个表。每个 SQL 函数由五个基本部分组成：

函数名

一系列参数声明（如果有参数的话）

函数返回值的声明

一个或多个函数选项

函数体

函数体可以是单一 SQL 语句，也可以是复合 SQL 语句。复合 SQL 语句是一种可以把多个 SQL 语句组合成一个可执行块的特殊机制；使用关键字 BEGIN ATOMIC 和 END 表示复合 SQL 语句块的开头和结尾。

在本教程中，还了解了 SQL 函数中的错误处理，它捕捉和处理在函数范围内可能发生的任何错误和/或警告，然后把错误的相关信息返回给调用函数的用户或应用程序。通常使用 SIGNAL SQL 语句实现错误处理。

如果函数是标量函数，就可以在 SELECT、VALUES 或 SET 语句中调用它；如果函数是表函数，那么必须在 SELECT 语句的 FROM 子句中引用它。您现在应该能够在任何 DB2 数据库环境中构造和部署 SQL 函数了。