php网站开发软件是什么,湛江建网站,app开发教程,wordpress给通知用户邮件环境#xff1a;Oracle 11g#xff0c;plsql 14目的#xff1a;不使用*,查询拥有上百个字段的表的所有字段。懒人大法#xff1a;在文章末尾。sql实现逻辑#xff1a;1、首先建一张100个字段以上的表#xff0c;通过excel的方式将表建好后直接复制粘贴到plsql的建表界面。…环境Oracle 11gplsql 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) d6、将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
