开发 DB2 SQL 用户定义函数

Danna Nicholson
咨询软件工程师, IBM
2005 年 2 月

IBM® DB2® Universal Database™（UDB）用户定义函数允许您构建满足您业务需求的函数。学习如何使用 WebSphere® Studio Application Developer 开发 DB2 SQL 用户定义函数。

简介
DB2 Universal Database（DB2 UDB）用户定义函数（user-defined functions，UDF）允许您添加自己的函数定义，从而扩展 DB2 的内置函数。有了这些用户定义函数，就可以通过编写自己的用于事务、计算等方面的函数来定制 DB2，以满足业务需求。

UDF 鼓励代码重用，这样可以提高应用程序开发人员的生产率。如果您有很多实现相同逻辑的程序，那么可以通过 UDF 标准化该逻辑，并让所有这些程序使用相同的实现。一旦在 DB2 中定义好一个 UDF，便可以像使用内置 DB2 函数那样，在 SQL 语句中使用这个 UDF。如果是在应用程序中编写这个函数，而不是将其定义为 UDF，那么 SQL 查询工具（例如 CLP）的用户将不能访问这个函数。UDF 允许任何前端，包括 Java™ 程序，访问这个标准逻辑。

直接从数据库引擎（而不是从应用程序代码）中调用 UDF 还可以使性能获得极大的提高，尤其是在将数据发送回应用程序进行进一步处理之前，可以使用 UDF 来过滤数据。

本文无意成为用户定义函数的完全指南，只是一篇关于如何在 WebSphere Studio Application Developer（Application Developer）中创建 UDF 的入门读物。有关 DB2 中 UDF 和 SQL 过程语言的细节，请参阅 DB2 文档。DB2 还附带了一些很好的示例，放在 DB2 安装目录下的 samples\sqlproc 目录中。而且，还应注意的是，UDF 也可以用 Java 编写，其他一些文章描述了如何编写 Java UDF。

为什么在 WebSphere Studio Application Developer 中创建 UDF？
您可以使用 DB2 Development Center 来创建 UDF，甚至还可以使用任何文本编辑器手动地创建 UDF。然而，在 Application Developer 中创建 UDF 有几点好处。通过将这些函数成组放入到项目中，与其他 Java 函数和代码放在一起，开发小组可以将所有代码组织到一个地方。将 SQL 函数的代码保存在一个版本控制库（例如 CVS 或 Clear Case）中，这对于保留修订、保存更改历史记录以及与其他小组成员共享代码都有重要意义。

软件版本
本文中的例子使用以下版本的 DB2 UDB 和 Application Developer：

• WebSphere Studio Application Developer V5.1.2
• DB2 UDB V8.1

创建一个新的 UDF
在 DB2 中有两种类型的 UDF：标量函数（scalar function）和表函数（table function）。每次调用标量函数 时，都只返回一个值。而表函数 则返回一个包含多个行和多个列的表。表函数允许您对非实际表（不是实际上的表）中的数据有效地使用关系操作和 SQL。这类似于使用视图，但由于 UDF 可以采用输入参数，因而更像是在“动态地”创建视图。

现在我们将观察一个关于如何创建新的标量 DB2 函数的例子。

首先，在 Application Developer 中创建一个新的 Java 项目。

图 1. 新建项目

图 2. 新建项目例子

为新项目命名，并单击 Finish 按钮。

现在打开 Data perspective。

图 3. Data Perspective

在左下角的 DB Servers 面板中单击鼠标右键，然后选择 New Connection。

图 4. New Connection 窗口

这时将打开新的数据库连接窗口，如上所示。

您可以任意设置 Connection Name，但是通常将其设为数据库名比较合适。使用正确的数据库名、用户 ID 和密码、主机名以及端口号。确保 db2java.zip 文件的 Class location 正确。单击 Finish。

如果输入的信息正确，那么将加载数据库模式。

图 5. 完成的连接

在连接名上单击右键并选择 Import to Folder。通过浏览找到刚才创建的项目的名称，然后单击 Finish。

图 6. Import to Folder

这时，如果展开项目（在上端面板中），就可以看到所有当前的数据库信息。

图 7. 展开的数据库信息

在 User defined functions 上单击右键并选择 New，然后选择 SQL User Defined Function。如下所示填充新的用户定义函数信息，为新函数提供一个函数名：

图 8. New UDF 窗口

单击 Next。单击 Change 按钮，输入一条 SQL 语句，这条语句非常接近于完成操作时应该返回的语句。可以将 Return Type 设为 Scalar 或 Table。如果要返回多个行或值，则应该使用返回类型“Table”。在本文中，我们选择 Scalar，这意味着该函数将只返回一个值。单击 Next。

图 9. New UDF 窗口 2

由于选择了 Scalar 返回类型，因此这里要提供返回值的数据类型。选择 TIMESTAMP 并单击 Next。

图 10. New UDF 窗口 3

接下来的窗口将要求提供函数所需的参数。使用 Add、Change 和 Delete 按钮设置函数所需的所有输入参数，并单击 Next。

图 11. New UDF 窗口 4

接下来的窗口将要求提供 Specific Name。这一项通常应该与函数名一致，但是如果您需要拥有具有相同名单但带有不同参数的“过载的（overloaded）”函数，那么您可以使用这个名称。

图 12. New UDF 窗口 5

最后的窗口将总结到现在为止您所做出的选择。如果一切没问题，请单击 Finish 。

图 13. New UDF 窗口总结

现在这个新函数将出现在主窗口中。必要时可以修改函数的内容，然后保存这些内容。

图 14. 修改函数代码

图 15. 修改函数代码

当完成对函数代码的修改时，在函数名上单击右键并选择 Build。

图 16. 编译函数

在右下角检查错误信息。如果编译成功，便可以在函数名上单击右键并选择 Run。这时可以键入函数的输入参数。

图 17. 设置输入参数

在左下角查看结果。

图 18. 结果

恭喜您！现在您有了一个用于 DB2 用户定义函数的 Application Developer 项目。可以将这个新项目登记到自己选择的一个版本控制系统中，并与其他开发人员共享这个项目。

如何从 DB2 CPL 窗口调用新函数：

• 标量函数的调用方法如下：
  select SSE.ADD_TO_DATE(3) from sysibm.sysdummy1
• 表函数的调用方法如下：
  select * from TABLE( schemaName.functionName(parameters) ) AS T

如果要从 Java 程序的预备语句中调用函数，则需要在预备语句中对每个输入参数使用一个 CAST，方法如下：

• 选择 sse.add_to_date( CAST(? AS INTEGER ) ) from sysibm.sysdummy1;
• 选择 1 from sysibm.sysdummy where schemaName.functionName( CAST(? AS VARCHAR(35)) ) = 'Y' ";

如果必须更改函数的输入参数列表，则需要自己删除该函数，因为在执行 Build 时，Application Developer 不能删除和重新创建函数。您可以在 Application Developer 左下角的 DB Servers 面板中右键单击函数名称，然后选择 Drop。（如果需要具有相同名称但是带不同参数的多个函数，那么要记得使用不同的 Specific Name）。