了解 DB2 中的复杂 SP

Question

我需要对具有一堆复杂XML函数的SP进行更改

，而不是

Declare ResultCsr2 Cursor For
WITH
MDI_BOM_COMP(PROD_ID,SITE_ID, xml ) AS (
SELECT TC401F.T41PID,TC401F.T41SID,
XMLSERIALIZE(
XMLAGG(
XMLELEMENT( NAME "MDI_BOM_COMP",
XMLFOREST(
trim(TC401F.T41CTY) AS COMPONENT_TYPE,
TC401F.T41LNO AS COMP_NUM,
trim(TC401F.T41CTO) AS CTRY_OF_ORIGIN,
trim(TC401F.T41DSC) AS DESCRIPTION,
TC401F.T41EFR AS EFFECTIVE_FROM,
TC401F.T41EFT AS EFFECTIVE_TO,
trim(TC401F.T41MID) AS MANUFACTURER_ID,
trim(TC401F.T41MOC) AS MANUFACTURER_ORG_CODE,
trim(TC401F.T41CNO) AS PROD_ID,
trim(TC401F.T41POC) AS PROD_ORG_CODE,
TC401F.T41QPR AS QTY_PER,
trim(TC401F.T41SBI) AS SUB_BOM_ID,
trim(TC401F.T41SBO) AS SUB_BOM_ORG_CODE,       --ADB01
trim(TC401F.T41VID) AS SUPPLIER_ID,
trim(TC401F.T41SOC) AS SUPPLIER_ORG_CODE,
TC401F.T41UCT AS UNIT_COST
)
)
) AS CLOB(1M)
)
FROM TC401F TC401F
GROUP BY T41PID,T41SID
)
SELECT
RowNum, '<BOM_INBOUND>' ||
XMLSERIALIZE (
XMLELEMENT(NAME "INTEGRATION_MESSAGE_CONTROL",
XMLFOREST(
'FULL_UPDATE' as ACTION,
'POLARIS' as COMPANY_CODE,
TRIM(TC400F.T40OCD) as ORG_CODE,
'5' as PRIORITY,
'INBOUND_ENTITY_INTEGRATION' as MESSAGE_TYPE,
'POLARIS_INTEGRATION' as USERID,
'TA' as RECEIVER,
HEX(Generate_Unique()) as SOURCE_SYSTEM_TOKEN
),
XMLELEMENT(NAME "BUS_KEY",
XMLFOREST(
TRIM(TC400F.T40BID) as BOM_ID,
TRIM(TC400F.T40OCD) as ORG_CODE
)
)
) AS VARCHAR(1000)
)
|| '<MDI_BOM>' ||
XMLSERIALIZE (
XMLFOREST(
TRIM(TC400F.T40ATP) AS ASSEMBLY_TYPE,
TRIM(TC400F.T40BID) AS BOM_ID,
TRIM(TC400F.T40CCD) AS CURRENCY_CODE,
TC400F.T40DPC AS DIRECT_PROCESSING_COST,
TC400F.T40EFD AS EFFECTIVE_FROM,
TC400F.T40EFT AS EFFECTIVE_TO,
TRIM(TC400F.T40MID) AS MANUFACTURER_ID,
TRIM(TC400F.T40MOC) AS MANUFACTURER_ORG_CODE,
TRIM(TC400F.T40OCD) AS ORG_CODE,
TRIM(TC400F.T40PRF) AS PROD_FAMILY,
TRIM(TC400F.T40PID) AS PROD_ID,
TRIM(TC400F.T40POC) AS PROD_ORG_CODE,
TRIM(TC400F.T40ISA) AS IS_ACTIVE,
TRIM(TC400F.T40VID) AS SUPPLIER_ID,
TRIM(TC400F.T40SOC) AS SUPPLIER_ORG_CODE,
TRIM(TC400F.T40PSF) AS PROD_SUB_FAMILY,
CASE TRIM(TC400F.T40PML)
WHEN '' THEN TRIM(TC400F.T40PML)
ELSE TRIM(TC400F.T40PML) || '~' || TRIM(TC403F.T43MDD)
END    AS PROD_MODEL
) AS VARCHAR(3000)
)
|| IFNULL(MBC.xml, '') ||
XMLSERIALIZE (
XMLFOREST(
XMLFOREST(
TRIM(TC400F.T40CCD) AS CURRENCY_CODE,
TC400F.T40PRI AS PRICE,
TRIM(TC400F.T40PTY) AS PRICE_TYPE
) AS MDI_BOM_PRICE,
XMLFOREST(
TRIM(TC400F.T40CCD) AS CURRENCY_CODE,
TRIM(TC400F.T40PRI) AS PRICE,
'TRANSACTION_VALUE' AS PRICE_TYPE
) AS MDI_BOM_PRICE,
XMLFOREST(
TRIM(TC400F.T40INA) AS INCLUDE_IN_AVERAGING
) AS MDI_BOM_IMPL_BOM_PROD_FAMILY_AUTOMOBILES
) AS VARCHAR(3000)
)
|| '</MDI_BOM>' ||
'</BOM_INBOUND>' XML
FROM (
SELECT
ROW_NUMBER() OVER (
ORDER BY T40STS
,T40SID
,T40BID
) AS RowNum
,t.*
FROM TC400F t
) TC400F
LEFT OUTER JOIN MDI_BOM_COMP MBC
ON TC400F.T40SID = MBC.SITE_ID
AND TC400F.T40PID = MBC.PROD_ID
LEFT OUTER JOIN TC403F TC403F
ON TC400F.T40PML <> ''
AND TC400F.T40PML = TC403F.T43MDL
WHERE TC400F.T40STS = '10'
AND TC400F.RowNUM BETWEEN
(P_STARTROW + (P_PAGENOS - 1) * P_NBROFRCDS)
AND (P_STARTROW  + (P_PAGENOS - 1) * P_NBROFRCDS  +
P_NBROFRCDS - 1);

