拉链表的展开算法

在做数据仓库项目的过程中，有时候可能也会根据历史拉链表，展开为每天全量表；相当于一个还原的过程，即构建拉链表的反过程。

1、 建表及插入测试数据语句

1. --建表语句  
   --生成EDW_T00_H表(历史拉链表)  
   -- Create table  
   create table EDW_T00_H  
   (  
     ID         VARCHAR2(2) not null,  
     START_DATE DATE not null,  
     STATUS     VARCHAR2(2),  
     END_DATE   DATE  
   );  
              

   -- Add comments to the table   
   comment on table EDW_T00_H  
     is '历史拉链表';  
   -- Add comments to the columns   
   comment on column EDW_T00_H.ID  
     is 'ID';  
   comment on column EDW_T00_H.START_DATE  
     is '开始日期';  
   comment on column EDW_T00_H.STATUS  
     is '状态';  
   comment on column EDW_T00_H.END_DATE  
     is '结束日期';  
              

   -- Create/Recreate primary, unique and foreign key constraints   
   alter table EDW_T00_H  
     add constraint EDW_T00_ID primary key (ID, START_DATE);  
   --生成EDW_T00_ALL表(历史拉链展开后的表)  
   -- Create table  
   create table EDW_T00_ALL  
   (  
     ID        VARCHAR2(2) not null,  
     DATA_DATE DATE not null,  
     STATUS    VARCHAR2(2) not null  
   );  
              

   -- Add comments to the table   
   comment on table EDW_T00_ALL  
     is '历史拉链展开后的表';  
   -- Add comments to the columns   
   comment on column EDW_T00_ALL.ID  
     is 'ID';  
   comment on column EDW_T00_ALL.DATA_DATE  
     is '数据日期';  
   comment on column EDW_T00_ALL.STATUS  
     is '状态';  
              

   --插入历史表测试数据  
   insert into edw_t00_H (ID, START_DATE, STATUS, END_DATE)  
   values ('3', to_date('01-12-2007', 'dd-mm-yyyy'), 'N', to_date('01-12-2009', 'dd-mm-yyyy'));  
   insert into edw_t00_H (ID, START_DATE, STATUS, END_DATE)  
   values ('3', to_date('01-12-2009', 'dd-mm-yyyy'), 'Y', to_date('16-12-2010', 'dd-mm-yyyy'));  
   insert into edw_t00_H (ID, START_DATE, STATUS, END_DATE)  
   values ('1', to_date('01-12-2010', 'dd-mm-yyyy'), 'Y', to_date('16-12-2010', 'dd-mm-yyyy'));  
   insert into edw_t00_H (ID, START_DATE, STATUS, END_DATE)  
   values ('2', to_date('01-12-2010', 'dd-mm-yyyy'), 'N', to_date('12-12-2010', 'dd-mm-yyyy'));  
   COMMIT;  
              

2、 插入系统维护日期表

主要用来取ROWNUM供第3步操作

--插入系统维护时间  
CREATE TABLE SYS_MATIAN_DATE(DATE_COL DATE);  
  
DECLARE  
  V_DATE     DATE := TO_DATE('20080101', 'YYYYMMDD');  
  V_DATE_MAX DATE;  
BEGIN  
  EXECUTE IMMEDIATE 'TRUNCATE TABLE SYS_MATIAN_DATE';  
  SELECT SYSDATE INTO V_DATE_MAX FROM DUAL;  
  WHILE V_DATE <= V_DATE_MAX LOOP  
    INSERT INTO SYS_MATIAN_DATE  
      SELECT V_DATE FROM DUAL;  
    V_DATE := V_DATE + 1;  
    COMMIT;  
  END LOOP;  
END;  
  
SELECT * FROM SYS_MATIAN_DATE;  
           

3、 实现拉链表展开的代码

1. DECLARE  
     CURSOR LSLL_ID IS  
       SELECT ID,  
              START_DATE,  
              STATUS,  
              DECODE(END_DATE,  
                     TO_DATE('30001231', 'YYYYMMDD'),  
                     SYSDATE,      --TO_DATE(P_ETLDATE, 'YYYYMMDD'),  
                     END_DATE) END_DATE  
         FROM EDW_T00_H;  
     V_ID         VARCHAR2(1000);  
     V_START_DATE DATE;  
     V_END_DATE   DATE;  
     V_DATA_DATE  DATE;  
     V_STATUS     VARCHAR2(2);  
   BEGIN  
     BEGIN  
       EXECUTE IMMEDIATE 'TRUNCATE TABLE EDW_T00_ALL';  
       OPEN LSLL_ID;  
       LOOP  
         FETCH LSLL_ID  
           INTO V_ID, V_START_DATE, V_STATUS, V_END_DATE;  
         EXIT WHEN LSLL_ID%NOTFOUND;  
         INSERT INTO EDW_T00_ALL  
           (ID, DATA_DATE, STATUS)  
           SELECT V_ID, V_START_DATE + ROWNUM - 1 AS DATA_DATE, V_STATUS  
             FROM SYS_MATIAN_DATE  
            WHERE V_START_DATE + ROWNUM - 1 < V_END_DATE;  
         COMMIT;  
       END LOOP;  
       CLOSE LSLL_ID;  
     END;  
   END;  
              

4、 总结说明

第3步中，游标的START_DATE、END_DATE要根据实际情况，灵活变通；代码的EXECUTE IMMEDIATE 'TRUNCATE TABLE EDW_T00_ALL';需要不需要，根据实际情况来定；第2步中生成数据日期维护表，主要用来第3步

SELECT V_ID, V_START_DATE + ROWNUM - 1 AS DATA_DATE, V_STATUS

          FROM SYS_MATIAN_DATE

         WHERE V_START_DATE + ROWNUM - 1 < V_END_DATE;

取数用，目的是保证SYS_MATIAN_DATE表有足够的记录，以使对于每一条历史拉链表中的记录都能取得到；本文给出了我们进行拉链表展开时的一个思路。