Oracle拉链表

news/2024/11/29 9:37:03/

-- 准备一个拉链表

-- 2.将所有的数据同步到拉链表中 TEST_TARGET中

--3. 源表的数据发生了变化

--4. 将新增和修改的数据同步到拉链表 -- 开链的过程 -- 判断源表和目标表的数据,不同数据插入

--5. 修改拉链表中失效的时间和状态(将原本的开链时间,改为当前时间)-- 闭链

-- 拉链表
-- 一张反应历史变化的表,维护数据历史状态,和数据最新的状态

-- 拉链表涉及的表
1. 源表(业务数据库里的表)
2. 拉链表

-- 它通常用于数据仓库中维护事实表和维度表之间的关系

-- 拉链表的实现过程.
1. 准备一个源表和一个拉链表
2. 将所有的数据同步到拉链表
3. 源表的数据发生变化
4. 将新增和修改的数据同步到拉链表
5. 修改拉链表的失效时间和状态.

-- 准备一个拉链表

CREATE TABLE TEST_SOURCE  -- 源表
(
S_ID NUMBER,
S_NAME VARCHAR2(10),
S_SAL NUMBER,
CREATE_AT DATE,
UPDATE_AT DATE
);

CREATE TABLE TEST_TARGET --目标表
(
S_ID NUMBER,
S_NAME VARCHAR2(10),
S_SAL NUMBER,
CREATE_AT DATE,
UPDATE_AT DATE,
START_AT DATE,  -- to_date(to_char(sysdate-1,'yyyymmdd'),'yyyymmdd')
END_AT DATE,    -- to_date('9999-12-31','yyyy-mm-dd')
T_ACTIVE VARCHAR2(10) -- '有效'
);

INSERT INTO TEST_SOURCE VALUES(1,'黄征',6000,to_date(to_char(sysdate-1,'yyyymmdd'),'yyyymmdd'),to_date(to_char(sysdate-1,'yyyymmdd'),'yyyymmdd'));
INSERT INTO TEST_SOURCE VALUES(2,'徐峥',7000,to_date(to_char(sysdate-1,'yyyymmdd'),'yyyymmdd'),to_date(to_char(sysdate-1,'yyyymmdd'),'yyyymmdd'));

select * from test_source;

-- 2.将所有的数据同步到拉链表中 TEST_TARGET中

 INSERT INTO TEST_TARGETSELECT S.S_ID,S.S_NAME,S.S_SAL,S.CREATE_AT,S.UPDATE_AT,TO_DATE(TO_CHAR(SYSDATE-1,'yyyymmdd'),'yyyymmdd'),TO_DATE('9999-12-31','yyyy-mm-dd'),'有效'FROM TEST_SOURCE S

SELECT * FROM TEST_TARGET;

--3. 源表的数据发生了变化

 INSERT INTO TEST_SOURCE VALUES (3,'黄海波',8000,to_date(to_char(sysdate,'yyyymmdd'),'yyyymmdd'),to_date(to_char(sysdate,'yyyymmdd'),'yyyymmdd'));UPDATE TEST_SOURCE S SET S.S_SAL=S.S_SAL+900, S.UPDATE_AT=TO_DATE(TO_CHAR(SYSDATE,'YYYYMMDD'),'YYYYMMDD') WHERE S.S_ID=1;

--4. 将新增和修改的数据同步到拉链表 -- 开链的过程
-- 判断源表和目标表的数据,不同数据插入

 INSERT INTO TEST_TARGET SELECT S.S_ID,S.S_NAME,S.S_SAL,S.CREATE_AT,S.UPDATE_AT,TO_DATE(TO_CHAR(SYSDATE-1,'yyyymmdd'),'yyyymmdd'),TO_DATE('9999-12-31','yyyy-mm-dd'),'有效'FROM TEST_SOURCE SWHERE NOT EXISTS(SELECT 1 FROM TEST_TARGET G
WHERE S.S_ID=G.S_IDAND G.S_NAME=S.S_NAME AND G.S_SAL=S.S_SALAND G.CREATE_AT =S.CREATE_ATAND G.UPDATE_AT=S.UPDATE_ATAND G.END_AT=TO_DATE('9999-12-31','yyyy-mm-dd'));

SELECT * FROM TEST_TARGET;

--5. 修改拉链表中失效的时间和状态(将原本的开链时间,改为当前时间)-- 闭链

UPDATE TEST_TARGET T SET T.END_AT=TO_DATE(TO_CHAR (SYSDATE,'YYYYMMDD'),'YYYYMMDD'),T.T_ACTIVE='失效'WHERE EXISTS(select 1from TEST_SOURCE sWHERE t.s_id=s.s_idAND (T.S_NAME <> S.S_NAME OR T.S_SAL<> S.S_SAL OR T.UPDATE_AT <> S.UPDATE_AT));

SELECT * FROM TEST_TARGET;