史上最详拉链表演示

史上最详拉链表演示

-拉链表
--就是反应一条数据历史变化过程的表，
拉链表程序涉及到的表
1 源表--取数据的源头    2 拉链表 --这张表里的数据体现了一个数据变化过程

实现拉链表的过程
1--造一个数据来源表，一个拉链表（相当于目标表）
2--来源表的数据同步到拉链表
2.1 当来源表的数据发生了变化(新增或者修改)
2.2 需要再次将发生了变化 (新增或者修改) 的数据同步到拉链表，此时同一个业务主键会有2条或者多条数据（比如7369的员工发生了变化，需要
    将7369的数据同步到拉链表中，拉链表里就会有2条 7369 的数据）
3--修改拉链表中重复数据的失效时间（将原来那条数据的失效时间修改为当前时间）

---造源表
CREATE TABLE EMP_LALIAN_SOURCE 
AS
SELECT T.*
FROM EMP T;

SELECT * FROM EMP_LALIAN_SOURCE;  --- 源表 

--创建一张拉链表  
create table EMP_LALIAN_TAG
(
  EMPNO    NUMBER(4)
  ,ENAME    VARCHAR2(10)
  ,JOB      VARCHAR2(9)
  ,MGR      NUMBER(4)
  ,HIREDATE DATE
  ,SAL      NUMBER(7,2)
  ,COMM     NUMBER(7,2)
  ,DEPTNO   NUMBER(2)
  ,START_DATE  DATE
  ,END_DATE    DATE
  ,ACTIVE_FLAG  VARCHAR2(10)
);

-----2--来源表的数据同步到拉链表， --初始化基础数据
INSERT INTO EMP_LALIAN_TAG 
SELECT T.EMPNO,
       T.ENAME,
       T.JOB,
       T.MGR,
       T.HIREDATE,
       T.SAL,
       T.COMM,
       T.DEPTNO
       ,T.HIREDATE---入职日期这一天，就是这条数据的开始生效时间
       ,TO_DATE('47121231','YYYYMMDD')
       ,'Y'
FROM EMP_LALIAN_SOURCE T;
COMMIT;

SELECT * FROM EMP_LALIAN_TAG;   --查看初始化基础数据

---2.1 当来源表的数据发生了变化
UPDATE EMP_LALIAN_SOURCE T SET DEPTNO = 40  WHERE DEPTNO = 20;
UPDATE EMP_LALIAN_SOURCE T SET SAL = 2850  WHERE EMPNO= 7782;
INSERT INTO EMP_LALIAN_SOURCE VALUES(123,'AAA','CLERK',7698,TRUNC(SYSDATE,'DD'),2380,100,20);

COMMIT;
SELECT * FROM EMP_LALIAN_SOURCE;

--2.3 需要再次将发生了变化的数据同步到拉链表，此时同一个业务主键会有2条数据
---2.3 ---新增，或者有改变的最新数据写入到 拉链表  ---开链
INSERT INTO EMP_LALIAN_TAG
SELECT T.EMPNO,
       T.ENAME,
       T.JOB,
       T.MGR,
       T.HIREDATE,
       T.SAL,
       T.COMM,
       T.DEPTNO
       ,TRUNC(SYSDATE,'DD') AS START_DATE
       ,TO_DATE('47121231','YYYYMMDD') AS END_DATE
       ,'Y'  ACTIVE_FLAG
FROM  EMP_LALIAN_SOURCE T 
WHERE NOT EXISTS (SELECT 1
                 FROM EMP_LALIAN_TAG T1
                 WHERE T1.END_DATE = TO_DATE('4712-12-31','YYYY-MM-DD')
                 AND T.EMPNO = T1.EMPNO
                 AND T1.ENAME = T.ENAME
                 AND T.JOB = T1.JOB 
                 AND T.HIREDATE = T1.HIREDATE
                 AND NVL(T.MGR,0) = NVL(T1.MGR,0) 
                 AND T.SAL = T1.SAL 
                 AND NVL(T.COMM,'1') = NVL(T1.COMM,'1') 
                 AND T.DEPTNO = T1.DEPTNO 
                 );
COMMIT;
SELECT * FROM EMP_LALIAN_TAG;

