存储过程

 更新数据

merge into 表名 a
using(
select  
from
where
group by 
)  on (

条件
)

--如果条件成立，执行更新
when matched then
update set 只需更改条件即可

--如果条件不成立，执行添加
when not matched then
insert (
) values(
);
commit;

按照表中的一个时间字段分组，统计历史每天的量

create or replace procedure PDW_DAY_HISTORY(o_status  out  integer,
                                             o_info    out  varchar2)
/**************************************************
  * 作    者：XX
    日    期：2018-06-25
    功    能：
        汇集银行端、开发商端、个人端历史每日报卷量
    算    法：
        1.按日期、各个系统汇总

    源    表：
        bank_pledge、deve_dcxx
    目 标 表：
        dw_bank_deve_per_day（sys_type，个人端：0 银行端：1 开发商端：2 ）
    修改历史：
    时间         内容                               修改人
   ------------ ----------------------------------- --------
   2014-3-3     添加注释说明                        XXX
  **************************************************/
is
begin
  o_status:=0;

  delete from dw_bank_deve_per_day;

  --插入银行端数据
  insert into dw_bank_deve_per_day(
    date_id,
    sys_type,
    count_addres,
    count_accept,
    count_refuse
  )
  SELECT TO_char(A.gmt_commit, 'yyyymmdd') date_id,0,A.count_addres count_addres ,nvl(B.count_accept,0) count_accept,nvl(C.count_refuse,0) count_refuse
  FROM  (
       select to_date(TO_char(gmt_commit, 'yyyymmdd'), 'yyyymmdd') gmt_commit,
              count(1) count_addres
       from zzbdc.per_case
            where commit_flag = '1'
            group by to_date(TO_char(gmt_commit, 'yyyymmdd'), 'yyyymmdd')
            order by to_date(TO_char(gmt_commit, 'yyyymmdd'), 'yyyymmdd')
       ) A LEFT JOIN(
       select to_date(TO_char(gmt_update, 'yyyymmdd'), 'yyyymmdd') gmt_update,
              count(1) count_accept
       from zzbdc.per_case
            where commit_flag = '1'and realestatecaseno is not null
            group by to_date(TO_char(gmt_update, 'yyyymmdd'), 'yyyymmdd')
            order by to_date(TO_char(gmt_update, 'yyyymmdd'), 'yyyymmdd')
       ) B ON A.gmt_commit = B.gmt_update
       LEFT JOIN(
       select to_date(TO_char(gmt_update, 'yyyymmdd'), 'yyyymmdd') gmt_update,
              count(1) count_refuse
          from zzbdc.per_case
            where commit_flag = '1'and deal_info is not null
            group by to_date(TO_char(gmt_update, 'yyyymmdd'), 'yyyymmdd')
            order by to_date(TO_char(gmt_update, 'yyyymmdd'), 'yyyymmdd')
       ) C
       ON B.gmt_update = C.gmt_update ORDER BY date_id;
  commit;
  --插入银行端数据
  insert into dw_bank_deve_per_day(
    date_id,
    sys_type,
    count_addres,
    count_accept,
    count_refuse
  )
  SELECT TO_char(A.gmt_commit, 'yyyymmdd') date_id,1,A.count_addres count_addres ,nvl(B.count_accept,0) count_accept,nvl(C.count_refuse,0) count_refuse
  FROM  (
       select to_date(TO_char(gmt_commit, 'yyyymmdd'), 'yyyymmdd') gmt_commit,
              count(1) count_addres
       from zzbdc.bank_pledge
            where commit_flag = '1'
            group by to_date(TO_char(gmt_commit, 'yyyymmdd'), 'yyyymmdd')
            order by to_date(TO_char(gmt_commit, 'yyyymmdd'), 'yyyymmdd')
       ) A LEFT JOIN(
       select to_date(TO_char(gmt_update, 'yyyymmdd'), 'yyyymmdd') gmt_update,
              count(1) count_accept
       from zzbdc.bank_pledge
            where commit_flag = '1'and realestatecaseno is not null
            group by to_date(TO_char(gmt_update, 'yyyymmdd'), 'yyyymmdd')
            order by to_date(TO_char(gmt_update, 'yyyymmdd'), 'yyyymmdd')
       ) B ON A.gmt_commit = B.gmt_update
       LEFT JOIN(
       select to_date(TO_char(gmt_update, 'yyyymmdd'), 'yyyymmdd') gmt_update,
              count(1) count_refuse
          from zzbdc.bank_pledge
            where commit_flag = '1'and deal_info is not null
            group by to_date(TO_char(gmt_update, 'yyyymmdd'), 'yyyymmdd')
            order by to_date(TO_char(gmt_update, 'yyyymmdd'), 'yyyymmdd')
       ) C
       ON B.gmt_update = C.gmt_update ORDER BY date_id;
  commit;
  --插入开发商端数据
  insert into dw_bank_deve_per_day(
    date_id,
    sys_type,
    count_addres,
    count_accept,
    count_refuse
  )
  SELECT TO_char(A.gmt_commit, 'yyyymmdd') date_id,2,A.count_addres count_addres ,nvl(B.count_accept,0) count_accept,nvl(C.count_refuse,0) count_refuse
  FROM  (
       select to_date(TO_char(gmt_commit, 'yyyymmdd'), 'yyyymmdd') gmt_commit,
              count(1) count_addres
       from zzbdc.deve_dcxx
            where commit_flag = '1'
            group by to_date(TO_char(gmt_commit, 'yyyymmdd'), 'yyyymmdd')
            order by to_date(TO_char(gmt_commit, 'yyyymmdd'), 'yyyymmdd')
       ) A LEFT JOIN(
       select to_date(TO_char(gmt_update, 'yyyymmdd'), 'yyyymmdd') gmt_update,
              count(1) count_accept
       from zzbdc.deve_dcxx
            where commit_flag = '1'and realestatecaseno is not null
            group by to_date(TO_char(gmt_update, 'yyyymmdd'), 'yyyymmdd')
            order by to_date(TO_char(gmt_update, 'yyyymmdd'), 'yyyymmdd')
       ) B ON A.gmt_commit = B.gmt_update
       LEFT JOIN(
       select to_date(TO_char(gmt_update, 'yyyymmdd'), 'yyyymmdd') gmt_update,
              count(1) count_refuse
          from zzbdc.deve_dcxx
            where commit_flag = '1'and deal_info is not null
            group by to_date(TO_char(gmt_update, 'yyyymmdd'), 'yyyymmdd')
            order by to_date(TO_char(gmt_update, 'yyyymmdd'), 'yyyymmdd')
       ) C
       ON B.gmt_update = C.gmt_update ORDER BY date_id;
  commit;

  --执行完毕
  o_status:=1;
  exception
  when others then
    rollback;
    --发生异常
    o_status:=99;
    o_info:=SQLCODE||'---'||SQLERRM||'---'||dbms_utility.format_error_backtrace;
end PDW_DAY_HISTORY;

汇总当天数据，其中p_tx_date 为传入的当天时间参数

create or replace procedure PDW_BANK_DEVE_PER_DAY(p_tx_date date,
                                             o_status  out  integer,
                                             o_info    out  varchar2)
/**************************************************
  * 作    者：高想
    日    期：2018-06-21
    功    能：
        汇集每日银行端、开发商端、个人端报卷量
    算    法：
        1.按日期、各个系统汇总

    源    表：
        bank_pledge、deve_dcxx、per_case
    临 时 表：
        tmp_bank_deve_per_day
    目 标 表：
        dw_bank_deve_per_day（sys_type，个人端：0 银行端：1 开发商端：2 ）
    修改历史：
    时间         内容                               修改人
   ------------ ----------------------------------- --------
   2014-3-3     添加注释说明                        XXX
  **************************************************/
