1 /*拉链表: 也就是一个 记录历史 表,用于记录事物从 最开始的状态 到 当前状态 所有变化的信息 */
2
3 --历史表
4 drop table old_tb_his;
5 drop table new_tb;
6
7
8 --od 我们希望的拉链表记录数据的所有状态，初始化为昨日全量数据
9 create table old_tb_his(
10 id number(10,0),
11 status varchar2(20),
12 start_date varchar2(20),
13 end_date varchar2(20)
14 );
15 insert into old_tb_his values(1,'1', '200712' , '299901');
16 insert into old_tb_his values(2,'2', '200712' , '299901');
17 insert into old_tb_his values(3,'3', '200712' , '299901');
18 insert into old_tb_his values(4,'4', '200712' , '299901');
19 insert into old_tb_his values(5,'5', '200712' , '299901');
20 commit;
21 select * from old_tb_his;
22
23
24 --nd 今日全量数据
25 create table new_tb as select * from old_tb_his where 2 = 1 ;
26 insert into new_tb values(1,'2', '200801' , '299901');
27 insert into new_tb values(2,'2', '200801' , '299901');
28 insert into new_tb values(3,'4', '200801' , '299901');
29 insert into new_tb values(4,'4', '200801' , '299901');
30 insert into new_tb values(5,'6', '200801' , '299901');
31 commit;
32 select * from new_tb;
33
34
35 /*merge into old_tb_his
36 using new_tb
37 on (old_tb_his.id = new_tb.id and old_tb_his.status = new_tb.status )
38 when matched then update set old_tb_his.end_date = new_tb.start_date
39 when not matched then insert values(new_tb.id, new_tb.status, new_tb.start_date,new_tb.end_date);*/
40
41
42 /*
43 用不了 这个函数是匹配就更新 不匹配添加
44 而拉链算法可以看作是 不匹配的更新 不匹配的也添加
45 */
46 /*merge into old_tb_his
47 using new_tb
48 on (old_tb_his.id = new_tb.id and old_tb_his.status = new_tb.status )
49 when not matched then update set old_tb_his.end_date = new_tb.start_date ;
50 when not matched then insert values(new_tb.id, new_tb.status, new_tb.start_date,new_tb.end_date);
51 */
52 --如果函数不能完成拉链算法 只能通过存储过程来完成
53 select * from old_tb_his;
54 select * from new_tb;
55
56
57 drop table old_tb_his_temp;
58 drop table new_tb_temp;
59 --创建临时表old_tb_his_temp
60 create global temporary table old_tb_his_temp
61 (
62 id number(10,0),
63 status varchar2(20),
64 start_date varchar2(20),
65 end_date varchar2(20)
66 )
67 on commit delete rows ;
68
69 --创建临时表new_tb_temp
70 create global temporary table new_tb_temp
71 (
72 id number(10,0),
73 status varchar2(20),
74 start_date varchar2(20),
75 end_date varchar2(20)
76 )
77 on commit delete rows ;
78
79
80 -- w_i = nd - od ( 将w_i表的内容全部插入到历史表中，这些是新增记录 )
81 --今日全量数据减去拉链表数据 等于新增数据 用w_i表示
82 --UNION(无重并集)、UNION ALL(有重并集)、INTERSECT(交集)、MINUS (差集)：只显示在第一个集合中存在，在第二个集合中不存在的数据
83 insert into old_tb_his_temp
84 select *
85 from new_tb t
86 where t.id not in (select id--今日全量数据，减去拉链表数据和今日全量数据的交集，等于今日新增数据
87 from (select t1.id, t1.status, t1.end_date
88 from old_tb_his t1
89 intersect
90 select t2.id, t2.status, t2.end_date
91 from new_tb t2));--查询拉链表数据和今日全量数据的交集
92
93 -- w_u = od - nd ( 对历史表进行w_u部份的更新操作，start_date保持不变，而end_date改为当天 )封链
94 insert into new_tb_temp
95 select *
96 from old_tb_his t
97 where t.id not in (select id--拉链表数据，减去拉链表数据和今日全量数据的交集，等于待更新数据
98 from (select t1.id, t1.status, t1.end_date
99 from old_tb_his t1
100 intersect
101 select t2.id, t2.status, t2.end_date
102 from new_tb t2));--查询拉链表数据和今日全量数据的交集
103 --查询两个临时表
104 select * from old_tb_his_temp;
105 select * from new_tb_temp;
106 commit;
107
108 --insert操作把w_i插入到拉链表
109 insert into old_tb_his
110 select * from old_tb_his_temp ;
111 --查询是否成功插入
112 --select * from old_tb_his ;
113
114
115 --多表更新语句一: update 操作按u更新his
116 /*merge into old_tb_his
117 using old_tb_his_temp on (old_tb_his.id = old_tb_his_temp.id and old_tb_his.status <> old_tb_his_temp.status )
118 when matched then update set old_tb_his.end_date = old_tb_his_temp.start_date ;*/
119
120 --多表更新语句二: or: update 操作按u更新his
121 update old_tb_his
122 set old_tb_his.end_date = (select old_tb_his_temp.start_date from old_tb_his_temp where old_tb_his_temp.id = old_tb_his.id)
123 where exists(
124 select 1 from old_tb_his_temp where old_tb_his.id = old_tb_his_temp.id
125 and old_tb_his.status <> old_tb_his_temp.status
126 );
127
128 commit;
129
130
131 select * from old_tb_his
132
133 用户标志 状态 开始时间 结束时间
134 1 1 200712 200801 --change
135 2 2 200712 299901
136 3 3 200712 200801 --change
137 4 4 200712 299901
138 5 5 200712 200801 --change
139 1 2 200801 299901
140 3 4 200801 299901
141 5 6 200801 299901