---验证新增或者修改的数据是否插入到了拉链表里
-- SELECT * FROM EMP_LALIAN_TAG
UPDATE EMP_LALIAN_TAG T 
SET T.END_DATE = TRUNC(SYSDATE,'DD')
    ,T.ACTIVE_FLAG = 'N'
--SELECT * FROM  EMP_LALIAN_TAG T 
WHERE  T.END_DATE = TO_DATE('4712-12-31','YYYY-MM-DD')
AND EXISTS (SELECT 1
           FROM EMP_LALIAN_SOURCE T2
            WHERE T.EMPNO = T2.EMPNO 
              AND (T.ENAME <> T2.ENAME OR T.JOB <> T2.JOB OR NVL(T.MGR,0) <> NVL(T2.MGR,0) OR T.SAL <> T2.SAL 
                   OR NVL(T.COMM,'1') <> NVL(T2.COMM,'1') OR T.DEPTNO <> T2.DEPTNO OR T.HIREDATE <> T2.HIREDATE
                   ) 
           );

COMMIT;

---验证数据是否发生了改变
SELECT * FROM EMP_LALIAN_TAG;
SELECT * FROM EMP_LALIAN_SOURCE;  --- 源表  

--拉链表练习
用学生表 Student 作为拉链表的源表
要自己创建一个 拉链表 Student_TAG
SELECT * FROM Student;

--学生 student表
drop table student;
create table student(
sno varchar2(10) ,
sname varchar2(20),
sage number(2),
ssex varchar2(5),
birthday date
);

/*******初始化学生表的数据******/
insert into student values ('s001','张芃',FLOOR(months_between(SYSDATE,date '2000-3-5')/12),'男',date '2000-3-5');
insert into student values ('s002','李蕤',FLOOR(months_between(SYSDATE,date '2001-2-3')/12),'男',date '2001-2-3');
insert into student values ('s003','吴筱莜',FLOOR(months_between(SYSDATE,date '2002-5-8')/12),'男',date '2002-5-8');
insert into student values ('s004','琴沁',FLOOR(months_between(SYSDATE,date '2000-6-15')/12),'女',date '2000-6-15');
insert into student values ('s005','王郦珩',FLOOR(months_between(SYSDATE,date '2000-8-12')/12),'女',date '2000-8-12');
insert into student values ('s006','李薄骞',FLOOR(months_between(SYSDATE,date '2001-9-20')/12),'男',date '2001-9-20');
insert into student values ('s007','刘昱音',FLOOR(months_between(SYSDATE,date '2002-10-5')/12),'男',date '2002-10-5');
insert into student values ('s008','萧戎翰',FLOOR(months_between(SYSDATE,date '2003-6-1')/12),'女',date '2003-6-1');
insert into student values ('s009','陈萧晓妍',FLOOR(months_between(SYSDATE,date '2001-1-15')/12),'女',date '2001-1-15');
insert into student values ('s010','陈媺珈',FLOOR(months_between(SYSDATE,date '2001-1-9')/12),'女',date '2001-1-9');
commit;

--拉链表的初始化  就是  将源表的数据   写到 拉链表
实现拉链表的过程
1--造一个数据来源表，一个拉链表（相当于目标表）
2--来源表的数据同步到拉链表
2.1 当来源表的数据发生了变化
2.3 需要再次将发生了变化的数据同步到拉链表
3 修改拉链表中重复数据的失效时间（将原来那条数据的失效时间修改为当前时间）以及有效标识
--DROP TABLE S;
SELECT * FROM  S;
SELECT * FROM Student_TAG;
CREATE TABLE Student_TAG(
        sno varchar2(10) ,
        sname varchar2(20),
        sage number(2),
        ssex varchar2(5),
        birthday date
       ,START_DATE  DATE
       ,END_DATE    DATE
       ,ACTIVE_FLAG  VARCHAR2(10)
);
---------------初始化数据
INSERT INTO  Student_TAG  
SELECT  T.SNO,
        T.SNAME,
        T.SAGE,
        T.SSEX,
        T.BIRTHDAY,  
        T.BIRTHDAY,
        TO_DATE(47121231，'YYYYMMDD')  END_DATE,
        'Y' ACTIVE_FLAG
        FROM S T;
