oracle学习笔记

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表当天的历史数据；支持重跑