UDTF使用动态SQL在DB2V5R4上返回一个表

Question

我必须编写一个返回表的UDF。我已经用静态SQL完成了。

我创建了Procedures，准备一个动态和复杂的SQL语句并返回一个游标。

但现在我必须使用DynamicSQL创建一个UDF，并返回一个表，以便与其他select中的IN子句一起使用。

这在DB2v5R4上是可能的？你有一个例子吗？

提前感谢。。。

Answer 1

我没有V5R4，但我有I 6.1和V5R3。我有一个6.1的例子，我在V5R3中摸索了一下，想知道如何让同样的例子在那里工作。我不能保证V5R4，但这应该非常接近。将正在工作的V5R3代码生成到"运行SQL脚本"中可以得到以下结果：

DROP SPECIFIC FUNCTION SQLEXAMPLE.DYNTABLE ; 
SET PATH "QSYS","QSYS2","SYSPROC","SYSIBMADM","SQLEXAMPLE" ; 
CREATE FUNCTION SQLEXAMPLE.DYNTABLE ( 
              SELECTBY VARCHAR( 64 ) ) 
   RETURNS TABLE ( 
              CUSTNBR DECIMAL( 6, 0 ) , 
              CUSTFULLNAME VARCHAR( 12 ) , 
              CUSTBALDUE DECIMAL( 6, 0 ) )   
   LANGUAGE SQL
   NO EXTERNAL ACTION 
   MODIFIES SQL DATA 
   NOT FENCED 
   DISALLOW PARALLEL
   CARDINALITY 100 
   BEGIN 
DECLARE DYNSTMT VARCHAR ( 512 ) ; 
DECLARE GLOBAL TEMPORARY TABLE SESSION.TCUSTCDT 
   ( CUSTNBR DECIMAL ( 6 , 0 ) NOT NULL , 
     CUSTNAME VARCHAR ( 12 ) , 
     CUSTBALDUE DECIMAL ( 6 , 2 ) ) 
WITH REPLACE ; 
SET DYNSTMT = 'INSERT INTO Session.TCustCDt SELECT t2.CUSNUM , (t2.INIT CONCAT '' '' CONCAT t2.LSTNAM) as FullName , t2.BALDUE FROM QIWS.QCUSTCDT t2 ' CONCAT CASE WHEN SELECTBY = '' THEN '' ELSE SELECTBY END ; 
EXECUTE IMMEDIATE DYNSTMT ; 
RETURN SELECT * FROM SESSION . TCUSTCDT ; 
END  ; 
COMMENT ON SPECIFIC FUNCTION SQLEXAMPLE.DYNTABLE 
    IS 'UDTF returning dynamic table' ;

在"运行SQL脚本"中，函数可以这样调用：

SELECT t1.* FROM TABLE(sqlexample.dyntable('WHERE STATE = ''TX''')) t1

该示例旨在处理库QIWS中IBM的示例QCUSCDT表。大多数系统都会提供该表。表函数直接通过表函数的两列CUSTNBR和CUSTBALDUE返回来自两个QCUSCDT列CUSNUM和BALDUE的值。第三个表函数列CUSTFULLNAME通过连接QCUSTCDT中的INIT和LSTNAM来获取其值。

然而，显然与问题有关的部分是函数的SELECTBY参数。用法示例显示，WHERE clause被传入并用于帮助构建动态的"INSERT INTO。。。选择。。。将返回statement. The example shows that rows containing STATE='TX'`。可以传入更复杂的子句，或者可以从其他地方（例如从另一个表）检索所需的条件。

动态语句将行插入到名为SESSION.TCUSTCDT的GLOBAL TEMPORARY TABLE中。临时表是在函数中定义的。临时列定义（由开发人员）保证与表函数的"RETURNS TABLE"列匹配，因为不能对这些元素中的任何元素进行动态更改。这允许SQL可靠地处理从函数返回的列，并允许它编译函数。

RETURN语句只是在动态语句完成后返回临时表中的任何行。

各种字段定义考虑了QCUSTCDT文件中有些不寻常的定义。这些没有多大意义，但它们已经足够有用了。