WITH db AS ( SELECT DISTINCT
dzag001 AS 作业代码, -- 程序/作业编号
dzag002 AS 表名, -- 绑定数据表
oobx001 AS 单别 -- 单据单别
FROM dsdata.dzag_t -- 画面绑定数据表设置档
LEFT JOIN dsdata.oobx_t ON oobxent = 60 AND oobx004 = dzag001 -- 关联单据别设置档
WHERE dzag005 = 'Y'
AND oobx001 IS NOT NULL
AND '6150' LIKE '%' || oobx001 || '%'),
zd AS (SELECT DECODE(dzed003, 'F', dzed005 || dzed001, dzed001) AS 索引,
TO_CHAR(DBMS_LOB.SUBSTR(RTRIM(XMLAGG(XMLPARSE(CONTENT dzebl001 || ' as "' || SUBSTR(dzebl003, 1, 10) || '",' || CHR(10)
WELLFORMED
) ORDER BY SUBSTR(dzebl001, 1, 4), dzeb021
).GETCLOBVAL(),
',' || CHR(10) ),
2544 -- 截取长度规避ORA-22835
) ) AS 表字段
FROM db
LEFT JOIN dsdata.dzed_t ON db.dzag002 = dzed005 OR db.dzag002 = dzed001 -- 数据表键值档
LEFT JOIN dsdata.dzeb_t ON dzeb001 = dzed005 OR dzeb001 = dzed001 -- 数据表字段主档
LEFT JOIN dsdata.dzebl_t ON dzebl000 = dzeb001 AND dzebl001 = dzeb002 -- 字段多语言档
WHERE dzebl003 NOT LIKE '%自定义%'
AND dzebl002 = 'zh_CN'
GROUP BY DECODE(dzed003, 'F', dzed005 || dzed001, dzed001))
SELECT
dzed005 || l2.dzeal003 || '|' || dzed001 || l1.dzeal003 AS 表名,
'SELECT ' || zd.表字段 || CHR(10) ||
'FROM dsdata.' || dzed001 ||
CASE
WHEN dzed003 = 'F' THEN ',dsdata.' || dzed005
ELSE ''
END || CHR(10) ||
'WHERE ' || REPLACE(dzed001, '_t', 'ent=60') ||
CASE
WHEN dzed003 = 'F'
THEN ' AND ' || REPLACE(dzed004, ',', '||') || '=' || REPLACE(dzed006, ',', '||')
ELSE ''
END AS sql
FROM db
LEFT JOIN dsdata.dzed_t ON db.dzag002 = DECODE(dzed003, 'F', dzed005, dzed001)
LEFT JOIN zd ON DECODE(dzed003, 'F', dzed005 || dzed001, dzed001) = zd.索引
LEFT JOIN dsdata.dzeal_t l1 ON l1.dzeal001 = dzed001 AND l1.dzeal002 = 'zh_CN' -- 数据表中文名称
LEFT JOIN dsdata.dzeal_t l2 ON l2.dzeal001 = dzed005 AND l2.dzeal002 = 'zh_CN'; -- 关联副表中文名称