系统迁移实现自动数据校验 - 金融行业 - ITPUB论坛－专业的IT技术社区

wangjia

res_1      number(12);
res_2      number(12);
flag_n     number(1);
temp_sql    varchar2(1000);
/* 1、建DBlink
2、建配置表、结果表
3、加载校验sql语句
4、跑存储过程test
5、执行SQL，跑出结果清单*/
/* create database link MODELANSWER
connect to MODELANSWER
using 'SOLAR';*/
/*drop table test_config;
create table test_config
(
ID number(4)--ID
,module varchar2(20)--验证主题
,class number(1)--类别，1：新老数据校验，0：关联关系校验
,check_item varchar2(100)--逻辑验证项
,str_sql1 varchar2(500)--验证sql1
,str_sql2 varchar2(500)--验证sql2
);
drop table test_result;
create table test_result
(
ID number(4) --ID
,str_result1 number(12)--结果记录数
,str_result2 number(12)--结果记录数
,flag number(1)--1:数据正常，0：数据异常，-1：暂无验证SQL或者验证SQL错误
);*/
/*select t1.id,t2.module,t2.check_item,t2.class,t1.str_result1,t1.str_result2,t1.flag
from test_result1 t1
left join test_config8 t2
on t1.id = t2.id*/
begin
EXECUTE IMMEDIATE 'truncate table test_result1';
commit;
--新老数据校验
FOR i in (select ID, str_sql1, str_sql2 from test_config8 where class = '1') loop
    begin
     EXECUTE IMMEDIATE i.str_sql1
     into res_1;
    EXCEPTION
     when others then
     res_1 := 99999999999;
    end;
    begin
     EXECUTE IMMEDIATE i.str_sql2
     into res_2;
    EXCEPTION
     when others then
     res_2 := 99999999999;
    end;
    if res_1 = 99999999999 or res_2 = 99999999999 then
     flag_n := -1;
    else
     if res_1 = res_2 then
     flag_n := 1;
     else
     flag_n := 0;
     end if;
    end if;
    temp_sql := 'INSERT INTO test_result1(ID,str_result1,str_result2,flag) values( ' || i.ID || ',' ||
            res_1 || ',' || res_2 || ',' || flag_n || ')';
    EXECUTE IMMEDIATE temp_sql;
    COMMIT;
end loop;
--关联数据校验
FOR i in (select ID, str_sql1 from test_config8 where class = '0') loop
    begin
     EXECUTE IMMEDIATE i.str_sql1
     into res_1;
    EXCEPTION
     when others then
     res_1 := 99999999999;
    end;
    if res_1 = 99999999999 then
     flag_n := -1;
    else
     if res_1 = 0 then
     flag_n := 1;
     else
     flag_n := 0;
     end if;
    end if;
    temp_sql := 'INSERT INTO test_result1(ID,flag) values( ' || i.ID || ',' || flag_n || ')';
    EXECUTE IMMEDIATE temp_sql;
end loop;
EXCEPTION
when others then
    dbms_output.put_line(SQLCODE);
end;
/