is
  v_tx_date date;
  v_date_id varchar2(8);
begin
  o_status:=0;
  v_tx_date:=trunc(p_tx_date);
  v_date_id:=to_char(v_tx_date,'yyyymmdd');

  --清空临时表
  --execute immediate 'truncate table tmp_bank_deve_per_day';

  delete from dw_bank_deve_per_day 
  where date_id=v_date_id;
    
 --插入个人端数据
  insert into dw_bank_deve_per_day(
    date_id,
    sys_type,
    count_addres,
    count_accept,
    count_refuse
  )
  select v_date_id,
    0 dummy,
    (select count(1) from zzbdc.per_case where commit_flag='1' and trunc(gmt_commit)=v_tx_date) count_addres,
    (select count(1) from zzbdc.per_case where commit_flag='1' and realestatecaseno is not null and trunc(gmt_update)=v_tx_date ) count_accept,
    (select count(1) from zzbdc.per_case where commit_flag='1' and deal_info is not null and trunc(gmt_update)=v_tx_date) count_refuse
  from dual group by dummy;
  commit;
  --插入银行端数据
  insert into dw_bank_deve_per_day(
    date_id,
    sys_type,
    count_addres,
    count_accept,
    count_refuse
  )
  select v_date_id,
    1 dummy,
    (select count(1) from zzbdc.bank_pledge where commit_flag='1' and trunc(gmt_commit)=v_tx_date) count_addres,
    (select count(1) from zzbdc.bank_pledge where commit_flag='1' and realestatecaseno is not null and trunc(gmt_update)=v_tx_date ) count_accept,
    (select count(1) from zzbdc.bank_pledge where commit_flag='1' and deal_info is not null and trunc(gmt_update)=v_tx_date) count_refuse
  from dual group by dummy;
  commit;
  --插入开发商端数据
  insert into dw_bank_deve_per_day(
    date_id,
    sys_type,
    count_addres,
    count_accept,
    count_refuse
  )
  select v_date_id,
    2 dummy,
    (select count(1) from zzbdc.deve_dcxx where commit_flag='1' and trunc(gmt_commit)=v_tx_date) count_addres,
    (select count(1) from zzbdc.deve_dcxx where commit_flag='1' and realestatecaseno is not null and trunc(gmt_update)=v_tx_date ) count_accept,
    (select count(1) from zzbdc.deve_dcxx where commit_flag='1' and deal_info is not null and trunc(gmt_update)=v_tx_date) count_refuse
  from dual group by dummy;
  commit;

  --执行完毕
  o_status:=1;
  exception
  when others then
    rollback;
    --发生异常
    o_status:=99;
    o_info:=SQLCODE||'---'||SQLERRM||'---'||dbms_utility.format_error_backtrace;
end PDW_BANK_DEVE_PER_DAY;

汇总数据

create or replace procedure pdw_case_collect(p_tx_date date,
                                             o_status  out  integer,
                                             o_info    out  varchar2)
/**************************************************
  * 作    者：XXX
    日    期：2017-11-2
    功    能：
       整理案卷信息
    算    法：
        1.按案卷编号汇总业务数,包含所有业务
        2.快速转移和抵押使用qicguid作为案卷编号，快速查封以id0作为案卷编号
        3.记录案卷各环节时长，注：有回退和移交的情况，每个环节会有多次，这里开始时间
          为首次进入该环节时间，结束时间为最后一次完成该环节时间
        4.权籍调查regist_type='0701' 不会记录部门、时间

    源    表：
        zzbdc.bdc_djzb, zzbdc.bdc_yw_sh, zzbdc.wfprocess, zzbdc.wfprocesshistory, zzbdc.sysuser, zzbdc.sysdept
    临 时 表：

    目 标 表：
        dw_case_collect
    修改历史：
    时间         内容                               修改人
   ------------ ----------------------------------- --------
   2014-3-3     添加注释说明                        XXX
  **************************************************/
  is
  v_tx_date date;
