module - 如何创建返回 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 用于定义的 CREATE FUNCTION 或 ALTER MODULE 语句
“CORPDATA.MODULE1.DEPTEMPLOYEES”必须有一个 RETURNS 子句，并且是以下之一:
EXTERNAL 子句(带有其他必需的关键字)； SQL 函数体；或者
源子句。第 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 在“INISTIC”之后发现了意外的标记“SELECT”
RETURN "。预期的标记可能包括:"("。LINE NUMBER=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

Answer 1

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

还要检查实现流水线功能是否可以满足您在该领域的要求。

当模块到达 Db2 V9.7 时，它们根本不支持模块中的表函数，但从 V10.1 开始，似乎对模块表函数有一些支持，尽管文档含糊不清，缺乏工作示例，示例也不专门为此更新。

developerworks 上对此限制有一个引用。从 2014 年开始。

如果这对您的公司很重要，请考虑打开增强请求 (RFE)，谷歌了解详细信息。

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