由于proc_policyDetail执行效率偏低,所以可能对proc_policyDetail进行优化。但是编写好新的procedure怎么来确定它是正确的呢?
我们可以改写proc_policyDetail,将其执行的结果保存到一张临时表中temp_policy_one。将proc_policyDetail_n执行的结果存储到temp_policy_two中。
--在保单号相同的情况下,分别使用新旧procedure进行数据采集
procedure main as
begin
for cur_policy in (
select POLICY_NO from vw_ods_policy where rownum < 11 --取10个保单号进行测试
)loop
proc_policyDetail(cur_policy.POLICY_NO);--优化之前的procedure,将前10个保单号执行的结果保存到temp_policy_one
proc_policyDetail_n(cur_policy.POLICY_NO); --优化之后的proedure,将前10个保单号执行的结果保存到temp_policy_two
end loop;
end main;
由于proc_policyDetail已经在生产上使用了很长的事件,确认没问题的,所以说proc_policyDetail_n中采集的数据要与proc_policyDetail采集的数据一致,则说明proc_policyDetail_n是正确的。
采集完后,需要比较2张表中的数据是否一致,我们可以采用如下办法:将所有字段拼接到一起进行比较
select * from temp_policy_one a,temp_policy_two b
where a.policy_no = b.policy_no
and
(
nvl(to_char(a.policy_no),'')
||nvl(to_char(a.POLICY_STATUS),'')
||nvl(to_char(a.STATUS_DATE),'')
||nvl(to_char(a.CVRG_NAME),'')
||nvl(to_char(a.EFFECT_DATE),'')
||nvl(to_char(a.AREA),'')
||nvl(to_char(a.CHANNEL),'')
||nvl(to_char(a.ISSUE_DATE),'')
||nvl(to_char(a.RCPT_ACK_DATE),'')
||nvl(to_char(a.PREM),'')
||nvl(to_char(a.LOAN),'')
||nvl(to_char(a.BLLINGFREQ),'')
||nvl(to_char(a.PAYTODAY),'')
||nvl(to_char(a.NFO_DEFAULT_TYPE),'')
||nvl(to_char(a.BLLNG_ACCT_NMBR),'')
||nvl(to_char(a.SUSPEND_EVENT),'')
||nvl(to_char(a.SUSPEND_REASON),'')
||nvl(to_char(a.PAYMENTMETHOD_NEXT),'')
||nvl(to_char(a.PAYMENT_ADDRESS),'')
||nvl(to_char(a.PREM_M),'')
||nvl(to_char(a.PREM_S),'')
||nvl(to_char(a.PREM_H),'')
||nvl(to_char(a.PREM_Y),'')
||nvl(to_char(a.PREM_N),'')
||nvl(to_char(a.VIP),'')
||nvl(to_char(a.BLLNG_BANK_NAME),'')
||nvl(to_char(a.AUTOPAYMENTDATE_LAST),'')
)
<>
(
nvl(to_char(b.policy_no),'')
||nvl(to_char(b.POLICY_STATUS),'')
||nvl(to_char(b.STATUS_DATE),'')
||nvl(to_char(b.CVRG_NAME),'')
||nvl(to_char(b.EFFECT_DATE),'')
||nvl(to_char(b.AREA),'')
||nvl(to_char(b.CHANNEL),'')
||nvl(to_char(b.ISSUE_DATE),'')
||nvl(to_char(b.RCPT_ACK_DATE),'')
||nvl(to_char(b.PREM),'')
||nvl(to_char(b.LOAN),'')
||nvl(to_char(b.BLLINGFREQ),'')
||nvl(to_char(b.PAYTODAY),'')
||nvl(to_char(b.NFO_DEFAULT_TYPE),'')
||nvl(to_char(b.BLLNG_ACCT_NMBR),'')
||nvl(to_char(b.SUSPEND_EVENT),'')
||nvl(to_char(b.SUSPEND_REASON),'')
||nvl(to_char(b.PAYMENTMETHOD_NEXT),'')
||nvl(to_char(b.PAYMENT_ADDRESS),'')
||nvl(to_char(b.PREM_M),'')
||nvl(to_char(b.PREM_S),'')
||nvl(to_char(b.PREM_H),'')
||nvl(to_char(b.PREM_Y),'')
||nvl(to_char(b.PREM_N),'')
||nvl(to_char(b.VIP),'')
||nvl(to_char(b.BLLNG_BANK_NAME),'')
||nvl(to_char(b.AUTOPAYMENTDATE_LAST),'')
);