begin
  o_status:=0;
  v_tx_date:=trunc(p_tx_date);

  --正式业务
  --插入新增登记记录
  insert into dw_case_collect(
    caseno,
    regist_type,
    regist_type_desc,
    busi_count,
    additional_flag,
    subsystemid
  )
  select a.受理编号,
    min(a.登记类型),
    max(a.登记类别),
    count(a.业务号),
    max(a.补录标志),
    max(a.subsystemid)
  from zzbdc.bdc_djzb a
  where not exists(
    select 1 from dw_case_collect b where a.受理编号=b.caseno
  )
  group by a.受理编号;
  commit;

  --更新退件标志
  update dw_case_collect a
  set a.refuse_flag='1',
    a.gmt_refuse=(select max(审核结束时间) from zzbdc.bdc_yw_sh b where a.caseno=b.受理编号)
  where exists(select 1 from zzbdc.bdc_djzb c where a.caseno=c.受理编号 and c.权属状态='3')
    and a.refuse_flag='0';
  commit;
  --删卷
  update dw_case_collect a
  set a.refuse_flag='1',
    a.gmt_refuse=nvl((select max(b.starttime) from zzbdc.wfprocess b where a.caseno=b.caseno),(select max(审核结束时间) from zzbdc.bdc_yw_sh c where a.caseno=c.受理编号))
  where not exists(select 1 from zzbdc.bdc_djzb c where a.caseno=c.受理编号)
    and a.refuse_flag='0' and a.fast_flag='0';
  commit;

  --更新登簿标志
  update dw_case_collect a
  set a.record_flag='1'
  where exists(select 1 from zzbdc.bdc_djzb b where a.caseno=b.受理编号 and b.登记簿标志=1)
    and a.record_flag='0';
  commit;

  --更新归档标志
  update dw_case_collect a
  set a.archive_flag='1'
  where exists(select 1 from zzbdc.bdc_djzb b where a.caseno=b.受理编号 and b.归档标志=1)
    and a.archive_flag='0';
  commit;

  --更新收件人信息
  merge into dw_case_collect a
  using(
    select rank() over(partition by p.caseno order by p.starttime) rn,
      p.caseno,
      p.dept,
      p.utuser,
      u.uname
    from zzbdc.wfprocess p
    left join zzbdc.sysuser u on p.utuser=u.id
    where p.atext='收件'
  ) b on (a.caseno=b.caseno and b.rn=1)
  when matched then
    update set a.dept=b.dept,
      a.user_id=b.utuser,
      a.user_name=b.uname
    where a.dept is null;
  commit;
  --更新收件人信息 已归档
  merge into dw_case_collect a
  using(
    select rank() over(partition by p.caseno order by p.starttime) rn,
      p.caseno,
      p.dept,
      p.utuser,
      u.uname
    from zzbdc.wfprocesshistory p
    left join zzbdc.sysuser u on p.utuser=u.id
    where p.atext='收件'
  ) b on (a.caseno=b.caseno and b.rn=1)
  when matched then
    update set a.dept=b.dept,
      a.user_id=b.utuser,
      a.user_name=b.uname
    where a.dept is null;
  commit;
  --更新收件人信息 无过程信息
  merge into dw_case_collect a
  using(
    select 受理编号 caseno,max(受理人员) user_name from zzbdc.bdc_yw_slsq s
    where exists(
      select 1 from dw_case_collect c
      where s.受理编号=c.caseno
        and c.dept is null
        and c.user_name is null
    )
    group by 受理编号
  ) b on (a.caseno=b.caseno)
  when matched then
    update set a.user_name=b.user_name;
  commit;

  merge into dw_case_collect a
  using(
    select rank() over(partition by uname order by ustate desc,dno desc,id desc) rn,
      decode(instr(dno,','),0,dno,substr(dno,1,instr(dno,',')-1)) dept,
      id,
      uname
    from zzbdc.sysuser
  ) b on(a.user_name=b.uname and b.rn=1)
  when matched then
    update set a.dept=b.dept,
      a.user_id=b.id
  where a.dept is null;



  --更新各环节时间
  merge into dw_case_collect a
  using(
    select caseno,
      min(gmt_accept_start) gmt_accept_start,
      max(gmt_accept_end) gmt_accept_end,
      min(gmt_check_start) gmt_check_start,
      max(gmt_check_end) gmt_check_end,
      min(gmt_recheck_start) gmt_recheck_start,
      max(gmt_recheck_end) gmt_recheck_end,
      min(gmt_ratify_start) gmt_ratify_start,
      max(gmt_ratify_end) gmt_ratify_end,
      min(gmt_record_start) gmt_record_start,
      max(gmt_record_end) gmt_record_end,
      min(gmt_make_start) gmt_make_start,
      max(gmt_make_end) gmt_make_end,
      min(gmt_charge_start) gmt_charge_start,
      max(gmt_charge_end) gmt_charge_end,
      min(gmt_send_start) gmt_send_start,
      max(gmt_send_end) gmt_send_end,
      max(gmt_archive_start) gmt_archive_start
    from (
      select caseno,
        decode(atext,'收件',starttime,null) gmt_accept_start,
        decode(atext,'收件',donetime,null) gmt_accept_end,
        decode(atext,'初审',starttime,null) gmt_check_start,
        decode(atext,'初审',donetime,null) gmt_check_end,
        (case when atext='审核' or atext='复审' then starttime else null end) gmt_recheck_start,
        (case when atext='审核' or atext='复审' then donetime else null end) gmt_recheck_end,
        decode(atext,'核定',starttime,null) gmt_ratify_start,
        decode(atext,'核定',donetime,null) gmt_ratify_end,
        (case when atext='记载登记薄' or atext='记载登记簿' then starttime else null end) gmt_record_start,
        (case when atext='记载登记薄' or atext='记载登记簿' then donetime else null end) gmt_record_end,
        decode(atext,'缮证',starttime,null) gmt_make_start,
        decode(atext,'缮证',donetime,null) gmt_make_end,
        decode(atext,'缴费',starttime,null) gmt_charge_start,
        decode(atext,'缴费',donetime,null) gmt_charge_end,
        decode(atext,'发证',starttime,null) gmt_send_start,
        decode(atext,'发证',donetime,null) gmt_send_end,
        decode(atext,'归档',starttime,null) gmt_archive_start,
        decode(atext,'归档',donetime,null) gmt_archive_end
      from zzbdc.wfprocess p
      where exists(
        select 1 from zzbdc.wfprocess p1
        where p.caseno=p1.caseno
          and (trunc(p1.starttime)=v_tx_date or trunc(p1.donetime)=v_tx_date)
      )
    )
    group by caseno
  ) b on (a.caseno=b.caseno)
  when matched then
    update set a.gmt_accept_start=b.gmt_accept_start,
      a.gmt_accept_end=b.gmt_accept_end,
      a.gmt_check_start=b.gmt_check_start,
      a.gmt_check_end=b.gmt_check_end,
      a.gmt_recheck_start=b.gmt_recheck_start,
      a.gmt_recheck_end=b.gmt_recheck_end,
      a.gmt_ratify_start=b.gmt_ratify_start,
      a.gmt_ratify_end=b.gmt_ratify_end,
      a.gmt_record_start=b.gmt_record_start,
      a.gmt_record_end=b.gmt_record_end,
      a.gmt_make_start=b.gmt_make_start,
      a.gmt_make_end=b.gmt_make_end,
      a.gmt_charge_start=b.gmt_charge_start,
      a.gmt_charge_end=b.gmt_charge_end,
      a.gmt_send_start=b.gmt_send_start,
      a.gmt_send_end=b.gmt_send_end,
      a.gmt_archive_start=b.gmt_archive_start;
  commit;

  --更新各环节时间 已归档
  merge into dw_case_collect a
  using(
    select caseno,
      min(gmt_accept_start) gmt_accept_start,
      max(gmt_accept_end) gmt_accept_end,
      min(gmt_check_start) gmt_check_start,
      max(gmt_check_end) gmt_check_end,
      min(gmt_recheck_start) gmt_recheck_start,
      max(gmt_recheck_end) gmt_recheck_end,
      min(gmt_ratify_start) gmt_ratify_start,
      max(gmt_ratify_end) gmt_ratify_end,
      min(gmt_record_start) gmt_record_start,
      max(gmt_record_end) gmt_record_end,
      min(gmt_make_start) gmt_make_start,
      max(gmt_make_end) gmt_make_end,
      min(gmt_charge_start) gmt_charge_start,
      max(gmt_charge_end) gmt_charge_end,
      min(gmt_send_start) gmt_send_start,
      max(gmt_send_end) gmt_send_end,
      max(gmt_archive_start) gmt_archive_start,
      max(gmt_archive_end) gmt_archive_end
    from (
      select caseno,
        decode(atext,'收件',starttime,null) gmt_accept_start,
        decode(atext,'收件',donetime,null) gmt_accept_end,
        decode(atext,'初审',starttime,null) gmt_check_start,
        decode(atext,'初审',donetime,null) gmt_check_end,
        (case when atext='审核' or atext='复审' then starttime else null end) gmt_recheck_start,
        (case when atext='审核' or atext='复审' then donetime else null end) gmt_recheck_end,
        decode(atext,'核定',starttime,null) gmt_ratify_start,
        decode(atext,'核定',donetime,null) gmt_ratify_end,
        (case when atext='记载登记薄' or atext='记载登记簿' then starttime else null end) gmt_record_start,
        (case when atext='记载登记薄' or atext='记载登记簿' then donetime else null end) gmt_record_end,
        decode(atext,'缮证',starttime,null) gmt_make_start,
        decode(atext,'缮证',donetime,null) gmt_make_end,
        decode(atext,'缴费',starttime,null) gmt_charge_start,
        decode(atext,'缴费',donetime,null) gmt_charge_end,
        decode(atext,'发证',starttime,null) gmt_send_start,
        decode(atext,'发证',donetime,null) gmt_send_end,
        decode(atext,'归档',starttime,null) gmt_archive_start,
        decode(atext,'归档',donetime,null) gmt_archive_end
      from zzbdc.wfprocesshistory ph
      where exists(
        select 1 from zzbdc.wfprocesshistory ph1
        where ph.caseno=ph1.caseno
          and (trunc(ph1.starttime)=v_tx_date or trunc(ph1.donetime)=v_tx_date)
      )
    )
    group by caseno
  ) b on (a.caseno=b.caseno)
  when matched then
    update set a.gmt_accept_start=b.gmt_accept_start,
      a.gmt_accept_end=b.gmt_accept_end,
      a.gmt_check_start=b.gmt_check_start,
      a.gmt_check_end=b.gmt_check_end,
      a.gmt_recheck_start=b.gmt_recheck_start,
      a.gmt_recheck_end=b.gmt_recheck_end,
      a.gmt_ratify_start=b.gmt_ratify_start,
      a.gmt_ratify_end=b.gmt_ratify_end,
      a.gmt_record_start=b.gmt_record_start,
      a.gmt_record_end=b.gmt_record_end,
      a.gmt_make_start=b.gmt_make_start,
      a.gmt_make_end=b.gmt_make_end,
      a.gmt_charge_start=b.gmt_charge_start,
      a.gmt_charge_end=b.gmt_charge_end,
      a.gmt_send_start=b.gmt_send_start,
      a.gmt_send_end=b.gmt_send_end,
      a.gmt_archive_start=b.gmt_archive_start,
      a.gmt_archive_end=b.gmt_archive_end;
  commit;

  --更新缮证数量
  merge into dw_case_collect a
  using(
    select z.受理编号 caseno,max(z.缮证人员) user_make,
      sum(decode(instr(nvl(z.缮证证号, z.不动产权证号), '证明'), 0, 1, 0)) c1,--证书
      sum(decode(instr(nvl(z.缮证证号, z.不动产权证号), '证明'), 0, 0, 1)) c2--证明
    from (
      select distinct x.受理编号,x.不动产权证号,y.缮证人员,y.缮证证号,y.印刷序列号
      from zzbdc.bdc_djzb x,zzbdc.bdc_yw_sz y
      where x.业务号=y.业务号
        and y.印刷序列号 is not null
        and trunc(y.缮证时间)=v_tx_date
    ) z
    group by z.受理编号
  ) b on (a.caseno=b.caseno)
  when matched then
    update set a.user_make=b.user_make,
           a.count_make_certificate=b.c1,
           a.count_make_certification=b.c2;

  --更新缴费金额
  merge into dw_case_collect a
  using(
    select s1.受理编号 caseno,
      nvl(sum(case when s2.sfno in('SFBH-3','SFBH-5','SFBH-7','SFBH-10') then nvl(s1.实收额,s1.应收额-nvl(s1.减免额,0)) else 0 end),0) charge_redidence,
      nvl(sum(case when s2.sfno in('SFBH-4','SFBH-6','SFBH-8','SFBH-9','SFBH-11') then nvl(s1.实收额,s1.应收额-nvl(s1.减免额,0)) else 0 end),0) charge_not_redidence,
      nvl(sum(case when s2.sfno in('SFBH-1','SFBH-2') then nvl(s1.实收额,s1.应收额-nvl(s1.减免额,0)) else 0 end),0) charge_production,
      nvl(sum(nvl(s1.实收额,s1.应收额-nvl(s1.减免额,0))),0) charge
    from zzbdc.bdc_sfx s1,zzbdc.bdc_sf_config s2
    where s1.收费项名称=s2.sfname
      and exists(
        select 1 from zzbdc.wfprocesshistory ph
        where s1.受理编号=ph.caseno
        and ph.atext='缴费'
        and trunc(ph.donetime)=v_tx_date
      )
    group by s1.受理编号
  ) b on(a.caseno=b.caseno)
  when matched then
    update set a.charge=b.charge,
      a.charge_production=b.charge_production,
      a.charge_redidence=b.charge_redidence,
      a.charge_not_redidence=b.charge_not_redidence;

  merge into dw_case_collect a
  using(
    select s1.受理编号 caseno,
      nvl(sum(case when s2.sfno in('SFBH-3','SFBH-5','SFBH-7','SFBH-10') then nvl(s1.实收额,s1.应收额-nvl(s1.减免额,0)) else 0 end),0) charge_redidence,
      nvl(sum(case when s2.sfno in('SFBH-4','SFBH-6','SFBH-8','SFBH-9','SFBH-11') then nvl(s1.实收额,s1.应收额-nvl(s1.减免额,0)) else 0 end),0) charge_not_redidence,
      nvl(sum(case when s2.sfno in('SFBH-1','SFBH-2') then nvl(s1.实收额,s1.应收额-nvl(s1.减免额,0)) else 0 end),0) charge_production,
      nvl(sum(nvl(s1.实收额,s1.应收额-nvl(s1.减免额,0))),0) charge
    from zzbdc.bdc_sfx s1,zzbdc.bdc_sf_config s2
    where s1.收费项名称=s2.sfname
      and exists(
        select 1 from zzbdc.wfprocess p
        where s1.受理编号=p.caseno
        and p.atext in('发证','归档')
        and p.direction=1
        and trunc(p.starttime)=v_tx_date
      )
    group by s1.受理编号
  ) b on(a.caseno=b.caseno)
  when matched then
    update set a.charge=b.charge,
      a.charge_production=b.charge_production,
      a.charge_redidence=b.charge_redidence,
      a.charge_not_redidence=b.charge_not_redidence;
  commit;

  --更新登簿人
  merge into dw_case_collect a
  using(
    select rank() over(partition by p.caseno order by p.donetime desc,p.pid desc) rn,
      p.caseno,
      p.dept,
      p.utuser,
      u.uname
    from zzbdc.wfprocess p
    left join zzbdc.sysuser u on p.utuser=u.id
    where p.atext in('记载登记簿','记载登记薄') and donetime is not null
  ) b on(a.caseno=b.caseno and b.rn=1)
  when matched then
    update set a.user_record=nvl(b.uname,b.utuser),a.record_flag='1'
    where a.user_record is null and (a.record_flag='1'
      or a.count_make_certificate>0
      or a.count_make_certificate>0
      or a.charge>0
      or a.gmt_send_start is not null
      or a.gmt_archive_start is not null);

  --更新登簿人
  merge into dw_case_collect a
  using(
    select rank() over(partition by p.caseno order by p.donetime desc,p.pid desc) rn,
      p.caseno,
      p.dept,
      p.utuser,
      u.uname
    from zzbdc.wfprocesshistory p
    left join zzbdc.sysuser u on p.utuser=u.id
    where p.atext in('记载登记簿','记载登记薄') and donetime is not null
  ) b on(a.caseno=b.caseno and b.rn=1)
  when matched then
    update set a.user_record=nvl(b.uname,b.utuser),a.record_flag='1'
    where a.user_record is null and (a.record_flag='1'
      or a.count_make_certificate>0
      or a.count_make_certificate>0
      or a.charge>0
      or a.gmt_send_start is not null
      or a.gmt_archive_start is not null);

  --快速业务
  --插入新增快速业务
  insert into dw_case_collect(
    caseno,
    regist_type,
    regist_type_desc,
    busi_count,
    user_name,
    gmt_accept_start,
    additional_flag,
    subsystemid,
    fast_flag
  )
  select distinct a.qicguid,
    '0102',
    '快速转移',
    1,
    a.受理人员,
    a.受理日期,
    decode(a.补录标志,'1','1','0'),
    a.subsystemid,
    '1'
  from zzbdc.bdc_kszy a
  where not exists(
    select 1 from dw_case_collect b where a.qicguid=b.caseno
  )
  union all
  select distinct a.qicguid,
    decode(instr(a.抵押类型,'注销'),0,'0201','0204'),
    decode(instr(抵押类型,'注销'),0,'快速抵押','快速抵押注销'),
    1,
    a.受理人员,
    a.受理日期,
    decode(a.补录标志,1,'1','0'),
    to_number(a.subsystemid),
    '1'
  from zzbdc.bdc_ksdy a
  where not exists(
    select 1 from dw_case_collect b where a.qicguid=b.caseno
  )
  union all
  select distinct to_char(id0),
    decode(instr(a.查封类型,'解查封'),0,'0404','0401'),
    decode(instr(查封类型,'解查封'),0,'快速解查封','快速查封'),
    1,
    a.受理人员,
    a.受理日期,
    decode(a.补录标志,'1','1','0'),
    to_number(a.subsystemid),
    '1'
  from zzbdc.bdc_zz_cfdj a
  where not exists(
    select 1 from dw_case_collect b where to_char(a.id0)=b.caseno
  );
  commit;
  --更新机构和用户id
  merge into dw_case_collect a
  using(
    select rank() over(partition by uname order by ustate desc,dno desc,id desc) rn,
      decode(instr(dno,','),0,dno,substr(dno,1,instr(dno,',')-1)) dept,
      id,
      uname
    from zzbdc.sysuser
  ) b on(a.user_name=b.uname and b.rn=1)
  when matched then
    update set a.dept=b.dept,
      a.user_id=b.id
  where a.fast_flag='1'
    and a.dept is null;
  commit;

  --更新登簿时间
  merge into dw_case_collect a
  using(
    select distinct qicguid caseno,
      登簿时间 gmt_record,登簿人 user_record
    from zzbdc.bdc_kszy a
    where trunc(登簿时间)=v_tx_date
    union all
    select distinct qicguid,
      登簿时间,登簿人
    from zzbdc.bdc_ksdy
    where trunc(登簿时间)=v_tx_date
    union all
    select distinct to_char(id0),
      登簿时间,登簿人
    from zzbdc.bdc_zz_cfdj a
    where trunc(登簿时间)=v_tx_date
  ) b on (a.caseno=b.caseno)
  when matched then
    update set a.gmt_record_end=b.gmt_record,
      a.user_record=b.user_record,
      a.record_flag='1';
  commit;
  --处理异常数据
  merge into dw_case_collect a
  using(
    select distinct qicguid caseno,登簿时间 gmt_record,登簿人 user_record
    from zzbdc.bdc_kszy a
    where exists(select 1 from dw_case_collect c where a.qicguid=c.caseno and c.record_flag='0')
      and a.权属状态 in('1','2')
    union all
    select distinct qicguid,
      登簿时间,登簿人
    from zzbdc.bdc_ksdy a
    where exists(select 1 from dw_case_collect c where a.qicguid=c.caseno and c.record_flag='0')
      and a.权属状态 in('1','2','4')
    union all
    select distinct to_char(id0),登簿时间,登簿人
    from zzbdc.bdc_zz_cfdj a
    where exists(select 1 from dw_case_collect c where to_char(a.id0)=c.caseno and c.record_flag='0')
      and a.权属状态!='0'
  ) b on (a.caseno=b.caseno)
  when matched then
    update set a.gmt_record_end=nvl(b.gmt_record,a.gmt_accept_start),
      a.user_record=b.user_record,
      a.record_flag='1';
  commit;

  --更新缮证时间
  merge into dw_case_collect a
  using(
    select distinct qicguid caseno,
      nvl(缮证时间,登簿时间) gmt_make,
      1 c1,
      0 c2,
      缮证人 user_make
    from zzbdc.bdc_kszy a
    where 是否出证='1'
      and 印刷序列号 is not null
      and trunc(nvl(缮证时间,登簿时间))=v_tx_date
    union all
    select distinct qicguid,
      nvl(缮证时间,登簿时间),
      0,
      1,
      缮证人
    from zzbdc.bdc_ksdy
    where 是否出证='1'
      and 印刷序列号 is not null
      and trunc(nvl(缮证时间,登簿时间))=v_tx_date
  ) b on (a.caseno=b.caseno)
  when matched then
    update set a.gmt_make_end=b.gmt_make,
      a.user_make=b.user_make,
      a.count_make_certificate=b.c1,
      a.count_make_certification=b.c2;
  commit;

  --更新缴费
  merge into dw_case_collect a
  using(
    select qicguid caseno,
      受理日期 gmt_charge,
      缴费金额 charge
    from zzbdc.bdc_kssf
    where trunc(受理日期)=v_tx_date
  ) b on (a.caseno=b.caseno)
  when matched then
    update set a.gmt_charge_end=b.gmt_charge,
      a.charge=b.charge;
  commit;

  --更新快速业务相关状态
  update dw_case_collect a
  set refuse_flag='1',
    gmt_refuse=nvl(gmt_make_end,nvl(gmt_record_end,gmt_accept_start))
  where additional_flag='0'
    and refuse_flag='0'
    and fast_flag='1'
    and (
      exists(select 1 from zzbdc.bdc_kszy b where a.caseno=b.qicguid and b.权属状态='3')
      or exists(select 1 from zzbdc.bdc_ksdy b where a.caseno=b.qicguid and b.权属状态='3')
    );
  commit;
  update dw_case_collect a
  set refuse_flag='1',
    gmt_refuse=nvl(gmt_make_end,nvl(gmt_record_end,gmt_accept_start))
  where additional_flag='0'
    and refuse_flag='0'
    and fast_flag='1'
    and substr(regist_type,1,2)='01'
    and not exists(select 1 from zzbdc.bdc_kszy b where a.caseno=b.qicguid);
  commit;
   update dw_case_collect a
  set refuse_flag='1',
    gmt_refuse=nvl(gmt_make_end,nvl(gmt_record_end,gmt_accept_start))
  where additional_flag='0'
    and refuse_flag='0'
    and fast_flag='1'
    and substr(regist_type,1,2)='02'
    and not exists(select 1 from zzbdc.bdc_ksdy b where a.caseno=b.qicguid);
  commit;
   update dw_case_collect a
  set refuse_flag='1',
    gmt_refuse=nvl(gmt_make_end,nvl(gmt_record_end,gmt_accept_start))
  where additional_flag='0'
    and refuse_flag='0'
    and fast_flag='1'
    and substr(regist_type,1,2)='04'
    and not exists(select 1 from zzbdc.bdc_zz_cfdj b where a.caseno=b.id0);
  commit;

  --更新异常标志 没有收件人、收件人已删除、无过程记录
  update dw_case_collect
  set exception_flag=1
  where dept is null
    and additional_flag='0'
    and regist_type!='0701';--权籍调查 测绘的不算
  commit;
  --快速件受理时间为空
  update dw_case_collect
  set exception_flag=1
  where gmt_accept_start is null
    and refuse_flag='0'
    and additional_flag='0'
    and fast_flag='1';
  commit;

  --更新案卷耗时
  --处理历史
  merge into dw_case_collect a
  using(
    select a.caseno,case
      when a.record_flag='0' then v_tx_date
      when a.record_flag='1' and ((fast_flag='0' and a.gmt_make_start is null) or (fast_flag='1' and regist_type in('0204','0401','0404'))) then a.gmt_record_end
      when a.gmt_make_end is not null then a.gmt_make_end
      else v_tx_date end end_time,
      trunc(a.gmt_accept_start) start_date,
    count(1) days
    from dw_case_collect a
    left join zzbdc.wfholiday b on
      b.hday between a.gmt_accept_start and case
      when a.record_flag='0' then v_tx_date
      when a.record_flag='1' and ((fast_flag='0' and a.gmt_make_start is null) or (fast_flag='1' and regist_type in('0204','0401','0404'))) then a.gmt_record_end
      when a.gmt_make_end is not null then a.gmt_make_end
      else v_tx_date end
  where a.cost_time is null and gmt_accept_start is not null and a.regist_type!='0701' and a.refuse_flag='0' and a.additional_flag='0'
  group by a.caseno,a.regist_type,a.record_flag,a.fast_flag,a.gmt_accept_start,a.gmt_make_start,a.gmt_record_end,a.gmt_make_end
  ) b on(a.caseno=b.caseno)
  when matched then
    update set a.cost_time=decode(b.start_date, trunc(b.end_time),
      decode(b.end_time,v_tx_date,
        b.end_time-b.start_date,
        b.end_time-a.gmt_accept_start),
      decode(b.end_time,v_tx_date,
        b.end_time-b.start_date-b.days,
        b.end_time-b.start_date-b.days-1));
  commit;

  --未办结
  update dw_case_collect
    set cost_time=v_tx_date-trunc(gmt_accept_start)-(select count(1) from zzbdc.wfholiday where hday>gmt_accept_start and hday<=v_tx_date)
  where regist_type!='0701' and record_flag='0' and additional_flag='0';
  commit;
  --不发证办结
  update dw_case_collect set cost_time=decode(
    trunc(gmt_record_end), trunc(gmt_accept_start), gmt_record_end-gmt_accept_start,
    gmt_record_end-trunc(gmt_accept_start)-1-(select count(1) from zzbdc.wfholiday where hday>gmt_accept_start and hday<gmt_record_end))
  where ((fast_flag='0' and gmt_make_start is null) or (fast_flag='1' and regist_type in('0204','0401','0404'))) and trunc(gmt_record_end)=v_tx_date and record_flag='1' and additional_flag='0';
  commit;
  --发证已办结
  update dw_case_collect set cost_time=gmt_make_end-trunc(gmt_accept_start)-1-(select count(1) from zzbdc.wfholiday where hday>gmt_accept_start and hday<gmt_make_end)
  where gmt_make_start is not null and trunc(gmt_make_end)=v_tx_date and record_flag='1' and additional_flag='0';
  --发证未办结
  update dw_case_collect set cost_time=v_tx_date-trunc(gmt_accept_start)-(select count(1) from zzbdc.wfholiday where hday>gmt_accept_start and hday<v_tx_date)
  where (gmt_make_start is not null or regist_type in('0102','0201')) and gmt_make_end is null and record_flag='1' and additional_flag='0';
  commit;
  
  --实时预警超期
  merge into dw_case_warn a
  using(
  select a.caseno,a.dept,nvl(b.atext,'收件') atext,b.uname,a.cost_time,d.房屋坐落||e.产权坐落||f.产权坐落 location,
    case when a.regist_type in ('0204','0504','0604') then 1 when a.regist_type in ('0201','0221','0304') then 5 when a.regist_type in ('0102','0305') then 7 else 20 end promise_time
  from dw_case_collect a left join (
    select caseno,trim(atext) atext,u.uname,row_number() over(partition by caseno order by pid desc) rn from zzbdc.wfprocess p
    left join zzbdc.sysuser u on p.utuser=u.id
  ) b on a.caseno=b.caseno and b.rn=1
    left join zzbdc.bdc_ksdy d on a.caseno=d.qicguid
    left join zzbdc.bdc_kszy e on a.caseno=e.qicguid
    left join zzbdc.bdc_zz_cfdj f on a.caseno=to_char(f.id0)
  where (a.record_flag='0' or (a.record_flag='1' and a.gmt_make_start is not null and a.gmt_make_end is null) or ( a.regist_type in('0102','0201') and a.gmt_make_end is null))
    and case when a.regist_type in ('0204','0504','0604') then 1 when a.regist_type in ('0201','0221','0304') then 5 when a.regist_type in ('0102','0305') then 7 else 20 end-a.cost_time<=case when a.regist_type in ('0204','0504','0604') then 0 else 1 end
    and a.gmt_accept_start>to_date('20180101','yyyymmdd')
    and a.refuse_flag='0'
  ) b on(a.caseno=b.caseno)
  when matched then
   update set a.atext=b.atext,a.user_name=b.uname,a.cost_time=b.cost_time,a.state=decode(b.promise_time-b.cost_time,1,1,2)
  when not matched then
    insert(caseno,
      location,
      dept,
      atext,
      user_name,
      promise_time,
      cost_time,
      state
    ) values(b.caseno,
      decode(b.location,null,null,'/'||b.location),
      b.dept,
      b.atext,
      b.uname,
      b.promise_time,
      b.cost_time,
      decode(b.promise_time-b.cost_time,1,1,2)
    );
  commit;
  delete from dw_case_warn a where exists(select 1 from dw_case_collect b where a.caseno=b.caseno and b.refuse_flag='1');
  delete from dw_case_warn a
    where exists(select 1 from dw_case_collect b where a.caseno=b.caseno and b.regist_type='0304')
      and exists(select 1 from zzbdc.bdc_yw_sz c where a.caseno=c.受理编号 and c.印刷序列号 is not null and c.缮证时间 is not null);

  UPDATE DW_CASE_WARN a SET busi_type='1' WHERE a.busi_type IS NULL and exists(SELECT 1 FROM DW_CASE_COLLECT b WHERE a.CASENO=b.caseno AND b.fast_flag='0');
  UPDATE DW_CASE_WARN a SET busi_type='2' WHERE a.busi_type IS NULL and exists(SELECT 1 FROM zzbdc.BDC_ZZ_CFDJ b WHERE a.CASENO=to_char(b.id0));
  UPDATE DW_CASE_WARN a SET busi_type='3' WHERE a.busi_type IS NULL and exists(SELECT 1 FROM zzbdc.BDC_KSZY b WHERE a.CASENO=b.QICGUID);
  UPDATE DW_CASE_WARN a SET busi_type='4' WHERE a.busi_type IS NULL and exists(SELECT 1 FROM zzbdc.BDC_KSDY b WHERE a.CASENO=b.QICGUID);

  --更新所有超期标志
  update dw_case_collect
    set exceed_flag='1'
  where exceed_flag='0'
    and additional_flag='0'
    and cost_time>=20;
  commit;
  update dw_case_collect
    set exceed_flag='1'
  where exceed_flag='0'
    and additional_flag='0'
    and regist_type in('0201','0221','0304') --抵押 联合抵押 预告抵押 5天
    and cost_time>=5;
  commit;
  update dw_case_collect
    set exceed_flag='1'
  where exceed_flag='0'
    and additional_flag='0'
    and regist_type in('0102','0305') --转移 转移抵押联合办理 预告注销 7天
    and cost_time>=7;
  commit;
  update dw_case_collect
    set exceed_flag='1'
  where exceed_flag='0'
    and additional_flag='0'
    and regist_type in('0204','0504','0604') --抵押注销 地役权注销登记 异议注销  1天
    and cost_time>=1;
  commit;

  --更新收件部门为1-3，改为1-3-2
  update dw_case_collect set dept='1-3-2'
  where dept='1-3';
  commit;

  --更新登记类型列表
  delete from stats_regist_type
  where group_name='noclass';

  insert into stats_regist_type
  select rownum,'noclass',null,regist_type,regist_type_desc,null from(
    select distinct regist_type,regist_type_desc
    from dw_case_collect
    where additional_flag='0'
    order by regist_type,regist_type_desc
  );
  commit;

  --执行完毕
  o_status:=1;

  exception
  when others then
    rollback;
    --发生异常，同样需要清空表，以防止无法修改临时表表结构
    o_status:=99;
    o_info:=SQLCODE||'---'||SQLERRM||'---'||dbms_utility.format_error_backtrace;
