ORACLE如何生成SELECT表中所有字段的SQL语句

在ORACLE数据库中，有时候我们可能需要查询某个表，或者转存表中的数据到全局临时表..... ,我们知道为了SQL的性能和健壮性，不建议使用SELECT * ，而是应该是用具体字段替换*， 但是对于有些宽表或字段较多的表，如果在SELECT语句中一个个敲写每个字段，确实也是一个枯燥、繁琐、低效的工作，那么我们能否用SQL语句或工具生成一个表的SELECT语句，这个SELECT语句查询了所有字段（如果业务只需要部分字段，我们可以在生成的语句上删减部分字段）。下面简单总结了一些方法。仅供参考。

SQL 1:

/******************************************************************************************************    脚本功能：   生成当前用户下某个表的SELECT查询语句,使用USER_TAB_COLUMNS视图，普通用户使用。    注意事项：   1：下面这种方式,WM_CONCAT并不能保证输出的字段按COLUMN_ID顺序输出.               2：脚本依赖WM_CONCAT函数*******************************************************************************************************/SET LONG 99999999;SET PAGESIZE 10000;SELECT 'SELECT ' || CHR(10)||'      ' || REPLACE(WM_CONCAT(T.COLUMN_NAME), ',', ','||CHR(10)||'       ')     || CHR(10)  || 'FROM  ' || T.TABLE_NAME || ';'     AS SELECT_SQL_TEXTFROM(    SELECT TABLE_NAME           ,COLUMN_NAME      FROM USER_TAB_COLUMNS    WHERE TABLE_NAME=UPPER('&TABLE_NAME')    ORDER BY COLUMN_ID)  TGROUP BY  T.TABLE_NAME;

SQL2:

/******************************************************************************************************    脚本功能：   生成当前用户下有权限的某个表的SELECT查询语句,使用ALL_TAB_COLUMNS视图，普通用户使用。    注意事项：   1：下面这种方式,WM_CONCAT并不能保证输出的字段按COLUMN_ID顺序输出               2：脚本依赖WM_CONCAT函数*******************************************************************************************************/SET LONG 99999999;SET PAGESIZE 10000;SELECT 'SELECT ' || CHR(10)||'       ' || REPLACE(WM_CONCAT(T.COLUMN_NAME), ',', ','||CHR(10)||'       ')     || CHR(10)  || 'FROM  ' || T.OWNER ||'.'|| T.TABLE_NAME || ';'     AS SELECT_SQL_TEXTFROM(    SELECT OWNER          ,TABLE_NAME           ,COLUMN_NAME      FROM DBA_TAB_COLUMNS    WHERE OWNER=UPPER('&OWNER')      AND TABLE_NAME=UPPER('&TABLE_NAME')    ORDER BY COLUMN_ID)  TGROUP BY T.OWNER, T.TABLE_NAME;

SQL 3:

/******************************************************************************************************    脚本功能：   生成数据库中任意某个表的SELECT查询语句，使用DBA_TAB_COLUMNS视图    注意事项：   1：下面这种方式,WM_CONCAT并不能保证输出的字段按COLUMN_ID顺序输出               2：脚本依赖WM_CONCAT函数*******************************************************************************************************/SET LONG 99999999;SET PAGESIZE 10000;SELECT 'SELECT ' || CHR(10)||'      ' || REPLACE(WM_CONCAT(T.COLUMNS), ',', ','||CHR(10)||'      ')     || CHR(10)  || 'FROM  ' || T.OWNER ||'.'|| T.TABLE_NAME || ';'     AS SELECT_SQL_TEXTFROM(SELECT OWNER, TABLE_NAME ,COLUMN_NAME  AS COLUMNSFROM DBA_TAB_COLUMNSWHERE   OWNER=UPPER('&OWNER')    AND TABLE_NAME=UPPER('&TABLE_NAME')ORDER BY COLUMN_ID)  TGROUP BY  T.OWNER, T.TABLE_NAME;

上面三段SQL分别依赖USER_TAB_COLUMNS、ALL_TAB_COLUMNS、DBA_TAB_COLUMNS这三个不同权限的视图，其它功能都一样，但是上面这种写法，WM_CONAT函数无法保字段顺序。如下例子所示：

如果要保证SELECT语句中字段有序（按COLUMN_ID或其它字段排序）的话，可以使用下面改写的SQL，具体如下所示：

SQL 1:

 /******************************************************************************************************    脚本功能：   当前用户下有权限的某个表的SELECT查询语句,使用ALL_TAB_COLUMNS视图，普通用户使用。    注意事项：   1：此脚本能保证SELECT按字段COLUMN_ID排序。               2：脚本依赖WM_CONCAT函数*******************************************************************************************************/SET LONG 99999999;SET PAGESIZE 10000;WITH TAB_COLUMNS AS(    SELECT WM_CONCAT(COLUMN_NAME)   OVER (ORDER BY COLUMN_ID)    AS TB_COLUMNS          ,COUNT(COLUMN_NAME) OVER (ORDER BY COLUMN_ID)          AS ROW_CT          ,COUNT(COLUMN_NAME) OVER (PARTITION BY OWNER,TABLE_NAME)  AS TOT_CT          ,OWNER ||'.' || TABLE_NAME AS TABLE_NAME    FROM ALL_TAB_COLUMNS    WHERE OWNER = UPPER('&OWNER')      AND TABLE_NAME= UPPER('&TABLE_NAME')    ORDER BY COLUMN_ID)SELECT 'SELECT '    || CHR(10)    ||'      '    || REPLACE(TB_COLUMNS, ',', ','||CHR(10)||'      ')    || CHR(10)    || 'FROM '    || TABLE_NAME    || ';'FROM TAB_COLUMNSWHERE ROW_CT = TOT_CT;