COMMIT;
-----------------------原表发生变化
UPDATE S T SET T.SAGE = 18 WHERE T.SNO = 's004';
COMMIT;
INSERT INTO  S VALUES('123','红月',16,'女',TO_DATE(20221001,'YYYYMMDD'));
INSERT INTO  S VALUES('11','无极',99,'男',TO_DATE(20221010,'YYYYMMDD'));
COMMIT;

---------------------新增的数据同步到拉链表

INSERT  INTO Student_TAG  
SELECT T.SNO,
       T.SNAME,
       T.SAGE,
       T.SSEX,
       T.BIRTHDAY,  
       T.BIRTHDAY START_DATE,
       TO_DATE('47121231','YYYYMMDD') END_DATE,
       'Y' ACTIVE_FLAG
 FROM S T
WHERE NOT EXISTS(SELECT 1 FROM Student_TAG S WHERE S.END_DATE = TO_DATE('47121231','YYYYMMDD')  
                 AND  S.SNO = T.SNO 
                 AND  S.SNAME =T.SNAME
                 AND  S.SAGE = T.SAGE
                 AND  S.SSEX = T.SSEX
                 AND  S.BIRTHDAY = T.BIRTHDAY
);
COMMIT;

SELECT * FROM Student_TAG;

-------------------修改拉链表的时间为失效
UPDATE  Student_TAG S SET  S.END_DATE = TRUNC(SYSDATE,'DD') ,S.ACTIVE_FLAG = 'N'
WHERE S.END_DATE = TO_DATE('47121231','YYYYMMDD') 
AND EXISTS ( SELECT 1 FROM S T WHERE T.SNO = S.SNO 
                      AND  (T.SNAME <> S.SNAME 
                      OR  T.SAGE  <> S.SAGE
                      OR  T.SSEX  <> S.SSEX
                      OR  T.BIRTHDAY <> S.BIRTHDAY)
);

COMMIT;

--思考以下如果要把拉链逻辑放在 存储过程里，该将哪一个步骤的代码放在存储过程里去
CREATE OR REPLACE PROCEDURE SP_Student_TAG
AS
BEGIN 
INSERT  INTO Student_TAG  
SELECT T.SNO,
       T.SNAME,
       T.SAGE,
       T.SSEX,
       T.BIRTHDAY,  
       T.BIRTHDAY START_DATE,
       TO_DATE('47121231','YYYYMMDD') END_DATE,
       'Y' ACTIVE_FLAG
 FROM S T
WHERE NOT EXISTS(SELECT 1 FROM Student_TAG S WHERE S.END_DATE = TO_DATE('47121231','YYYYMMDD')  
                 AND  S.SNO = T.SNO 
                 AND  S.SNAME =T.SNAME
                 AND  S.SAGE = T.SAGE
                 AND  S.SSEX = T.SSEX
                 AND  S.BIRTHDAY = T.BIRTHDAY);
        
UPDATE  Student_TAG S SET  S.END_DATE = TRUNC(SYSDATE,'DD') ,S.ACTIVE_FLAG = 'N'
WHERE S.END_DATE = TO_DATE('47121231','YYYYMMDD') 
AND EXISTS ( SELECT 1 FROM S T WHERE T.SNO = S.SNO 
                      AND  (T.SNAME <> S.SNAME 
                      OR  T.SAGE  <> S.SAGE
                      OR  T.SSEX  <> S.SSEX
                      OR  T.BIRTHDAY <> S.BIRTHDAY));

COMMIT;
EXCEPTION WHEN OTHERS THEN  ROLLBACK;
  DBMS_OUTPUT.put_line(SQLERRM);
END SP_Student_TAG;

BEGIN
  SP_Student_TAG;
END;

SELECT * FROM  Student_TAG;

---新增数据
SELECT  * FROM S;
INSERT INTO  S VALUES('23','红月尊者',17,'女',TO_DATE(20220901,'YYYYMMDD'));
INSERT INTO  S VALUES('09','无极帝',20,'男',TO_DATE(20220910,'YYYYMMDD'));
COMMIT;