场景:
不同库,结构相似表,比较sql脚本
过程描述
考虑到数据量为千万级,表a为小表,表b为大表
1.首先是声明部分,其中定义了一些变量,如 v_primary_key_a、v_column_value_a 等,用来存储a表的查询结果和计数。
DECLARE
v_primary_key_a VARCHAR2(100);
v_column_value_a VARCHAR2(100);
v_primary_key_b VARCHAR2(100);
v_column_value_b VARCHAR2(100);
v_column_desc VARCHAR2(100);
v_a_dblink VARCHAR2(30) := 'ens_test'; -- 数据库外连接 v_a_dblink 未定义
v_b_dblink VARCHAR2(30) := 'ens_test'; -- 数据库外连接 v_b_dblink 未定义
v_fail_count NUMBER := 0;
v_succ_count NUMBER := 0;
v_total_count NUMBER := 0;
2.第一个查询部分,使用了两张表的左连接,检查两张表的记录是否存在对应关系,并且比较其字段值是否一致。
BEGIN
FOR rec IN (
SELECT
a.primary_key_a AS primary_key_a,
a.column_value AS column_value_a,
b.primary_key_b AS primary_key_b,
b.column_value AS column_value_b,
CASE
WHEN a.primary_key_a IS NULL THEN 'b表中的记录不存在于a表'
ELSE NULL
END AS b_not_in_a,
CASE
WHEN b.primary_key_b IS NULL THEN 'a表中的记录不存在于b表'
ELSE NULL
END AS a_not_in_b,
CASE
WHEN a.primary_key_a IS NOT NULL AND b.primary_key_b IS NOT NULL THEN
CASE
WHEN a.column_value = b.column_value THEN 'ab中对应相同主键的信息一致'
ELSE 'ab中对应相同主键的信息不一致'
END
ELSE NULL
END AS ab_comparison
FROM
(SELECT TO_CHAR("internal key") AS primary_key_a, TO_CHAR("loan no") AS column_value FROM "ens test"."cl acct") a
LEFT JOIN
(SELECT TO_CHAR("internal_key_cmp") AS primary_key_b, TO_CHAR("loan_no_cmp") AS column_value_b FROM "ens test"."cl_acct_cmp") b
ON a.primary_key_a = b.primary_key_b
) LOOP
v_total_count := v_total_count + 1;
v_primary_key_a := rec.primary_key_a;
v_column_value_a := rec.column_value_a;
v_primary_key_b := rec.primary_key_b;
v_column_value_b := rec.column_value_b;
IF rec.ab_comparison = 'ab中对应相同主键的信息一致' THEN
v_succ_count := v_succ_count + 1;
END IF;
-- 输出第一个查询结果
DBMS_OUTPUT.PUT_LINE(
RPAD(rec.b_not_in_a, 30) || CHR(9) ||
LPAD(rec.a_not_in_b, 30) || CHR(9) ||
LPAD(rec.ab_comparison, 30) || CHR(9) ||
'a表主键:' || v_primary_key_a || CHR(9) ||
'a表字段:' || v_column_value_a || CHR(9) ||
'b表主键:' || v_primary_key_b || CHR(9) ||
'b表字段:' || v_column_value_b
);
END LOOP;
3.第二个查询部分,查找在 b 表中存在但在 a 表中不存在的记录。
FOR rec IN (
SELECT b.*, 'a表中无b表记录'
FROM (
SELECT TO_CHAR("internal_key_cmp") AS primary_key_b, TO_CHAR("loan_no_cmp") AS column_value_b
FROM "ens test"."cl ac
WHERE NOT EXISTS (
SELECT a.*
FROM (
SELECT TO_CHAR("internal key") AS primary_key_a, TO_CHAR("loan no") AS column_value_b
FROM "ens test"."cl acct"
) a
WHERE a.primary_key_a = b.primary_key_b
)
) LOOP
v_total_count := v_total_count + 1;
v_primary_key_b := rec.primary_key_b;
v_column_value_b := rec.column_value_b;
DBMS_OUTPUT.PUT_LINE(
LPAD('b表中的记录不存在于a表', 34) || CHR(9) ||
LPAD('', 30) || CHR(9) ||
LPAD('', 30) || CHR(9) ||
'a表主键:' || CHR(30) || ',a表字段:' || CHR(30) ||
',b表主键:' || v_primary_key_b || CHR(9) ||
',b表字段:' || v_column_value_b
);
END LOOP;
4.最后是计算比对结果并输出。
v_fail_count := v_total_count - v_succ_count;
DBMS_OUTPUT.PUT_LINE(
LPAD('总比对数', 30) || v_total_count ||
'比对成功数' || v_succ_count ||
'比对失败数' || v_fail_count
);
END;