SQL 2:

/******************************************************************************************************    脚本功能：   生成数据库中某个表的SELECT查询语句,使用DBA_TAB_COLUMNS视图。    注意事项：   1：此脚本能保证SELECT按字段COLUMN_ID排序。               2：脚本依赖WM_CONCAT函数*******************************************************************************************************/SET LONG 99999999;SET PAGESIZE 10000;WITH TAB_COLUMNS AS(    SELECT WM_CONCAT(COLUMN_NAME)   OVER (ORDER BY COLUMN_ID)   AS TB_COLUMNS          ,COUNT(COLUMN_NAME) OVER (ORDER BY COLUMN_ID)         AS ROW_CT          ,COUNT(COLUMN_NAME) OVER (PARTITION BY OWNER,TABLE_NAME)    AS TOT_CT          ,OWNER ||'.' || TABLE_NAME AS TABLE_NAME    FROM DBA_TAB_COLUMNS    WHERE OWNER = UPPER('&OWNER')      AND TABLE_NAME= UPPER('&TABLE_NAME')    ORDER BY COLUMN_ID)SELECT 'SELECT '    || CHR(10)    || '     '    || REPLACE(TB_COLUMNS, ',', ','||CHR(10)||'     ')    || CHR(10)    || 'FROM '    || TABLE_NAME    || ';'FROM TAB_COLUMNSWHERE ROW_CT = TOT_CT ;

上面的脚本都有个弊端，就是依赖WM_CONCAT函数，我们知道可能由于数据库版本问题，你的环境中可能没有WM_CONCAT函数。其实我们也可以使用聚合函数XMLAGG来实现这个功能，如下所示:

******************************************************************************************************    脚本功能：   生成数据库中某个表的SELECT查询语句,使用DBA_TAB_COLUMNS视图。不依赖WM_CONCAT函数    注意事项：   暂无*******************************************************************************************************/WITH TAB_COLUMN_LISTAS(    SELECT  OWNER || '.' || TABLE_NAME AS TABLE_NAME           ,XMLAGG(XMLELEMENT(C, C.COLUMN_NAME, ',') ORDER BY C.COLUMN_ID).EXTRACT('//text()').GETCLOBVAL() AS COLUMN_LIST    FROM   DBA_TAB_COLUMNS C     WHERE  OWNER=UPPER('&OWNER')       AND TABLE_NAME=UPPER('&TABLE_NAME')    GROUP BY OWNER, TABLE_NAME)SELECT 'SELECT '    || CHR(10)    ||'     '    || REPLACE(TRIM(',' FROM COLUMN_LIST), ',', ','||CHR(10)||'     ')    || CHR(10)    || 'FROM '    || TABLE_NAME    || ';'FROM TAB_COLUMN_LIST;

如果是Oracle 11g或以上版本，我们也可以借助LISTAGG函数实现这个功能，简单的脚本如下所示：

SQL 1:

/******************************************************************************************************    脚本功能：   生成当前用户有权限的某个表的SELECT查询语句,适用于普通用户。    注意事项：   此脚本只适用于ORACLE 11g以上版本。*******************************************************************************************************/ SET LONG 99999999;SET PAGESIZE 10000;SELECT 'SELECT '      || CHR(10)     || '      '     || REPLACE(COL_LIST, ',', ','||CHR(10)||'     ')      || CHR(10)     || 'FROM '      || T.TABLE_NAME      || ';' AS SQL_TEXTFROM(  SELECT DISTINCT OWNER || '.' || TABLE_NAME AS TABLE_NAME       , LISTAGG(COLUMN_NAME, ', ') WITHIN GROUP (ORDER BY COLUMN_ID)  OVER (PARTITION BY OWNER,TABLE_NAME) COL_LIST  FROM ALL_TAB_COLUMNS  WHERE OWNER = UPPER('&OWNER')    AND TABLE_NAME =UPPER('&TABLE_NAME')) T;

SQL 2:

/******************************************************************************************************    脚本功能：   生成数据库中某个表的SELECT查询语句    注意事项：   此脚本只适用于ORACLE 11g以上版本。*******************************************************************************************************/ SET LONG 99999999;SET PAGESIZE 10000;SELECT 'SELECT '      || CHR(10)     || '      '     || REPLACE(COL_LIST, ',', ','||CHR(10)||'     ')      || CHR(10)     || 'FROM '      || T.TABLE_NAME      || ';' AS SQL_TEXTFROM(  SELECT DISTINCT OWNER || '.' || TABLE_NAME AS TABLE_NAME
       , LISTAGG(COLUMN_NAME, ', ') WITHIN GROUP (ORDER BY COLUMN_ID)  OVER (PARTITION BY OWNER,TABLE_NAME) COL_LIST
  FROM DBA_TAB_COLUMNS
  WHERE OWNER = UPPER('&OWNER')
    AND TABLE_NAME =UPPER('&TABLE_NAME')
) T;

另外，其它一些工具也有相关功能实现这个，例如SQL Developer工具，在左侧找到对应的表，然后将其拖入右边的“工作表”，然后选择下图的“选项”后，点击应用也会生成对应的SELECT语句。