UDTF使用动态SQL在DB2 V5R4上返回表 [英] UDTF returning a Table on DB2 V5R4 with Dynamic SQL

问题描述

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

I must to write a UDF returning a Table. I’ve done it with Static SQL.

我创建了创建动态复杂SQL语句并返回游标的过程。

I’ve created Procedures preparing a Dynamic and Complex SQL sentence and returning a cursor.

但是现在我必须使用Dynamic SQL创建UDF并返回一个表，该表将与其他select中的IN子句一起使用。

But now I must to create a UDF with Dynamic SQL and return a table to be used with an IN clause inside other select.

这是可能的在DB2 v5R4上？你有例子吗？

It is possible on DB2 v5R4? Do you have an example?

预先感谢...

推荐答案

我没有V5R4，但是我有6.1和V5R3。我有一个6.1示例，我在V5R3中进行了查找，以查找如何使相同的示例在那里工作。我不能保证V5R4，但这应该非常接近。将工作正常的V5R3代码生成为运行SQL脚本即可：

I don't have V5R4, but I have i 6.1 and V5R3. I have a 6.1 example, and I poked around in V5R3 to find how to make the same example work there. I can't guarantee V5R4, but this ought to be extremely close. Generating the working V5R3 code into 'Run SQL Scripts' gives this:

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脚本中，可以这样调用函数：

And in 'Run SQL Scripts', the function can be called like this:

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

该示例旨在处理QIWS库中IBM的示例QCUSCDT表。大多数系统都会提供该表。表格函数直接通过表格函数的两个列CUSTNBR和CUSTBALDUE从QCUSCDT列CUSNUM和BALDUE返回值。第三个表函数列CUSTFULLNAME是由QCUSTCDT的INIT和LSTNAM串联而成的。

The example is intended to work over IBM's sample QCUSCDT table in library QIWS. Most systems will have that table available. The table function returns values from two QCUSCDT columns, CUSNUM and BALDUE, directly through two of the table function's columns, CUSTNBR and CUSTBALDUE. The third table function column, CUSTFULLNAME, gets its value by a concatenation of INIT and LSTNAM from QCUSTCDT.

但是，显然与问题相关的部分是<$函数的c $ c> SELECTBY 参数。该用法示例显示了一个 WHERE子句被传入并用于帮助构建动态的'INSERT INTO ... SELECT ... 语句。该示例显示将返回包含 STATE = TX的行。可以传入更复杂的子句，或者可以从其他位置（例如从另一个表）检索所需的条件。

However, the part that apparently relates to the question is the SELECTBY parameter of the function. The usage example shows that a WHERE clause is passed in and used to help built a dynamic 'INSERT INTO... SELECT...statement. The example shows that rows containingSTATE='TX'` will be returned. A more complex clause could be passed in or the needed condition(s) could be retrieved from somewhere else, e.g., from another table.

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

The dynamic statement inserts rows into a GLOBAL TEMPORARY TABLE named SESSION.TCUSTCDT. The temporary table is defined in the function. The temporary column definitions are guaranteed (by the developer) to match the 'RETURNS TABLE` columns of the table function because no dynamic changes can be made to any of those elements. This allows SQL to handle reliably columns returned from the function, and that lets it compile the function.

RETURN 动态语句完成后，该语句仅返回临时表中的所有行。

The RETURN statement simply returns whatever rows are in the temporary table after the dynamic statement completes.

各种字段定义都考虑了QCUSTCDT文件中有些不寻常的定义。那些没有什么意义，但是它们足够有用。

The various field definitions take into account the somewhat unusual definitions in the QCUSTCDT file. Those don't make great sense, but they're useful enough.

这篇关于UDTF使用动态SQL在DB2 V5R4上返回表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！