存储过程优化后 验证 优化后的存储过程是否正确

由于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),'')
);