上面给出的是 SP 代码中的游标声明，我正在努力理解。第一个WITH本身似乎很神秘。我已经将它与临时表名一起使用，但这是第一次，我看到这种似乎是 SP 或 UDF 的东西？有人可以指导我如何理解和理解这一切吗？

进一步补充问题，此处的实际要求是将数据排列在XML中，以便那些具有TC401F的记录。填充的 T41SBI 字段应出现在 XML 输出的开头。

此字段在代码中按如下方式选择：

trim(TC401F.T41SBI) AS SUB_BOM_ID.如果此字段为非空字段，则应首先在 XML 中显示，并且此字段值为"空白"的任何记录应仅显示在之后。最好的方法是什么？以任何方式使用 ORDER BY 似乎并没有真正的帮助，因为 XML 实际上是通过某些函数创建的，排序依据不会影响项在 XML 中的排列方式。我能想到的一种方法是使用 where 子句，首先TC401F.T41SBI <> ''然后将这些记录附加到TC401F.T41SBI = ''

Answer 1

我能做的最好的事情就是帮助CTE。

WITH
MDI_BOM_COMP(PROD_ID,SITE_ID, xml ) AS (
SELECT TC401F.T41PID,TC401F.T41SID,

这只会生成一个名为 MDI_BOM_COMP 的表，其中包含名为 PROD_ID、SITE_ID 和 XML 的三列。 该表将为每个PROD_ID、SITE_ID各有一条记录，XML 的内容将是一个 XML 代码段，其中包含该产品和站点的所有组件。

现在 XML 部分可能有点令人困惑，但是如果我们将其分解为标量和聚合组件，我们可以使其更容易理解。

首先忽略分组。因此 CTE 检索TC401F中的每一行。XMLELEMENT和XMLFORREST是标量函数。XMLELEMENT创建单个 XML 元素 标记是上面示例中的第一个参数，元素的内容是第二个参数。XMLFORREST就像一堆连接在一起的XMLELEMENT。

XMLSERIALIZE(
XMLAGG(
XMLELEMENT( NAME "MDI_BOM_COMP",
XMLFOREST(
trim(TC401F.T41CTY) AS COMPONENT_TYPE,
TC401F.T41LNO AS COMP_NUM,
trim(TC401F.T41CTO) AS CTRY_OF_ORIGIN,
trim(TC401F.T41DSC) AS DESCRIPTION,
TC401F.T41EFR AS EFFECTIVE_FROM,
TC401F.T41EFT AS EFFECTIVE_TO,
trim(TC401F.T41MID) AS MANUFACTURER_ID,
trim(TC401F.T41MOC) AS MANUFACTURER_ORG_CODE,
trim(TC401F.T41CNO) AS PROD_ID,
trim(TC401F.T41POC) AS PROD_ORG_CODE,
TC401F.T41QPR AS QTY_PER,
trim(TC401F.T41SBI) AS SUB_BOM_ID,
trim(TC401F.T41SBO) AS SUB_BOM_ORG_CODE,       --ADB01
trim(TC401F.T41VID) AS SUPPLIER_ID,
trim(TC401F.T41SOC) AS SUPPLIER_ORG_CODE,
TC401F.T41UCT AS UNIT_COST
)
)
) AS CLOB(1M)

所以在示例中，对于表中的每一行，XMLFORREST创建一个XML元素列表，COMPONENT_TYPE、COMP_NUM、CTRY_OF_ORIGIN等各一个。这些元素构成了由XMLELEMENT创建的另一个 XML 元素MDI_BOM_COMP的内容。

现在，对于表中的每一行，我们选择了PROD_ID、SITE_ID，并创建了一些XML。接下来我们按PROD_ID分组，SITE_ID。聚合函数XMLAGG将收集每个PROD_ID和SITE_ID的所有XML，并将其连接在一起。

最后XMLSERIALIZE会将内部XML表示形式转换为我们都知道并喜欢的字符串格式;)

Answer 2

我想我找到了满足我要求的答案。我不得不在XMLELEMENT函数之后按字段名称添加顺序