自动生成SQL查询、删除、更新、插入语句

自动生成sql语句

 select ‘update  ‘ || t.table_name || ‘ a  set ‘ ||
       (select wm_concat(‘a.‘ || a.column_name || ‘=‘ || Chr(39) || ‘{‘ ||
                         Abs(Rownum - 1) || ‘}‘ || chr(39) || ‘   --‘ ||
                         a.Comments || Chr(13) || Chr(10))
          from user_col_comments a
         where a.table_name = t.table_name)
  from user_col_comments t
 where t.table_name = upper(‘com_employee‘)
   and rownum = 1;
--插入语句 
select ‘insert into ‘ || t.table_name || ‘(‘ ||
       (select wm_concat(a.column_name || ‘  --‘ || a.Comments || Chr(13) ||
                         Chr(10))
          from user_col_comments a
         where a.table_name = t.table_name) || ‘)  values (‘ ||
       (select wm_concat(Chr(39) || ‘{‘ || Abs(Rownum - 1) || ‘}‘ || Chr(39) ||
                         ‘  --‘ || a.Comments || Chr(13) || Chr(10))
          from user_col_comments a
         where a.table_name = t.table_name) || ‘)‘
  from user_col_comments t
 where t.table_name = upper(‘com_employee‘)
   and rownum = 1;
--查询语句  
Select (Select ‘select ‘ ||
               Wmsys.Wm_Concat(‘a.‘ || T.Column_Name || ‘  --‘ || T.Comments ||
                               Chr(13) || Chr(10))
          From Sys.User_Col_Comments t
         Where T.Table_Name = A.Table_Name) || ‘ from ‘ || A.Table_Name ||
       ‘ a ‘
  From User_Col_Comments a
 Where A.Table_Name = Upper(‘com_employee‘)
   And Rownum = 1;
--查询语句 ,备注字段
select (select ‘select ‘ ||
               wm_concat(‘a.‘ || t.column_name || ‘ as "‘ ||
                         substr(nvl(trim(t.comments), ‘无备注‘), 0, 15) || ‘"‘ ||
                         Chr(13) || Chr(10))
          from SYS.user_col_comments t
         where t.table_name = a.table_name) || ‘ from ‘ || a.table_name ||
       ‘ a ‘
  from user_col_comments a
 where a.table_name = upper(‘com_employee‘)
   and rownum = 1;

本文出自 “海的儿子” 博客，谢绝转载！

自动生成SQL查询、删除、更新、插入语句

原文：http://4769069.blog.51cto.com/4759069/1825966