Oracle - PLSQL 用于检查截断/加载过程后记录数的差异

Question

我们的IT团队每个月都会加载几张桌子。新加载的记录应比以前的加载多，至少多 2% 的记录。 这是一个截断和加载过程，我在截断之前从每个表中收集记录数，并且我每个月都会检查 excel 中的差异以确保数据加载正确。 无论如何可以在甲骨文中自动执行此操作。

例如：

Table_name     Before_cnt       After_cnt
XX_TEST1    4,606,619,326   4,983,759,822 
XX_TEST2      121,973,005     123,161,581 

Answer 1

您可以像下面这样应用步骤：

SQL> create table XX_TEST1( id int primary key );
SQL> insert into XX_TEST1 select level from dual connect by level <= 100;
SQL> begin -- if table exists, then drop it!
for c in (select table_name from cat where table_name = 'XX_TEST1_OLD' )
loop
execute immediate 'drop table '||c.table_name;
end loop; 
end;  
/    
SQL> create table XX_TEST1_old as select count(*) as cnt from  XX_TEST1;
SQL> begin
execute immediate 'truncate table XX_TEST1';
end;  
/  
SQL> insert into XX_TEST1 select level from dual connect by level <= 103;
SQL> with xt1_new(cnt_new) as
(
select count(id) from XX_TEST1
)
select case when sign( (100 * ( cnt_new - cnt) / cnt)-2 ) = 1 then 1
else 0 end as "Rate Satisfaction"
from XX_TEST1_old
cross join xt1_new;

如果此 SELECT 语句重新调整1，则我们成功达到目标，否则返回0并表示我们不成功。

演示