環境：Oracle 11g，plsql 14

目的：不使用*,查詢擁有上百個字段的表的所有字段。

懶人大法：在文章末尾。

sql實現邏輯：

1、首先建一張100個字段以上的表，通過excel的方式將表建好后直接復制粘貼到plsql的建表界面。

2、首先，我們需要獲取TEST1表的字段及注釋，通過Oracle中自帶的表，user_tab_columns a和user_col_comments，這兩張表中存儲著這個賬號下所有的表的字段名、字段順序及注釋。

select a.column_id xh, a.table_name, lower(a.column_name) dm, b.COMMENTS mc

from user_tab_columns a, user_col_comments b

where a.TABLE_NAME = 'TEST1'

AND a.TABLE_NAME = b.TABLE_NAME

and a.COLUMN_NAME = b.COLUMN_NAME

order by a.column_id

4、通過case when語句進行處理，如在第一行添加select，最后一行添加from及表名，其他行添加尾部逗號，同時，將字段及字段注釋合并。

select case

when xh = '1' then

'select ' || dm || ', ' || '/*' || mc || '*/'

when xh = (select max(column_id)

from user_tab_columns aa

where aa.table_name = c.table_name) then

dm || ' ' || '/*' || mc || '*/' || 'from ' ||

c.table_name

else

dm || ', ' || '/*' || mc || '*/'

end val,

xh

from (select a.column_id xh,

a.table_name,

lower(a.column_name) dm,

b.COMMENTS mc

from user_tab_columns a, user_col_comments b

where a.TABLE_NAME = 'TEST1'

AND a.TABLE_NAME = b.TABLE_NAME

and a.COLUMN_NAME = b.COLUMN_NAME

order by a.column_id) c

5、最終處理。將各行利用listagg() within group (order by)函數進行合并處理。

select listagg(d.val, '') within group(order by xh) sql1

from (select case

when xh = '1' then

'select ' || dm || ', ' || '/*' || mc || '*/'

when xh = (select max(column_id)

from user_tab_columns aa

where aa.table_name = c.table_name) then

dm || ' ' || '/*' || mc || '*/' || 'from ' ||

c.table_name

else

dm || ', ' || '/*' || mc || '*/'

end val,

xh

from (select a.column_id xh,

a.table_name,

lower(a.column_name) dm,

b.COMMENTS mc

from user_tab_columns a, user_col_comments b

where a.TABLE_NAME = 'TEST1'

AND a.TABLE_NAME = b.TABLE_NAME

and a.COLUMN_NAME = b.COLUMN_NAME

order by a.column_id) c) d

6、將sql從查詢結果復制粘貼到新的sql窗口，使用plsql美化器美化后即可得到單表多字段查詢的sql了。

懶人大法：不用管如何實現的，直接把下列sql中的TEST1替換為你需要的表名即可查詢。注意：如果表上沒有注釋，會造成注釋處為/**/。

select listagg(d.val, '') within group(order by xh) sql1

from (select case

when xh = '1' then

'select ' || dm || ', ' || '/*' || mc || '*/'

when xh = (select max(column_id)

from user_tab_columns aa

where aa.table_name = c.table_name) then

dm || ' ' || '/*' || mc || '*/' || 'from ' ||

c.table_name

else

dm || ', ' || '/*' || mc || '*/'

end val,

xh

from (select a.column_id xh,

a.table_name,

lower(a.column_name) dm,

b.COMMENTS mc

from user_tab_columns a, user_col_comments b

where a.TABLE_NAME = 'TEST1'

AND a.TABLE_NAME = b.TABLE_NAME

and a.COLUMN_NAME = b.COLUMN_NAME

order by a.column_id) c) d