历史拉链表混乱日期跑批的个别数据重跑方案
--接文：《仓库拉链算法的数据恢复机制(重跑中间任意一天保证数据的准确完整性) 》；参考博文地址：http://blog.csdn.net/nsj820/article/details/6096682
本文是在《仓库拉链算法的数据恢复机制(重跑中间任意一天保证数据的准确完整性) 》基础上，对仓库拉链算法的进一步思考；描述了历史拉链表个别数据需要重跑的解决方案。
思考：原文曾说还未考虑用此种方法进行增量数据的拉链处理，现在思考了一下这并不具有可行性；其实此文中所述可以理解为增量处理方式的一种，可以按此文再作些扩充；但因增量源数据传输而产生的拉链断链问题并不能很好的解决，除非设定断链数据跟其前一非空链值相同；但这理解和解释起来好像又不是很通。
1、存储过程P_T100_STATUS_H_one_update代码
create or replace procedure P_T100_STATUS_H_one_update( P_ETLDATE in VARCHAR2, --日期参数 P_KEY in VARCHAR2, --主键数据 O_RUNSTATUS out NUMBER, --执行结果 O_MSG out VARCHAR2) --错误返回 is --定义存储过程信息 V_PROC_NAME VARCHAR2(50) := 'P_T100_STATUS_H'; V_TABLE_NAME VARCHAR2(50) := 'EDW_T100_STATUS_H'; V_START_TIMESTAMP TIMESTAMP; --加载开始时间 V_END_TIMESTAMP TIMESTAMP; --加载结束时间 V_RECORD_NUMBER INTEGER; --记录数 V_KEY VARCHAR2(100) := P_KEY; --定义错误代码，错误状态 V_SQLERRM VARCHAR2(1000); --异常信息 V_ERR_SQL VARCHAR2(1000); --出错位置 BEGIN --捕获过程开始时间 SELECT SYSDATE INTO V_START_TIMESTAMP FROM DUAL; /***************************************************************************************************/ /****************** 数据分链处理 **********************/ /***************************************************************************************************/ --1、处理目标中结束日期为P_ETLDATE和开始日期为P_ETLDATE+1的数据 --(1)、转移目标表中结束日期为P_ETLDATE的数据到TMP_T100_STATUS_H_PRE之前表中 V_ERR_SQL:='转移目标表中结束日期为P_ETLDATE的数据到之前表中'; INSERT INTO TMP_T100_STATUS_H_PRE (Id --ID ,Status --状态 ,Start_Date --开始日期 ,End_Date --结束日期 ) SELECT Id ,Status ,Start_Date ,End_Date FROM EDW_T100_STATUS_H WHERE End_Date = TO_DATE(P_ETLDATE,'YYYY-MM-DD') --结束日期为当天的数据 AND id = V_KEY; --(2)、转移目标表中开始日期为P_ETLDATE+1的数据到TMP_T100_STATUS_H_LAT之后表中 V_ERR_SQL:='转移目标表中开始日期为P_ETLDATE+1的数据到之后表中'; INSERT INTO TMP_T100_STATUS_H_LAT (Id --ID ,Status --状态 ,Start_Date --开始日期 ,End_Date --结束日期 ) SELECT Id ,Status ,Start_Date ,End_Date FROM EDW_T100_STATUS_H WHERE Start_Date = TO_DATE(P_ETLDATE,'YYYY-MM-DD')+1 --开始日期为N+1天的数据 AND id = V_KEY; --2、处理目标表中开始日期与结束日期跨越P_ETLDATE和P_ETLDATE+1的数据(分两部分：一部分转移到之前表；一部分转移到之后表) --(1)、转移到之前表的跨度数据 V_ERR_SQL:='转移到之前表的跨度数据'; INSERT INTO TMP_T100_STATUS_H_PRE (Id --ID ,Status --状态 ,Start_Date --开始日期 ,End_Date --结束日期 ) SELECT Id ,Status ,Start_Date ,TO_DATE(P_ETLDATE,'YYYY-MM-DD') FROM EDW_T100_STATUS_H WHERE Start_Date < TO_DATE(P_ETLDATE,'YYYY-MM-DD') AND End_date >= TO_DATE(P_ETLDATE,'YYYY-MM-DD')+1 --当天日期介于开始日期与结束日期之间的数据 AND id = V_KEY; --(2)、转移到之后表的跨度数据 V_ERR_SQL:='转移到之后表的跨度数据'; INSERT INTO TMP_T100_STATUS_H_LAT (Id --ID ,Status --状态 ,Start_Date --开始日期 ,End_Date --结束日期 ) SELECT Id ,Status ,TO_DATE(P_ETLDATE,'YYYY-MM-DD') + 1 Start_Date ,End_Date FROM EDW_T100_STATUS_H WHERE Start_Date <= TO_DATE(P_ETLDATE,'YYYY-MM-DD') AND End_date > TO_DATE(P_ETLDATE,'YYYY-MM-DD')+1 --当天日期介于开始日期与结束日期之间的数据 AND id = V_KEY; --3、删除目标表中以上4步已经转移了的数据及要被第4步更新的当日数据 V_ERR_SQL:='删除目标表中以上4步已经转移了的数据及要被第4步更新的当日数据'; DELETE FROM EDW_T100_STATUS_H WHERE Start_Date <= TO_DATE(P_ETLDATE,'YYYY-MM-DD')+1 AND End_date >= TO_DATE(P_ETLDATE,'YYYY-MM-DD') AND id = V_KEY; --4、从源表中获取当日数据存放于当前表中(将CUR中的start_date置为当天，end_date置为N+1天) V_ERR_SQL:='从源表中获取当日数据存放于当前表中'; INSERT INTO TMP_T100_STATUS_H_CUR (Id --ID ,Status --状态 ,Start_Date --开始日期 ,End_Date --结束日期 ) SELECT ID ,Status ,TO_DATE(P_ETLDATE,'YYYY-MM-DD') ,TO_DATE(P_ETLDATE,'YYYY-MM-DD')+1 FROM ODS_XT_ZT WHERE Ods_Data_Date = P_ETLDATE AND id = V_KEY; /***************************************************************************************************/ /****************** 数据合链处理 **********************/ /***************************************************************************************************/ --1、之前表与当前表合链处理 --(1)、不可以合链的之前表数据，插入到目标表EDW_T100_STATUS_H V_ERR_SQL:='不可以合链的之前表数据，插入到目标表'; INSERT INTO EDW_T100_STATUS_H --(插入合链不了的之前表数据) (Id --ID ,Status --状态 ,Start_Date --开始日期 ,End_Date --结束日期 ) SELECT A.ID ,A.STATUS ,A.START_DATE ,A.END_DATE FROM TMP_T100_STATUS_H_PRE a LEFT JOIN TMP_T100_STATUS_H_CUR b ON A.ID = B.ID AND A.STATUS = B.STATUS WHERE B.ID IS NULL; --(2)、新当前表==>包含两部分数据：与之前表合链不了的当前表数据；当前表与之前表可以完全合链的数据(结束日期都为P_etldate+1) V_ERR_SQL:='将(与之前表合链不了的当前表数据；当前表与之前表可以完全合链的数据)转移到新当前表中'; INSERT INTO TMP_T100_STATUS_H_NCU --(包含两部分数据：与之前表合链不了的当前表数据；当前表与之前表可以完全合链的数据) (Id --ID ,Status --状态 ,Start_Date --开始日期 ,End_Date --结束日期 ) SELECT B.ID ,B.STATUS ,B.START_DATE ,B.END_DATE --与之前表合链不了的当前表数据 FROM TMP_T100_STATUS_H_PRE A RIGHT JOIN TMP_T100_STATUS_H_CUR B ON A.ID = B.ID AND A.STATUS = B.STATUS WHERE A.ID IS NULL UNION ALL SELECT A.ID ,A.STATUS ,A.START_DATE ,B.END_DATE --当前表与之前表可以完全合链的数据 FROM TMP_T100_STATUS_H_PRE A INNER JOIN TMP_T100_STATUS_H_CUR B ON A.ID = B.ID AND A.STATUS = B.STATUS; --2、新当前表与之前表合链处理 --(1)、新当前表与之后表不能合链的新当前表数据 V_ERR_SQL:='新当前表与之后表不能合链的新当前表数据，插入到目标表'; INSERT INTO EDW_T100_STATUS_H (Id --ID ,Status --状态 ,Start_Date --开始日期 ,End_Date --结束日期 ) SELECT A.ID ,A.STATUS ,A.START_DATE ,A.END_DATE FROM TMP_T100_STATUS_H_NCU A LEFT JOIN TMP_T100_STATUS_H_LAT B ON A.ID = B.ID AND A.STATUS = B.STATUS WHERE B.ID IS NULL; --(2)、新当前表与之后表不能合链的之后表数据 V_ERR_SQL:='新当前表与之后表不能合链的之后表数据，插入到目标表'; INSERT INTO EDW_T100_STATUS_H (Id --ID ,Status --状态 ,Start_Date --开始日期 ,End_Date --结束日期 ) SELECT B.ID ,B.STATUS ,B.START_DATE ,B.END_DATE FROM TMP_T100_STATUS_H_NCU A RIGHT JOIN TMP_T100_STATUS_H_LAT B ON A.ID = B.ID AND A.STATUS = B.STATUS WHERE A.ID IS NULL; --(3)、新当前表与之后表可以完全合链的数据 V_ERR_SQL:='新当前表与之后表可以完全合链的数据，插入到目标表'; INSERT INTO EDW_T100_STATUS_H (Id --ID ,Status --状态 ,Start_Date --开始日期 ,End_Date --结束日期 ) SELECT A.ID ,A.STATUS ,A.START_DATE ,B.END_DATE FROM TMP_T100_STATUS_H_NCU A INNER JOIN TMP_T100_STATUS_H_LAT B ON A.ID = B.ID AND A.STATUS = B.STATUS; --正常处理 V_RECORD_NUMBER := SQL%ROWCOUNT; SELECT SYSDATE INTO V_END_TIMESTAMP FROM DUAL; INSERT INTO EDW_ETL_LOG_DETAIL(START_TIMESTAMP,END_TIMESTAMP,PROC_NAME,TABLE_NAME,ETL_RECORD_NUM,ETL_MEMO,P_ETLDATE) VALUES (V_START_TIMESTAMP,V_END_TIMESTAMP,V_PROC_NAME,V_TABLE_NAME,V_RECORD_NUMBER,'成功',P_ETLDATE); COMMIT; --异常处理 EXCEPTION WHEN OTHERS THEN BEGIN ROLLBACK; V_SQLERRM := SQLERRM; INSERT INTO EDW_ETL_LOG_DETAIL(START_TIMESTAMP,END_TIMESTAMP,PROC_NAME,TABLE_NAME,ETL_RECORD_NUM,ETL_MEMO,ERR_MSG,ERR_SQL,P_ETLDATE) VALUES (V_START_TIMESTAMP,V_END_TIMESTAMP,V_PROC_NAME,V_TABLE_NAME,0,'失败',V_SQLERRM,V_ERR_SQL,P_ETLDATE); O_RUNSTATUS := 1; O_MSG := 'PROGRAMMING ERROR HAPPENED!'; COMMIT; END; END;/
2、几种方式批量调度拉链过程
--1、建参数表CREATE TABLE KEY_PAR_TAB(ID INT,KEY_PAR_1 VARCHAR2(100));INSERT INTO KEY_PAR_TAB(ID,KEY_PAR_1)SELECT 1,'1' FROM DUALUNION ALLSELECT 2,'2' FROM DUALUNION ALLSELECT 3,'3' FROM DUALUNION ALLSELECT 4,'4' FROM DUAL;COMMIT;--2、调度的代码DECLARE CURSOR KEY_PAR_CUR IS SELECT KEY_PAR_1 FROM KEY_PAR_TAB ORDER BY ID; P_ETLDATE VARCHAR2(8) := '20100110'; V_KEY VARCHAR2(100); --定义存储过程信息 O_RUNSTATUS NUMBER; --执行结果 O_MSG VARCHAR2(100); --错误返回BEGIN OPEN KEY_PAR_CUR; FETCH KEY_PAR_CUR INTO V_KEY; WHILE KEY_PAR_CUR%FOUND LOOP BEGIN P_T100_STATUS_H_ONE_UPDATE(P_ETLDATE, V_KEY, O_RUNSTATUS, O_MSG); FETCH KEY_PAR_CUR INTO V_KEY; COMMIT; END; END LOOP; CLOSE KEY_PAR_CUR;END;--3、使用for游标--3.1调度拉链过程,where P_KEY is 连续的数DECLARE P_ETLDATE VARCHAR2(100) := '20100110'; --定义存储过程信息 O_RUNSTATUS NUMBER; --执行结果 O_MSG VARCHAR2(100); --错误返回BEGIN FOR V_KEY IN 2 .. 4 LOOP P_T100_STATUS_H_ONE_UPDATE(P_ETLDATE, V_KEY, O_RUNSTATUS, O_MSG); END LOOP;END;--3.2使用参数存储在表中的隐式游标方式DECLARE P_ETLDATE VARCHAR2(100) := '20100110'; --定义存储过程信息 O_RUNSTATUS NUMBER; --执行结果 O_MSG VARCHAR2(100); --错误返回BEGIN FOR V_KEY IN (SELECT id,KEY_PAR_1 FROM KEY_PAR_TAB/* ORDER BY ID*/) LOOP P_T100_STATUS_H_ONE_UPDATE(P_ETLDATE, V_KEY.KEY_PAR_1, O_RUNSTATUS, O_MSG); END LOOP;END;--注意，在使用此种游标的时候概念不清楚可能就会出错；如在本例中V_KEY是一个游标，而不是游标里面的值；所以存储过程的传入参数要写成V_KEY.KEY_PAR_1的形式。