oracle 回滚字段过小_Oracle-单表多字段查询（不使用*）

环境：Oracle 11g，plsql 14

目的：不使用*,查询拥有上百个字段的表的所有字段。

懒人大法：在文章末尾。

sql实现逻辑：

1、首先建一张100个字段以上的表，通过excel的方式将表建好后直接复制粘贴到plsql的建表界面。

利用excel快速建表

复制粘贴到PLSQL中，建表test1完成。

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