select * from test_all;   --全量的數據
insert into test_all (ID, NAME, FISRT_FLG)
values ('1', 'aaa', '1');

insert into test_all (ID, NAME, FISRT_FLG)
values ('2', 'bbc', '1');

insert into test_all (ID, NAME, FISRT_FLG)
values ('3', 'ccc', '1');

insert into test_all (ID, NAME, FISRT_FLG)
values ('4', 'ddd', '1');
commit;

select* from test_his;    --歷史全量數據(有切片)
insert into test_his (ID, NAME, DATA_DT, FIRST_FLG)
values ('1', 'aaa', '20221014', '0');

insert into test_his (ID, NAME, DATA_DT, FIRST_FLG)
values ('2', 'bbb', '20221015', '0');
commit;

--更新fisrt_flg值
update test_all  t1
set t1.fisrt_flg = '0'
where exists (select 1 from test_his t2 /***應該取最新一條數據防止反復變化****/
where t1.id = t2.id);
commit;

select * from test1;      --要回傳的數據`
insert into test1
select * from test_all t1
where t1.fisrt_flg='0'
and not exists (
select 1 from test_his  t2 where  /***最新一條數據***/
t1.id||t1.name = t1.id||t2.name
--and t1.name = t2.name 
and t2.first_flg = '0'
);
commit;

向歷史表插入數據;
insert into test_his
select 
t1.id,
t1.name,
to_char(sysdate,'yyyymmdd'),
'1' as fisrt_flg
from test_all t1
where not exists (
select 1 from test_his t2
where t1.id||t1.name = t2.id||t2.name
);
commit;

程序之前記得刪除his表當天的歷史數據；支持重跑