oracle 进阶（七）修数
修数逻辑构成
一个完整的修数过程应该包含如下几部分：
1、数据备份；
2、修数逻辑实现；
3、验证；
4、（出错时执行）回滚。
示例
例 1
1、数据备份
--备份额度流水
create table clm_trafficflow_0125 as(
select * from clm_crdmg_trafficflow t where t.busitype='1'and t.operatetype='290CL1801'
and t.operatereason='2'
and (t.reasonremark ='CECM-IBMS二次占用时恢复一次占用'or t.reasonremark='CECM-IBMS二次额度占用恢复')
);
--备份限额流水
create table clm_lmt_ocp_recd_0125 as (
select * from clm_lmt_ocp_recd r where r.abst='290CL1801' and r.ocpsubjtype='2'
);
2、修数逻辑实现
--修额度流水
merge into clm_crdmg_trafficflow t
using (select t1.flowid, t1.busino, ci.ibmsno
from clm_trafficflow_0125 t1,
(select c.contractno,
c.ibmsno,
rank() over(partition by c.contractno order by c.deleteflag asc, c.contractid desc) rowno
from clm_crdmg_contract_info c
where c.ibmsno is not null) ci
where t1.busino = ci.contractno
and ci.rowno = 1
and t1.busitype = '1') temp
on (temp.flowid = t.flowid)
when matched then
update
set t.busitype = '2',
t.busino = temp.ibmsno,
t.operatereason = '3',
t.operatetype = '290CL1807',
t.reasonremark = 'CECM合同占用，IBMS批复解除';
--修限额流水
update clm_lmt_ocp_recd r set r.absttype='3' ,r.abst='290CL1807',r.abstrmk='CECM合同占用，IBMS批复解除'
where r.abst='290CL1801' and r.ocpsubjtype='2' ;
3、验证
--额度流水
select count(1) from clm_crdmg_trafficflow t where t.busitype='1'and t.operatetype='290CL1801'
and t.operatereason='2'
and (t.reasonremark ='CECM-IBMS二次占用时恢复一次占用'or t.reasonremark='CECM-IBMS二次额度占用恢复');
--限额流水
select count(1) from clm_lmt_ocp_recd r where r.abst='290CL1801' and r.ocpsubjtype='2' and r.absttype='2';
4、（出错时执行）回滚
--回滚额度流水表
delete from clm_crdmg_trafficflow t where exists(select 1 from clm_trafficflow_0125 t125 where t125.flowid=t.flowid) ;
insert into clm_crdmg_trafficflow select * from clm_trafficflow_0125;
drop table clm_trafficflow_0125;
--回滚限额流水表
delete from clm_lmt_ocp_recd r where exists(select 1 from clm_lmt_ocp_recd_0125 r125 where r125.ocprecdid=r.ocprecdid);
insert into clm_lmt_ocp_recd select * from clm_lmt_ocp_recd_0125;
drop table clm_lmt_ocp_recd_0125;
例 2
1、数据库备份
/*展期业务集团限额占用规则优化备份和修数过程*/
--备份限额占用关系表
create table CLM_LMT_OCP_REL_0199_0921 as select * from CLM_LMT_OCP_REL;
--备份限额占用关系删除表
create table CLM_LMT_OCP_REL_DEL_0199_0921 as select * from CLM_LMT_OCP_REL_DEL;
--备份集团限额表
create table CLM_GRP_LMT_0199_0921 as select * from CLM_GRP_LMT;
--备份公司限额表
create table CLM_CMPN_LMT_0199_0921 as select * from CLM_CMPN_LMT;
--备份分类限额表
create table CLM_CLFC_LMT_0199_0921 as select * from CLM_CLFC_LMT;
--备份集团限额删除表
create table CLM_GRP_LMT_DEL_0199_0921 as select * from CLM_GRP_LMT_DEL;
--备份公司限额删除表
create table CLM_CMPN_LMT_DEL_0199_0921 as select * from CLM_CMPN_LMT_DEL;
--备份分类限额删除表
create table CLM_CLFC_LMT_DEL_0199_0921 as select * from CLM_CLFC_LMT_DEL;
2、修数逻辑实现
declare
--限额币种占用限额
v_lmtcrcyocplmt CLM_LMT_OCP_REL.lmtcrcyocplmt%TYPE;
--创建游标
cursor ocprel is
select ocprel.grplmtno,ocprel.lmttype,ocprel.mebecifno,ocprel.lmtcrcyocplmt, ocprel.lmtocprelid from CLM_LMT_OCP_REL ocprel ,
CLM_CRDMG_CREDIT_MAIN creditmain
where ocprel.ocpsubjtype='1' and
ocprel.creditno=creditmain.creditno
and creditmain.loanproperty='04';
BEGIN
--循环游标
for ocpreleach in ocprel
LOOP
v_lmtcrcyocplmt :=nvl(ocpreleach.lmtcrcyocplmt,'0');
/*如果限额占用信息中“限额类型”为公司限额11，将“限额币种占用限额”的值恢复到“公司限额表”中的
“可用限额”，同时“已用限额”减去“限额币种占用限额”，关联字段为“集团限额编号”、“成员ECIF编号”。 */
IF (trim(ocpreleach.lmttype) = '11')
THEN
merge into CLM_CMPN_LMT cmpnlmt using (
select nvl(usedlmt,'0') usedlmt ,nvl(avlblmt,'0') avlblmt , grplmtno, mebecifno
from CLM_CMPN_LMT where grplmtno=ocpreleach.grplmtno and mebecifno=ocpreleach.mebecifno ) cmpnlmtold
on (cmpnlmt.grplmtno=cmpnlmtold.grplmtno and cmpnlmt.mebecifno=cmpnlmtold.mebecifno)
when matched then update set cmpnlmt.usedlmt=(greatest ((cmpnlmtold.usedlmt-v_lmtcrcyocplmt),0)),cmpnlmt.avlblmt=(cmpnlmtold.avlblmt+v_lmtcrcyocplmt),cmpnlmt.updtdate=sysdate;
/*如果限额占用信息中“限额类型”为共享限额20，将“限额币种占用限额”的值恢复到“分类限额表”中的
“可用限额”，同时“已用限额”减去“限额币种占用限额”，关联字段为“集团限额编号”、“限额类型”为共享限额20。*/
ELSIF (trim(ocpreleach.lmttype) = '20')
THEN
merge into CLM_CLFC_LMT clfclmt using (
select nvl(usedlmt,'0') usedlmt ,nvl(avlblmt,'0') avlblmt , grplmtno,lmttype
from CLM_CLFC_LMT where grplmtno=ocpreleach.grplmtno and lmttype='20' ) clfclmtold
on (clfclmt.grplmtno=clfclmtold.grplmtno and clfclmt.lmttype=clfclmtold.lmttype)
when matched then update set clfclmt.usedlmt=(greatest ((clfclmtold.usedlmt-v_lmtcrcyocplmt),0)),clfclmt.avlblmt=(clfclmtold.avlblmt+v_lmtcrcyocplmt),clfclmt.updtdate=sysdate;
/* 如果限额占用信息中“限额类型”为其他暂不做处理*/
ELSE
null;
END IF;
/* 将占用关系中的“限额币种占用限额”的值恢复到“集团限额表”中的“可用限额”，
同时“已用限额”减去“限额币种占用限额”，关联字段为“集团限额编号”。*/
merge into CLM_GRP_LMT grpclmt using (
select nvl(usedlmt,'0') usedlmt ,nvl(avlblmt,'0') avlblmt , grplmtno
from CLM_GRP_LMT where grplmtno=ocpreleach.grplmtno ) grpclmtold
on (grpclmt.grplmtno=grpclmtold.grplmtno )
when matched then update set grpclmt.usedlmt=(greatest ((grpclmtold.usedlmt-v_lmtcrcyocplmt),0)),grpclmt.avlblmt=(grpclmtold.avlblmt+v_lmtcrcyocplmt),grpclmt.updtdate=sysdate;
/*删除限额占用信息，触发删除的触发器自动向“限额占用关系删除表”写入对应信息*/
--删除占用关系删除表中可能存在的干扰数据
delete from CLM_LMT_OCP_REL_DEL where lmtocprelid=ocpreleach.lmtocprelid;
--删除占用关系表中数据
delete from CLM_LMT_OCP_REL where lmtocprelid=ocpreleach.lmtocprelid;
end LOOP;
END;
3、验证
--验证展期业务限额占用规则优化修数过程是否生效
select count(1) from CLM_LMT_OCP_REL ocprel ,
CLM_CRDMG_CREDIT_MAIN creditmain
where ocprel.ocpsubjtype='1' and
ocprel.creditno=creditmain.creditno
and creditmain.loanproperty='04';
4、（出错时执行）回滚
/*回退和删除新建表 */
--回退限额占用关系表
delete from CLM_LMT_OCP_REL_DEL;
delete from CLM_LMT_OCP_REL;
insert into CLM_LMT_OCP_REL select * from CLM_LMT_OCP_REL_0199_0921;
--回退限额占用关系删除表
delete from CLM_LMT_OCP_REL_DEL;
insert into CLM_LMT_OCP_REL_DEL select * from CLM_LMT_OCP_REL_DEL_0199_0921;
--回退集团限额表
delete from CLM_GRP_LMT_DEL;
delete from CLM_GRP_LMT;
insert into CLM_GRP_LMT select * from CLM_GRP_LMT_0199_0921;
--回退集团限额删除表
delete from CLM_GRP_LMT_DEL;
insert into CLM_GRP_LMT_DEL select * from CLM_GRP_LMT_DEL_0199_0921;
--回退公司限额表
delete from CLM_CMPN_LMT_DEL;
delete from CLM_CMPN_LMT;
insert into CLM_CMPN_LMT select * from CLM_CMPN_LMT_0199_0921;
--回退公司限额删除表
delete from CLM_CMPN_LMT_DEL;
insert into CLM_CMPN_LMT_DEL select * from CLM_CMPN_LMT_DEL_0199_0921;
--回退分类限额表
delete from CLM_CLFC_LMT_DEL;
delete from CLM_CLFC_LMT;
insert into CLM_CLFC_LMT select * from CLM_CLFC_LMT_0199_0921;
--回退分类限额删除表
delete from CLM_CLFC_LMT_DEL;
insert into CLM_CLFC_LMT_DEL select * from CLM_CLFC_LMT_DEL_0199_0921;
--删除新建表
drop table CLM_LMT_OCP_REL_0199_0921;
drop table CLM_LMT_OCP_REL_DEL_0199_0921;
drop table CLM_GRP_LMT_0199_0921;
drop table CLM_CMPN_LMT_0199_0921;
drop table CLM_CLFC_LMT_0199_0921;
drop table CLM_GRP_LMT_DEL_0199_0921;
drop table CLM_CMPN_LMT_DEL_0199_0921;
drop table CLM_CLFC_LMT_DEL_0199_0921;