end;

汇总每天的数据

create or replace procedure PDW_CASE_DEAL_DAY(p_tx_date date,
                                             o_status  out  integer,
                                             o_info    out  varchar2)
/**************************************************
  * 作    者：XXX
    日    期：2017-11-8
    功    能：
        汇集每日不同部门不同业务受理业务量
    算    法：
        1.按日期、部门、业务类型汇总
        2.补录、异常和权籍调查数据不会计入
        
    源    表：
        dw_case_collect
    临 时 表：
        tmp_case_deal_day
    目 标 表：
        dw_case_deal_day
    修改历史：
    时间         内容                               修改人
   ------------ ----------------------------------- --------
   2014-3-3     添加注释说明                        XXX
  **************************************************/
is
  v_tx_date date;
  v_date_id varchar2(8);
begin
  o_status:=0;
  v_tx_date:=trunc(p_tx_date);
  v_date_id:=to_char(v_tx_date,'yyyymmdd');
  
  --清空临时表
  execute immediate 'truncate table tmp_case_deal_day';

  --插入收件数据
  insert into tmp_case_deal_day(
    date_id,
    dept,
    regist_type,
    regist_type_desc,
    count_accept_case,
    count_accept_busi
  )
  select v_date_id,
    dept,
    regist_type,
    regist_type_desc,
    count(caseno),
    sum(busi_count)
  from dw_case_collect
  where trunc(gmt_accept_start)=v_tx_date
    and additional_flag='0'
    and exception_flag='0'
    and regist_type!='0701'
  group by dept,regist_type,regist_type_desc;
  commit;
  
  --更新初审
  merge into tmp_case_deal_day a
  using(
    select v_date_id date_id,
      dept,
      regist_type,
      regist_type_desc,
      count(caseno) case_count,
      sum(busi_count) busi_count
    from dw_case_collect
    where trunc(gmt_check_start)=v_tx_date
      and additional_flag='0'
      and exception_flag='0'
      and regist_type!='0701'
    group by dept,regist_type,regist_type_desc
  ) b on (a.date_id=b.date_id
    and a.dept=b.dept
    and a.regist_type=b.regist_type
    and a.regist_type_desc=b.regist_type_desc
  )
  when matched then
    update set a.count_check_case=b.case_count,
      a.count_check_busi=b.busi_count
  when not matched then
    insert (
      date_id,
      dept,
      regist_type,
      regist_type_desc,
      count_check_case,
      count_check_busi
    ) values(
      b.date_id,
      b.dept,
      b.regist_type,
      b.regist_type_desc,
      b.case_count,
      b.busi_count
    );
  commit;
  
  --更新复审
  merge into tmp_case_deal_day a
  using(
    select v_date_id date_id,
      dept,
      regist_type,
      regist_type_desc,
      count(caseno) case_count,
      sum(busi_count) busi_count
    from dw_case_collect
    where trunc(gmt_recheck_start)=v_tx_date
      and additional_flag='0'
      and exception_flag='0'
      and regist_type!='0701'
    group by dept,regist_type,regist_type_desc
  ) b on (a.date_id=b.date_id
    and a.dept=b.dept
    and a.regist_type=b.regist_type
    and a.regist_type_desc=b.regist_type_desc
  )
  when matched then
    update set a.count_recheck_case=b.case_count,
      a.count_recheck_busi=b.busi_count
  when not matched then
    insert (
      date_id,
      dept,
      regist_type,
      regist_type_desc,
      count_recheck_case,
      count_recheck_busi
    ) values(
      b.date_id,
      b.dept,
      b.regist_type,
      b.regist_type_desc,
      b.case_count,
      b.busi_count
    );
  commit;
  
  --更新核定
  merge into tmp_case_deal_day a
  using(
    select v_date_id date_id,
      dept,
      regist_type,
      regist_type_desc,
      count(caseno) case_count,
      sum(busi_count) busi_count
    from dw_case_collect
    where trunc(gmt_ratify_start)=v_tx_date
      and additional_flag='0'
      and exception_flag='0'
      and regist_type!='0701'
    group by dept,regist_type,regist_type_desc
  ) b on (a.date_id=b.date_id
    and a.dept=b.dept
    and a.regist_type=b.regist_type
    and a.regist_type_desc=b.regist_type_desc
  )
  when matched then
    update set a.count_ratify_case=b.case_count,
      a.count_ratify_busi=b.busi_count
  when not matched then
    insert (
      date_id,
      dept,
      regist_type,
      regist_type_desc,
      count_ratify_case,
      count_ratify_busi
    ) values(
      b.date_id,
      b.dept,
      b.regist_type,
      b.regist_type_desc,
      b.case_count,
      b.busi_count
    );
  commit;
  
  --更新登簿
  merge into tmp_case_deal_day a
  using(
    select v_date_id date_id,
      dept,
      regist_type,
      regist_type_desc,
      count(caseno) case_count,
      sum(busi_count) busi_count
    from dw_case_collect
    where trunc(gmt_record_end)=v_tx_date
      and additional_flag='0'
      and exception_flag='0'
      and record_flag='1'
      and regist_type!='0701'
    group by dept,regist_type,regist_type_desc
  ) b on (a.date_id=b.date_id
    and a.dept=b.dept
    and a.regist_type=b.regist_type
    and a.regist_type_desc=b.regist_type_desc
  )
  when matched then
    update set a.count_record_case=b.case_count,
      a.count_record_busi=b.busi_count
  when not matched then
    insert (
      date_id,
      dept,
      regist_type,
      regist_type_desc,
      count_record_case,
      count_record_busi
    ) values(
      b.date_id,
      b.dept,
      b.regist_type,
      b.regist_type_desc,
      b.case_count,
      b.busi_count
    );
  commit;
  
  --更新缮证 以截止时间计入当天
  merge into tmp_case_deal_day a
  using(
    select v_date_id date_id,
      dept,
      regist_type,
      regist_type_desc,
      count(caseno) case_count,
      sum(busi_count) busi_count,
      sum(count_make_certificate) certificate_count,
      sum(count_make_certification) certification_count
    from dw_case_collect
    where trunc(gmt_make_end)=v_tx_date
      and additional_flag='0'
      and exception_flag='0'
      and regist_type!='0701'
      and (count_make_certificate!=0 or count_make_certification!=0)
    group by dept,regist_type,regist_type_desc
  ) b on (a.date_id=b.date_id
    and a.dept=b.dept
    and a.regist_type=b.regist_type
    and a.regist_type_desc=b.regist_type_desc
  )
  when matched then
    update set a.count_make_case=b.case_count,
      a.count_make_busi=b.busi_count,
      a.count_make_certificate=b.certificate_count,
      a.count_make_certification=b.certification_count
  when not matched then
    insert (
      date_id,
      dept,
      regist_type,
      regist_type_desc,
      count_make_case,
      count_make_busi,
      count_make_certificate,
      count_make_certification
    ) values(
      b.date_id,
      b.dept,
      b.regist_type,
      b.regist_type_desc,
      b.case_count,
      b.busi_count,
      b.certificate_count,
      b.certification_count
    );
  commit;
  
  
  --更新缴费 以截止时间计入当天
  merge into tmp_case_deal_day a
  using(
    select v_date_id date_id,
      dept,
      regist_type,
      regist_type_desc,
      count(caseno) case_count,
      sum(busi_count) busi_count,
      sum(charge) charge,
      sum(charge_production) charge_production,
      sum(charge_redidence) charge_redidence,
      sum(charge_not_redidence) charge_not_redidence
    from dw_case_collect
    where trunc(gmt_charge_end)=v_tx_date
      and additional_flag='0'
      and exception_flag='0'
      and regist_type!='0701'
      and charge!=0
    group by dept,regist_type,regist_type_desc
  ) b on (a.date_id=b.date_id
    and a.dept=b.dept
    and a.regist_type=b.regist_type
    and a.regist_type_desc=b.regist_type_desc
  )
  when matched then
    update set a.count_charge_case=b.case_count,
           a.count_charge_busi=b.busi_count,
           a.charge=b.charge,
           a.charge_production=b.charge_production,
           a.charge_redidence=b.charge_redidence,
           a.charge_not_redidence=b.charge_not_redidence
  when not matched then
    insert (
      date_id,
      dept,
      regist_type,
      regist_type_desc,
      count_charge_case,
      count_charge_busi,
      charge,
      charge_production,
      charge_redidence,
      charge_not_redidence
    ) values(
      b.date_id,
      b.dept,
      b.regist_type,
      b.regist_type_desc,
      b.case_count,
      b.busi_count,
      b.charge,
      b.charge_production,
      b.charge_redidence,
      b.charge_not_redidence
    );
  commit;
  
  --更新发证 存在已发证但没有移交归档情况
  merge into tmp_case_deal_day a
  using(
    select v_date_id date_id,
           dept,
           regist_type,
           regist_type_desc,
           count(caseno) case_count,
           sum(busi_count) busi_count
    from dw_case_collect c
    where trunc(gmt_send_end)=v_tx_date
      and additional_flag='0'
      and exception_flag='0'
      and regist_type!='0701'
      and exists(select 1 from zzbdc.bdc_yw_fz z where c.caseno=z.受理编号 and trunc(z.发证时间)<=v_tx_date)
    group by dept,regist_type,regist_type_desc
  ) b on (a.date_id=b.date_id
    and a.dept=b.dept
    and a.regist_type=b.regist_type
    and a.regist_type_desc=b.regist_type_desc
  )
  when matched then
    update set a.count_send_case=b.case_count,
      a.count_send_busi=b.busi_count
  when not matched then
    insert (
      date_id,
      dept,
      regist_type,
      regist_type_desc,
      count_send_case,
      count_send_busi
    ) values(
      b.date_id,
      b.dept,
      b.regist_type,
      b.regist_type_desc,
      b.case_count,
      b.busi_count
    );
  commit;
  
  --更新归档
  merge into tmp_case_deal_day a
  using(
    select v_date_id date_id,
      dept,
      regist_type,
      regist_type_desc,
      count(caseno) case_count,
      sum(busi_count) busi_count
    from dw_case_collect
    where trunc(gmt_archive_end)=v_tx_date
      and additional_flag='0'
      and exception_flag='0'
      and archive_flag='1'
      and regist_type!='0701'
    group by dept,regist_type,regist_type_desc
  ) b on (a.date_id=b.date_id
    and a.dept=b.dept
    and a.regist_type=b.regist_type
    and a.regist_type_desc=b.regist_type_desc
  )
  when matched then
  update set a.count_archive_case=b.case_count,
         a.count_archive_busi=b.busi_count
  when not matched then
    insert (
      date_id,
      dept,
      regist_type,
      regist_type_desc,
      count_archive_case,
      count_archive_busi
    ) values(
      b.date_id,
      b.dept,
      b.regist_type,
      b.regist_type_desc,
      b.case_count,
      b.busi_count
    );
  commit;
  
  --更新退件
  merge into tmp_case_deal_day a
  using(
    select v_date_id date_id,
      dept,
      regist_type,
      regist_type_desc,
      count(caseno) case_count,
      sum(busi_count) busi_count
    from dw_case_collect
    where trunc(gmt_refuse)=v_tx_date
      and additional_flag='0'
      and exception_flag='0'
      and regist_type!='0701'
    group by dept,regist_type,regist_type_desc
  ) b on (a.date_id=b.date_id
    and a.dept=b.dept
    and a.regist_type=b.regist_type
    and a.regist_type_desc=b.regist_type_desc
  )
  when matched then
    update set a.count_refuse_case=b.case_count,
           a.count_refuse_busi=b.busi_count
  when not matched then
    insert (
      date_id,
      dept,
      regist_type,
      regist_type_desc,
      count_refuse_case,
      count_refuse_busi
    ) values(
      b.date_id,
      b.dept,
      b.regist_type,
      b.regist_type_desc,
      b.case_count,
      b.busi_count
    );
  commit;
  
  --更新超期
  merge into tmp_case_deal_day a
  using(
    select v_date_id date_id,
      dept,
      regist_type,
      regist_type_desc,
      count(caseno) case_count,
      sum(busi_count) busi_count
    from dw_case_collect a
    where case when trunc(gmt_record_end)=v_tx_date and ((fast_flag='0' and gmt_make_start is null) or (fast_flag='1' and regist_type in('0102','0201'))) then 1
      when trunc(gmt_make_end)=v_tx_date and(fast_flag='1' or exists(select 1 from zzbdc.wfprocess p where a.caseno=p.caseno and p.atext in('缴费','发证','归档') and p.flag=1)) then 1 else 0 end=1
      and exceed_flag='1'
      and exception_flag='0'
      and record_flag='1'
      and regist_type!='0701'
    group by dept,regist_type,regist_type_desc
  ) b on (a.date_id=b.date_id
    and a.dept=b.dept
    and a.regist_type=b.regist_type
    and a.regist_type_desc=b.regist_type_desc
  )
  when matched then
    update set a.count_exceed_case=b.case_count,
           a.count_exceed_busi=b.busi_count
  when not matched then
    insert (
      date_id,
      dept,
      regist_type,
      regist_type_desc,
      count_exceed_case,
      count_exceed_busi
    ) values(
      b.date_id,
      b.dept,
      b.regist_type,
      b.regist_type_desc,
      b.case_count,
      b.busi_count
    );
  commit;
  
  --更改目标表
  delete from dw_case_deal_day
  where date_id=v_date_id;

  insert into dw_case_deal_day
  select date_id,
    dept,
    regist_type,
    regist_type_desc,
    count_accept_case,
    count_accept_busi,
    count_check_case,
    count_check_busi,
    count_recheck_case,
    count_recheck_busi,
    count_ratify_case,
    count_ratify_busi,
    count_record_case,
    count_record_busi,
    count_make_case,
    count_make_busi,
    count_charge_case,
    count_charge_busi,
    charge,
    charge_production,
    charge_redidence,
    charge_not_redidence,
    count_send_case,
    count_send_busi,
    count_archive_case,
    count_archive_busi,
    count_refuse_case,
    count_refuse_busi,
    count_make_certificate,
    count_make_certification,
    count_exceed_case,
    count_exceed_busi
  from tmp_case_deal_day;
  commit;
  
  --执行完毕
  o_status:=1;
  exception
  when others then
    rollback;
    --发生异常
    o_status:=99;
    o_info:=SQLCODE||'---'||SQLERRM||'---'||dbms_utility.format_error_backtrace; 
