生成SQL脚本的SQL脚本

Question

假设DBA_TAB_COLUMNS看起来如下所示：

​

我想编写一个SQL或PL/SQL脚本来生成以下文本：

select 'NULL' as A1, B1, QUERY, RECORD_KEY from SMHIST.probsummarym1
union all
select 'NULL' as A1, 'NULL' as B1, QUERY, RECORD_KEY from SMHIST_EIT200.probsummarym1
union all
select A1, 'NULL' as B1, QUERY, RECORD_KEY from SMHIST_EIT300.probsummarym1

所需经费如下：

1. 如果任何SMHIST%架构下的表没有该列，则为该列插入默认的空别名。
2. 列列表按字母顺序排列。

有人能告诉我怎么写这个剧本吗？

Answer 1

编辑：添加了更好的别名和显式CROSS JOIN。添加了XMLAGG版本。

NB: LISTAGG存在于甲骨文版本11.2及以后，并返回VARCHAR2。如果输出字符串大于4000 K，或者在以前的版本中，您可以使用XMLAGG，这对使用(例如：http://psoug.org/definition/xmlagg.htm)来说有点麻烦。

使用LISTAGG (返回VARCHAR2)：

SELECT LISTAGG (line,
                CHR (13) || CHR (10) || 'union all' || CHR (13) || CHR (10))
       WITHIN GROUP (ORDER BY sortorder)
          script
  FROM (SELECT line, ROWNUM sortorder
          FROM (  SELECT    'select '
                         || LISTAGG (
                                  CASE
                                     WHEN tc.column_name IS NULL
                                     THEN
                                        '''NULL'' as '
                                  END
                               || col_join.column_name,
                               ', ')
                            WITHIN GROUP (ORDER BY col_join.column_name)
                         || ' from '
                         || col_join.owner
                         || '.'
                         || col_join.table_name
                            line
                    FROM    dba_tab_columns tc
                         RIGHT OUTER JOIN
                            (SELECT DISTINCT
                                    owner, table_name, col_list.column_name
                               FROM    dba_tab_columns
                                    CROSS JOIN
                                       (SELECT DISTINCT column_name
                                          FROM dba_tab_columns
                                         WHERE owner LIKE 'SMHIST%') col_list
                              WHERE owner LIKE 'SMHIST%') col_join
                         ON     tc.owner = col_join.owner
                            AND tc.table_name = col_join.table_name
                            AND tc.column_name = col_join.column_name
                GROUP BY col_join.owner, col_join.table_name
                ORDER BY col_join.owner, col_join.table_name))

对于XMLAGG (通过添加.getclobval ()返回CLOB，注意：RTRIM在这里工作，因为表名不能包括','和' ' (空格))：

SELECT REPLACE (SUBSTR (script, 1, LENGTH (script) - 12),
                '&' || 'apos;',
                '''')
  FROM (SELECT XMLAGG (
                  XMLELEMENT (
                     e,
                     line,
                        CHR (13)
                     || CHR (10)
                     || 'union all'
                     || CHR (13)
                     || CHR (10))).EXTRACT ('//text()').getclobval ()
                  script
          FROM (SELECT line, ROWNUM sortorder
                  FROM (  SELECT    'select '
                                 || RTRIM (
                                       REPLACE (
                                          XMLAGG (XMLELEMENT (
                                                     e,
                                                        CASE
                                                           WHEN tc.column_name
                                                                   IS NULL
                                                           THEN
                                                              '''NULL'' as '
                                                        END
                                                     || col_join.column_name,
                                                     ', ') ORDER BY
                                                              col_join.column_name).EXTRACT (
                                             '//text()').getclobval (),
                                          '&' || 'apos;',
                                          ''''),
                                       ', ')
                                 || ' from '
                                 || col_join.owner
                                 || '.'
                                 || col_join.table_name
                                    line
                            FROM    dba_tab_columns tc
                                 RIGHT OUTER JOIN
                                    (SELECT DISTINCT
                                            owner,
                                            table_name,
                                            col_list.column_name
                                       FROM    dba_tab_columns
                                            CROSS JOIN
                                               (SELECT DISTINCT column_name
                                                  FROM dba_tab_columns
                                                 WHERE owner LIKE 'SMHIST%') col_list
                                      WHERE owner LIKE 'SMHIST%') col_join
                                 ON     tc.owner = col_join.owner
                                    AND tc.table_name = col_join.table_name
                                    AND tc.column_name = col_join.column_name
                        GROUP BY col_join.owner, col_join.table_name
                        ORDER BY col_join.owner, col_join.table_name)))