oracle 复杂SQL持续更新

缺字段查询
select ut.TABLE_NAME,--表名
      ut.COLUMN_NAME,--字段名称
      uc.comments,--字段注释
      ut.DATA_TYPE,--字典类型
      ut.DATA_LENGTH,--字典长度
      ut.NULLABLE,--是否为空
      'alter table '||ut.TABLE_NAME||' add '||ut.COLUMN_NAME||' '||ut.DATA_TYPE||'('||ut.DATA_LENGTH||');
comment on column '||ut.TABLE_NAME||'.'||ut.COLUMN_NAME||' is '''|| uc.comments||''';' 创建语句
from user_tab_columns  ut
inner JOIN user_col_comments uc
on ut.TABLE_NAME  = uc.table_name and ut.COLUMN_NAME = uc.column_name
where ut.COLUMN_NAME='WORKFLOW_ID' 

//删除 父节点已被删除的数据     多执行几遍，每次执行查询父节点被删除的
update EDU_PROFESSION t set t.del_flag=1  where t.profession_parent_id in(select a.id from EDU_PROFESSION a where a.del_flag='1')  and t.del_flag=0
select * from EDU_PROFESSION t where t.profession_parent_id in(select a.id from EDU_PROFESSION a where a.del_flag='1') and t.del_flag=0
 

like 查询
select * from KPI_DICT t where '二级指标的' like '%'||t.dict_name||'%'

select t.title, t.content, count(1) num, wm_concat(k.name) keys
  from article_manager t
 inner join ARTICLE_KEYS k
    on t.id = k.manager_id
 where '我想写一首唐朝的爱情诗' like '%' || k.name || '%'
 group by t.title, t.content
 order by count(1) desc

行转列
select *
  from (select a.staff_name, b.edu_item_name, b.class_hour
          from JHNIMS_EDUCATION_NURSE a, JHNIMS_EDUCATION_EXAM b
         where a.edu_id = b.id
          -- and a.staff_name = '莉莉'
           and b.class_hour is not null)
pivot(sum(class_hour)
   for edu_item_name in('三基考试' 三基考试));

批量插入
INSERT ALL
INTO JHNIMS_REPORT_ITEMS  (FIELD_NAME,FIELD_VALUE,DAY_ID,DELETE_FLAG,INPUT_TYPE,ID) VALUES(N'1663',N'0',N'7684',N'1',N'0',33954)  
SELECT 33954  FROM DUAL

//累计百分比
select t1.dict_name,
       t1.num,
       t2.allNum,
       round(t1.num / t2.allNum * 100, 2) || '%' percentage,
       round(t1.percentage / t2.allNum * 100, 2) || '%' totalPercentage
  from (select t.dict_name,
               count(*) num,
               sum(count(*)) over(order by t.dict_name) percentage
          from JHNIMS_QUALITY_CHECK_MASTER t
         group by t.dict_name) t1,
       (select count(1) allNum from JHNIMS_QUALITY_CHECK_MASTER) t2