end PDW_CASE_DEAL_DAY;

 循环树结构

create or replace procedure pstats_dept is
begin
  execute immediate 'truncate table stats_dept';
  insert into stats_dept
  select a.dno,b.dno,a.did,a.dname,a.部门简称,a.部门排序,'0'
  from zzbdc.sysdept a
  left join zzbdc.sysdept b on a.dpid=b.did;
  commit;
  update stats_dept set short_name='市中心' where short_name='市局';
  update stats_dept set short_name='东区合署',order_id=10
  where dname='东区合署办公';
  update stats_dept set short_name='北区合署',order_id=11
  where dname='北区合署办公';
  update stats_dept set short_name='西区合署',order_id=12
  where dname='西区合署办公';
  update stats_dept set short_name='新郑市' where dname='新郑市不动产登记中心';
  update stats_dept set short_name='登封市' where dname='登封市不动产登记中心';
  update stats_dept set short_name='中牟县' where dname='中牟县不动产登记中心';
  update stats_dept set short_name='荥阳市' where dname='荥阳市不动产登记中心';
  update stats_dept set short_name='上街区' where dname='上街区不动产登记中心';
  update stats_dept set short_name='新密市' where dname='新密市不动产登记中心';
  update stats_dept set short_name='航空港区' where dname='郑州航空港区不动产登记中心';
  update stats_dept set short_name='托管区' where dname='托管区不动产登记中心';
  commit;

  for v in(select did,dno from zzbdc.sysdept) loop
    insert into stats_dept
    select dno,v.dno,did,dname,null,null,'1'
    from zzbdc.sysdept
    connect by prior did=dpid
    start with did=v.did;
    commit;
  end loop;

end pstats_dept;