orcl一些操作记录.docxVIP

create or replace procedure MG_RISKCONMSG(WhereDate varchar2) is--风险部 生效合同在各月逾期情况Jieqdate varchar2(8);Montdate varchar2(8);Moendate varchar2(8);begindeclare j number; i int:=1;beginselect SUBSTR(to_char(last_day(to_date(WhereDate,yyyy-mm)),YYYYMMDD),0,6) into Montdate from dual;--当前月份select to_char(last_day(to_date(WhereDate,yyyy-mm)),YYYYMMDD) into Moendate from dual;--采集日期--删除当月查询结果（为支持多次查询）delete from mg_VITAGE where o_cdat=Moendate;commit;--删除临时统计数据delete from MG_RISKCONREPLAN;commit;--删除逾期统计情况delete from mg_riskcontab where o_cdat=Montdate;commit;--向还款信息表中插入数据insert into MG_RISKCONREPLANselect * from r_con_repayplan where o_cdat=Moendate and isov=Y;commit;--向逾期统计情况表中插入数据insert into mg_riskcontab(contno,paystadate,actualpaystadate,ov_day,o_cdat)select contno,TO_CHAR(paystadate,YYYYMMDD),TO_CHAR(actualpaystadate,YYYYMMDD),case when actualpaystadate is nullthen to_date(Moendate,YYYYMMDD)-(select paystadate from MG_RISKCONREPLAN r where termid=(select min(termid) from MG_RISKCONREPLAN c where c.contno=r.contno) and s.contno=r.contno)+1else actualpaystadate-paystadate end as ov_day,Montdate as cdatfrom MG_RISKCONREPLAN swhere substr(to_char(paystadate,YYYYMMDD),1,6)=Montdate;commit;update mg_riskcontab b set EFFECTDATE=(select to_char(EFFECTDATE,YYYYMMDD) from r_con_lncontinfo l where o_cdat=Moendate and b.contno=l.contno)where exists (select 1 from r_con_lncontinfo l where o_cdat=Moendate and b.contno=l.contno);COMMIT;--设置变量j:=months_between(to_date(WhereDate,yyyy-mm),to_date(2015-10,yyyy-mm));while i =j loopselect SUBSTR(to_char(last_day(add_months(last_day(to_date(WhereDate,yyyy-mm)), -i)),YYYYMMDD),0,6) into Jieqdate from dual;--生效日期--向统计表中插入数据insert into mg_VITAGEselectJieqdate as 生效月份,Moendate AS 采集日期,(select count(distinct contno) from R_CON_LNCONTINFO where substr(to_char(EFFECTDATE,YYYYMMDD),1,6)=Jieqdate and CONSTATE is not null ) AS overduecon,--合同账户数,(select count(distinct contno) from mg_riskcontab where substr(EFFECTDATE,1,6)=Jieqdate AND substr(pay