在ETL过程中对递归树的历史维护实验

来源：互联网发布：淘宝韩国正品女装编辑：程序博客网时间：2024/06/05 05:14

-- 当前递归树
CREATE TABLE TREE_CUR
(
C_CHILD VARCHAR2(32 BYTE),
C_NAME VARCHAR2(100 BYTE),
C_PARENT VARCHAR2(32 BYTE)
);
CREATE INDEX IDX1 ON TREE_CUR (C_PARENT);
CREATE UNIQUE INDEX TREE_CUR_PK ON TREE_CUR (C_CHILD);
ALTER TABLE TREE_CUR ADD (CONSTRAINT TREE_CUR_PK PRIMARY KEY (C_CHILD));
ALTER TABLE TREE_CUR ADD (CONSTRAINT TREE_CUR_R01 FOREIGN KEY (C_PARENT) REFERENCES TREE_CUR (C_CHILD));
Insert into TREE_CUR (C_CHILD, C_NAME, C_PARENT) Values ('A', '节点A', NULL);
Insert into TREE_CUR (C_CHILD, C_NAME, C_PARENT) Values ('B', '节点B', 'A');
Insert into TREE_CUR (C_CHILD, C_NAME, C_PARENT) Values ('C', '节点C', 'A');
Insert into TREE_CUR (C_CHILD, C_NAME, C_PARENT) Values ('D', '节点D', 'A');
Insert into TREE_CUR (C_CHILD, C_NAME, C_PARENT) Values ('E', '节点E', 'B');
Insert into TREE_CUR (C_CHILD, C_NAME, C_PARENT) Values ('F', '节点F', 'B');
Insert into TREE_CUR (C_CHILD, C_NAME, C_PARENT) Values ('G', '节点G', 'C');
Insert into TREE_CUR (C_CHILD, C_NAME, C_PARENT) Values ('H', '节点H', 'C');
Insert into TREE_CUR (C_CHILD, C_NAME, C_PARENT) Values ('I', '节点I', 'C');
Insert into TREE_CUR (C_CHILD, C_NAME, C_PARENT) Values ('J', '节点J', 'D');
Insert into TREE_CUR (C_CHILD, C_NAME, C_PARENT) Values ('K', '节点K', 'D');
COMMIT;
-- 递归历史树
CREATE TABLE TREE_HIS
(
ID NUMBER,
C_CHILD VARCHAR2(32 BYTE),
C_NAME VARCHAR2(100 BYTE),
P_ID NUMBER,
EFF_DATE DATE,
EXP_DATE DATE
);
CREATE INDEX IDX2 ON TREE_HIS (P_ID);
CREATE UNIQUE INDEX IDX3 ON TREE_HIS (C_CHILD, EXP_DATE);
CREATE UNIQUE INDEX TREE_HIS_PK ON TREE_HIS (ID);
ALTER TABLE TREE_HIS ADD (CONSTRAINT TREE_HIS_PK PRIMARY KEY (ID));
ALTER TABLE TREE_HIS ADD (CONSTRAINT TREE_HIS_R01 FOREIGN KEY (P_ID) REFERENCES TREE_HIS (ID));
-- 建立更新递归历史树数据的存储过程
CREATE OR REPLACE PROCEDURE p_tree_his_upd
IS
l_id tree_his.id%TYPE;
l_c_name tree_his.c_name%TYPE;
l_p_id tree_his.p_id%TYPE;
l_exp_date tree_his.exp_date%TYPE;
l_max_date DATE := TO_DATE ('9999-12-31', 'yyyy-mm-dd');
l_sysdate DATE := SYSDATE;
BEGIN
-- 对当前树中已删除的节点，则历史树当前版本中以此节点为根的子树都过期
FOR i
IN ( SELECT id
FROM tree_his
WHERE exp_date = l_max_date
AND c_child NOT IN (SELECT c_child FROM tree_cur)
ORDER BY id)
LOOP
SELECT exp_date
INTO l_exp_date
FROM tree_his
WHERE id = i.id;
IF l_exp_date = l_max_date
THEN -- 避免重复更新
UPDATE tree_his
SET exp_date = l_sysdate
WHERE id IN ( SELECT id
FROM tree_his
WHERE exp_date = l_max_date
START WITH id = i.id
CONNECT BY PRIOR id = p_id);
END IF;
END LOOP;
-- 遍历当前树
FOR x IN ( SELECT c_child, c_name, c_parent
FROM tree_cur
START WITH c_parent IS NULL
CONNECT BY PRIOR c_child = c_parent)
LOOP
-- 根据c_child查找历史树的当前版本
BEGIN
SELECT id, c_name, p_id
INTO l_id, l_c_name, l_p_id
FROM tree_his
WHERE exp_date = l_max_date AND c_child = x.c_child;
IF l_c_name != x.c_name
THEN -- 属性（这里属性只有名字）改变，则历史树当前版本中以此节点为根的子树都过期
FOR y IN ( SELECT id,
c_child,
c_name,
p_id
FROM tree_his
WHERE exp_date = l_max_date
START WITH id = l_id
CONNECT BY PRIOR id = p_id)
LOOP
IF y.id = l_id
THEN -- 子树的根，p_id不变
l_p_id := y.p_id;
ELSE
SELECT id
INTO l_p_id
FROM tree_his
WHERE exp_date = l_max_date
AND c_child = (SELECT c_child
FROM tree_his
WHERE id = y.p_id);
END IF;
BEGIN
SELECT c_name
INTO l_c_name
FROM tree_cur
WHERE c_child = y.c_child;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
l_c_name := y.c_name;
END;
UPDATE tree_his
SET exp_date = l_sysdate
WHERE id = y.id;
INSERT INTO TREE_HIS (ID,
C_CHILD,
C_NAME,
P_ID,
EFF_DATE,
EXP_DATE)
VALUES (seq_tree_his.NEXTVAL,
y.c_child,
l_c_name,
l_p_id,
l_sysdate,
l_max_date);
END LOOP;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN -- 新增节点，增加整颗子树，新增子树中的节点在原历史树中都过期
FOR y IN ( SELECT c_child, c_name, c_parent
FROM tree_cur
START WITH c_child = x.c_child
CONNECT BY PRIOR c_child = c_parent)
LOOP
IF y.c_child = x.c_child
THEN
BEGIN -- 子树的根
SELECT id
INTO l_p_id
FROM tree_his
WHERE exp_date = l_max_date AND c_child = y.c_parent;
EXCEPTION
WHEN NO_DATA_FOUND
THEN --新增根节点，重构整棵树
l_p_id := NULL;
END;
ELSE
SELECT id
INTO l_p_id
FROM tree_his
WHERE exp_date = l_max_date
AND c_child = (SELECT c_parent
FROM tree_cur
WHERE c_child = y.c_child);
END IF;
UPDATE tree_his
SET exp_date = l_sysdate
WHERE exp_date = l_max_date AND c_child = y.c_child;
INSERT INTO TREE_HIS (ID,
C_CHILD,
C_NAME,
P_ID,
EFF_DATE,
EXP_DATE)
VALUES (seq_tree_his.NEXTVAL,
y.c_child,
y.c_name,
l_p_id,
l_sysdate,
l_max_date);
END LOOP;
END;
END LOOP;
COMMIT;
END;
/
-- 测试
/*** 加载初始树，执行三次过程，查看结果 ***/
EXEC p_tree_his_upd;
EXEC p_tree_his_upd;
EXEC p_tree_his_upd;
SELECT * FROM tree_his;
SELECT *
FROM tree_his
WHERE exp_date = TO_DATE ('9999-12-31', 'yyyy-mm-dd')
START WITH p_id IS NULL
CONNECT BY PRIOR id = p_id;
/*** 修改当前递归树的名称列，执行三次过程，查看结果（修改名称） ***/
UPDATE tree_cur SET c_name = '节点A1' WHERE c_child = 'A';
UPDATE tree_cur SET c_name = '节点B1' WHERE c_child = 'B';
UPDATE tree_cur SET c_name = '节点C1' WHERE c_child = 'C';
UPDATE tree_cur SET c_name = '节点D1' WHERE c_child = 'D';
UPDATE tree_cur SET c_name = '节点E1' WHERE c_child = 'E';
UPDATE tree_cur SET c_name = '节点F1' WHERE c_child = 'F';
UPDATE tree_cur SET c_name = '节点G1' WHERE c_child = 'G';
UPDATE tree_cur SET c_name = '节点H1' WHERE c_child = 'H';
UPDATE tree_cur SET c_name = '节点I1' WHERE c_child = 'I';
UPDATE tree_cur SET c_name = '节点J1' WHERE c_child = 'J';
UPDATE tree_cur SET c_name = '节点K1' WHERE c_child = 'K';
COMMIT;
EXEC p_tree_his_upd;
EXEC p_tree_his_upd;
EXEC p_tree_his_upd;
SELECT * FROM tree_his;
SELECT *
FROM tree_his
WHERE exp_date = TO_DATE ('9999-12-31', 'yyyy-mm-dd')
START WITH p_id IS NULL
CONNECT BY PRIOR id = p_id;
/*** 修改名称 ***/
UPDATE tree_cur SET c_name = '节点A2' WHERE c_child = 'A';
UPDATE tree_cur SET c_name = '节点C2' WHERE c_child = 'C';
UPDATE tree_cur SET c_name = '节点E2' WHERE c_child = 'E';
UPDATE tree_cur SET c_name = '节点H2' WHERE c_child = 'H';
UPDATE tree_cur SET c_name = '节点K2' WHERE c_child = 'K';
COMMIT;
EXEC p_tree_his_upd;
EXEC p_tree_his_upd;
EXEC p_tree_his_upd;
SELECT * FROM tree_his;
SELECT *
FROM tree_his
WHERE exp_date = TO_DATE ('9999-12-31', 'yyyy-mm-dd')
START WITH p_id IS NULL
CONNECT BY PRIOR id = p_id;
/*** 修改名称 ***/
UPDATE tree_cur SET c_name = '节点B2' WHERE c_child = 'B';
UPDATE tree_cur SET c_name = '节点C3' WHERE c_child = 'C';
COMMIT;
EXEC p_tree_his_upd;
EXEC p_tree_his_upd;
EXEC p_tree_his_upd;
SELECT * FROM tree_his;
SELECT *
FROM tree_his
WHERE exp_date = TO_DATE ('9999-12-31', 'yyyy-mm-dd')
START WITH p_id IS NULL
CONNECT BY PRIOR id = p_id;
/*** 增加新的根节点，并改变原来的父子关系 ***/
INSERT INTO tree_cur VALUES ('X', '节点X', NULL);
INSERT INTO tree_cur VALUES ('Y', '节点Y', 'X');
UPDATE tree_cur SET c_parent = 'X' WHERE c_child = 'A';
UPDATE tree_cur SET c_parent = 'Y' WHERE c_child = 'C';
COMMIT;
EXEC p_tree_his_upd;
EXEC p_tree_his_upd;
EXEC p_tree_his_upd;
SELECT * FROM tree_his;
SELECT *
FROM tree_his
WHERE exp_date = TO_DATE ('9999-12-31', 'yyyy-mm-dd')
START WITH p_id IS NULL
CONNECT BY PRIOR id = p_id;
/*** 删除子树 ***/
DELETE FROM tree_cur WHERE c_child = 'J';
DELETE FROM tree_cur WHERE c_child = 'K';
DELETE FROM tree_cur WHERE c_child = 'D';
COMMIT;
EXEC p_tree_his_upd;
EXEC p_tree_his_upd;
EXEC p_tree_his_upd;
SELECT * FROM tree_his;
SELECT *
FROM tree_his
WHERE exp_date = TO_DATE ('9999-12-31', 'yyyy-mm-dd')
START WITH p_id IS NULL
CONNECT BY PRIOR id = p_id;

0 0