如何在DB2模块中创建一个返回表格的用户定义函数？

Question

我是一个有用的助手，可以为您进行文本翻译。以下是您需要翻译的内容：

我想创建一个在DB2中返回表格的用户定义函数。这是我目前所拥有的：

这是我使用的表格：

CREATE TABLE "CORPDATA"."EMPLOYEE" (
    "EMPNO" CHAR(6) NOT NULL, 
    "FIRSTNME" VARCHAR(12) NOT NULL, 
    "MIDINIT" CHAR(1) NOT NULL, 
    "LASTNAME" VARCHAR(15) NOT NULL, 
    "WORKDEPT" CHAR(3), 
    "PHONENO" CHAR(4), 
    "HIREDATE" DATE, 
    "JOB" CHAR(8), 
    "EDLEVEL" SMALLINT NOT NULL, 
    "SEX" CHAR(1), 
    "BIRTHDATE" DATE, 
    "SALARY" DECIMAL(9 , 2), 
    "BONUS" DECIMAL(9 , 2), 
    "COMM" DECIMAL(9 , 2)
);

ALTER TABLE "CORPDATA"."EMPLOYEE" ADD CONSTRAINT "PK_EMPLOYEE" PRIMARY KEY
("EMPNO");

这是一个用户定义的函数，它返回一个表格（目前正常工作）：

CREATE OR REPLACE FUNCTION "CORPDATA"."DEPTEMPLOYEES" (DEPTNO CHAR(3))
 RETURNS TABLE (EMPNO CHAR(6),
                LASTNAME VARCHAR(15),
                FIRSTNAME VARCHAR(12))
 LANGUAGE SQL
 READS SQL DATA
 NO EXTERNAL ACTION
 DETERMINISTIC
 BEGIN ATOMIC
   RETURN
     SELECT EMPNO, LASTNAME, FIRSTNME
     FROM CORPDATA.EMPLOYEE
     WHERE WORKDEPT = "DEPTEMPLOYEES".DEPTNO;
 END

这是我使用该模块时能够达到的最远进展:

CREATE MODULE CORPDATA.MODULE1

ALTER MODULE CORPDATA.MODULE1
PUBLISH FUNCTION DEPTEMPLOYEES2 (DEPTNO CHAR(3))
RETURNS TABLE (EMPNO CHAR(6),
                LASTNAME VARCHAR(15),
                FIRSTNAME VARCHAR(12))

任何尝试将函数添加到模块中的操作都会失败，并出现各种错误。以下是我的DB2版本信息： 数据库服务器 = DB2/LINUXX8664 11.1.2.2 这是在Redhat下进行的Express-C安装。

尝试这样做时，我得到了以下错误： SQL0628N 存在涉及“RETURNS”子句的多个或冲突的关键字。行号=16。 SQLSTATE=42613

ALTER MODULE corpdata.module1
ADD FUNCTION DEPTEMPLOYEES (DEPTNO CHAR(3))
     RETURNS TABLE (EMPNO CHAR(6),
                    LASTNAME VARCHAR(15),
                    FIRSTNAME VARCHAR(12))
     LANGUAGE SQL
     READS SQL DATA
     NO EXTERNAL ACTION
     DETERMINISTIC
BEGIN ATOMIC
     RETURN
       SELECT EMPNO, LASTNAME, FIRSTNME
       FROM CORPDATA.EMPLOYEE
       WHERE WORKDEPT = "DEPTEMPLOYEES".DEPTNO;
END

当我尝试这样做（删除RETURNS子句），我会得到SQL0491N错误，即定义"CORPDATA.MODULE1.DEPTEMPLOYEES"的CREATE FUNCTION或ALTER MODULE语句必须有一个RETURNS子句，并且其中之一是：EXTERNAL子句（带有其他必需关键字）；一个SQL函数体；或SOURCE子句。行号=8。SQLSTATE=42601。

ALTER MODULE corpdata.module1
ADD FUNCTION DEPTEMPLOYEES (DEPTNO CHAR(3))
     LANGUAGE SQL
     READS SQL DATA
     NO EXTERNAL ACTION
     DETERMINISTIC
BEGIN ATOMIC
     RETURN
       SELECT EMPNO, LASTNAME, FIRSTNME
       FROM CORPDATA.EMPLOYEE
       WHERE WORKDEPT = "DEPTEMPLOYEES".DEPTNO;
END

当我尝试这样做（删除BEGIN ATOMIC），我遇到了SQL0104N错误，提示意外的标记“SELECT”在“INISTIC RETURN”之后。 期望的标记可能包括： “（”。行号为9。SQLSTATE=42601。:) 是的，它确实是写成了“INISTIC”。

ALTER MODULE corpdata.module1
ADD FUNCTION DEPTEMPLOYEES (DEPTNO CHAR(3))
 LANGUAGE SQL
 READS SQL DATA
 NO EXTERNAL ACTION
 DETERMINISTIC
 RETURN
   SELECT EMPNO, LASTNAME, FIRSTNME
   FROM CORPDATA.EMPLOYEE
   WHERE WORKDEPT = "DEPTEMPLOYEES".DEPTNO

- Andrey Belykh

---

你认为将你遇到的“各种错误”包括进来会有用，还是我们应该开始猜测？ - mustaccio

可能这个可以给你提示："只有当SQL例程体是指定为NOT ATOMIC的复合SQL（已编译）语句时，模块函数定义才能指定RETURNS TABLE子句。" - mustaccio

我会更新一些我尝试过的事情。 - Andrey Belykh

你需要再次阅读手册中的语法图。前两次尝试失败是因为不支持内联复合SQL（begin atomic）；最后一次失败是因为缺少returns子句。 - mustaccio

但是如果我添加了RETURNS子句，它会给我返回SQL0628N，涉及“RETURNS”子句的多个或冲突的关键字存在。行号=10。 SQLSTATE = 42613，就像＃1。所以，基本上，你是在说，这是不可能的吗？ - Andrey Belykh

Answer 1

似乎DB2 LUW版本11.1尚未完全支持模块内的表函数，除非该表函数包括PIPE语句。这是尽管已发布的文档暗示可能存在某些限制。这就是您收到“关键字冲突”错误的原因，因为流水线处理函数一次只能返回一行，这与RETURNS TABLE相反。

还要检查是否实现流水线处理函数可以满足您在此领域的要求。

当模块到达Db2 V9.7时，它们根本不支持模块中的表函数，但自V10.1以来，似乎出现了对模块表函数的某些支持，尽管文档含糊不清，缺乏工作示例，并且样本没有专门针对此进行更新。 developerworks上有关于此限制的参考，日期为2014年。

如果这对您的公司很重要，请考虑打开增强请求（RFE），搜索详细信息。

您可能还想在Db2知识中心页面alter-module上提交文档注释，以及restrictions-on-modules页面上，这些页面没有提到关于模块内表函数使用PIPE语句的额外限制。

- mao