-- 按时间增量的拉链表示例
-- 先创建表
create table test_tmp_sm_emp(
empno varchar(100),
name varchar(100),
hiredate varchar(100),
gender varchar(100),
groupno varchar(100),
lupdate varchar(100),
pn_dayno varchar(100)
);
create table test_dim_sm_emp(
gno varchar(100),
empno varchar(100),
name varchar(100),
hiredate varchar(100),
gender varchar(100),
groupno varchar(100),
lupdate varchar(100),
staert_date varchar(100),
end_date varchar(100),
pn_dayno varchar(100)
);
-- 查看表数据语句
select * from test_tmp_sm_emp
select * from test_dim_sm_emp
--查看目标表有效数据的语句
select * from test_dim_sm_emp where end_date='99991231'
--删除表数据语句
delete test_tmp_sm_emp where pn_dayno='20221001'
delete test_dim_sm_emp where pn_dayno='20221002'
-- 插入临时表 pn_dayno='20221001'
insert into test_tmp_sm_emp
values('001','张五','','','','20200901','20221001');
-- 插入目标表pn_dayno='20221001'
insert into test_dim_sm_emp(
gno
,empno
,name
,hiredate
,gender
,groupno
,lupdate
,staert_date
,end_date
,pn_dayno )
select
sq_dpt.nextval as gno
,empno as empno
,name as name
,hiredate as hiredate
,gender as gender
,groupno as groupno
,lupdate as lupdate
,to_char(sysdate,'yyyymmdd') as staert_date
,'99991231' as end_date
,pn_dayno as pn_dayno
from test_tmp_sm_emp
where pn_dayno='20221001'
-- 临时表清除前一天的数据
delete test_tmp_sm_emp where pn_dayno='20221001'
-- 插入临时表 pn_dayno='20221002'
insert into test_tmp_sm_emp
values('002','王六','','','','20200902','20221002');
insert into test_tmp_sm_emp
values('001','张五1','','','','20200902','20221002');
-- 先将修改的客户闭链时间改成数据日期的时间(pn_dayno=20221002)
merge into (select * from test_dim_sm_emp where end_date='99991231') a
using test_tmp_sm_emp b
on (a.empno=b.empno)
when matched then
update set end_date= b.pn_dayno
-- 再插入目标表pn_dayno='20221002'
insert into test_dim_sm_emp(
gno
,empno
,name
,hiredate
,gender
,groupno
,lupdate
,staert_date
,end_date
,pn_dayno )
select
sq_dpt.nextval as gno
,empno as empno
,name as name
,hiredate as hiredate
,gender as gender
,groupno as groupno
,lupdate as lupdate
,to_char(sysdate,'yyyymmdd') as staert_date
,'99991231' as end_date
,pn_dayno as pn_dayno
from test_tmp_sm_emp
where pn_dayno='20221002'