缓慢变化维程序

create or replace procedure p_abc_dim_dept(p_dt       date default sysdate, --to日期

                                           v_init_flg number default 0 --1：初始化 0：非初始化

                                           ) is

  /*************************************************************

   author  : blt

   created : 2019-05-04

   purpose :

   version  modify  time        desc

   -------  -----   ----------  -------------------------------

   v1.0     blt     2019-05-04  生成机构维表

  **************************************************************/

  v_sqlstate  varchar2(1000);

  v_proc_name varchar2(300);

  v_rowcount  number(12);

  --自定义变量

  v_fm_date   date;

  v_max_sgkey number;

begin

  v_sqlstate  := '变量赋值';

  v_proc_name := 'P_ABC_DIM_DEPT';

  v_fm_date   := p_dt;

  v_sqlstate := '清空临时表';

  execute immediate 'TRUNCATE TABLE ABC_DIM_DEPT_TMP01';

  execute immediate 'TRUNCATE TABLE ABC_DIM_DEPT_TMP02';

  execute immediate 'TRUNCATE TABLE ABC_DIM_DEPT_TMP03';

  v_sqlstate := '初始化删除工作';

  if v_init_flg = 1 then

    delete abc_dim_dept;

  end if;

  v_sqlstate := '得到ASU机构路径';

  insert into abc_dim_dept_tmp01

    select dept_code,

           dept_name,

           parent_dept_code,

           level type_level,

           sys_connect_by_path(dept_code, '@') || '@' code_path,

           sys_connect_by_path(dept_name, '@') || '@' name_path

      from (select a.dept_code, a.dept_name, a.parent_dept_code

              from t_ods_dept a) t

     start with parent_dept_code is null --从集团往下找

    connect by parent_dept_code = prior dept_code;

  v_sqlstate := '得到机构遍平化';

  insert into abc_dim_dept_tmp02

    select a.code_path,

           nvl(regexp_substr(code_path, '[^@]+', 1, type_level), '') dept_code,

           nvl(regexp_substr(name_path, '[^@]+', 1, type_level), '') dept_name,

           case

             when v_init_flg = 1 then

              date '1993-03-01' --初始化时初始机构起始时间改创建日期当月第一天

             else

              v_fm_date

           end fm_tm, --起始时间

           to_date('9999-12-31', 'YYYY-MM-DD') to_tm,

           nvl(regexp_substr(code_path, '[^@]+', 1, 1),

               nvl(regexp_substr(code_path, '[^@]+', 1, type_level), '')) level1_code,

           nvl(regexp_substr(name_path, '[^@]+', 1, 1),

               nvl(regexp_substr(name_path, '[^@]+', 1, type_level), '')) level1_name,

           nvl(regexp_substr(code_path, '[^@]+', 1, 2),

               nvl(regexp_substr(code_path, '[^@]+', 1, type_level), '')) level2_code,

           nvl(regexp_substr(name_path, '[^@]+', 1, 2),

               nvl(regexp_substr(name_path, '[^@]+', 1, type_level), '')) level2_name,

           nvl(regexp_substr(code_path, '[^@]+', 1, 3),

               nvl(regexp_substr(code_path, '[^@]+', 1, type_level), '')) level3_code,

           nvl(regexp_substr(name_path, '[^@]+', 1, 3),

               nvl(regexp_substr(name_path, '[^@]+', 1, type_level), '')) level3_name,

           nvl(regexp_substr(code_path, '[^@]+', 1, 4),

               nvl(regexp_substr(code_path, '[^@]+', 1, type_level), '')) level4_code,

           nvl(regexp_substr(name_path, '[^@]+', 1, 4),

               nvl(regexp_substr(name_path, '[^@]+', 1, type_level), '')) level4_name,

           nvl(regexp_substr(code_path, '[^@]+', 1, 5),

               nvl(regexp_substr(code_path, '[^@]+', 1, type_level), '')) level5_code,

           nvl(regexp_substr(name_path, '[^@]+', 1, 5),

               nvl(regexp_substr(name_path, '[^@]+', 1, type_level), '')) level5_name,

           a.parent_dept_code

      from abc_dim_dept_tmp01 a;

  v_sqlstate := '取阿修罗与ABC机构表最大代理键';

  select nvl(max(dept_id), 0) into v_max_sgkey from abc_dim_dept;

  insert into abc_dim_dept_tmp03

    select nvl(b.dept_id,

               v_max_sgkey + row_number()

               over(partition by b.dept_id order by b.dept_id)) dept_id, --对新状态记录分配新代理键

           a.dept_code,

           a.dept_name,

           a.fm_tm,

           a.to_tm,

           a.level1_code,

           a.level1_name,

           a.level2_code,

           a.level2_name,

           a.level3_code,

           a.level3_name,

           a.level4_code,

           a.level4_name,

           a.level5_code,

           a.level5_name,

           a.parent_dept_code

      from abc_dim_dept_tmp02 a

      left join abc_dim_dept b

        on a.dept_code || a.dept_name || a.level1_code || a.level1_name ||

           a.level2_code || a.level2_name || a.level3_code || a.level3_name ||

           a.level4_code || a.level4_name || a.level5_code || a.level5_name ||

           a.parent_dept_code =

           b.dept_code || b.dept_name || b.level1_code || b.level1_name ||

           b.level2_code || b.level2_name || b.level3_code || b.level3_name ||

           b.level4_code || b.level4_name || b.level5_code || b.level5_name ||

           b.parent_dept_code

       and b.to_tm = date '9999-12-31';

  v_sqlstate := '更新有变更的阿修罗与ABC机构日期到前一天';

  update abc_dim_dept a

     set a.to_tm = v_fm_date - 1, load_tm = sysdate

   where a.to_tm = date

   '9999-12-31'

     and a.dept_code in (select b.dept_code

                           from abc_dim_dept_tmp03 b

                          where b.dept_id > v_max_sgkey);

  commit;

  v_sqlstate := '插入新阿修罗与ABC机构数据';

  insert into abc_dim_dept

    (dept_id,

     dept_code,

     dept_name,

     fm_tm,

     to_tm,

     level1_code,

     level1_name,

     level2_code,

     level2_name,

     level3_code,

     level3_name,

     level4_code,

     level4_name,

     level5_code,

     level5_name,

     parent_dept_code,

     load_tm)

    select dept_id,

           dept_code,

           dept_name,

           fm_tm,

           to_tm,

           level1_code,

           level1_name,

           level2_code,

           level2_name,

           level3_code,

           level3_name,

           level4_code,

           level4_name,

           level5_code,

           level5_name,

           parent_dept_code,

           sysdate load_tm

      from abc_dim_dept_tmp03 a

     where a.dept_id > v_max_sgkey;

  commit;

  v_sqlstate := '删除ASU_DEPT无效部门数据';

  delete abc_dim_dept a where a.fm_tm > a.to_tm;

  commit;

  v_sqlstate := '结束';

exception

  when others then

    rollback;

    commit;

  

end p_abc_dim_dept;

/

---------------------------------------------------------------------------------------------------------

--创建源头机构表

create table t_ods_dept

(

id int,

dept_code varchar2(10),

dept_name varchar2(100),

parent_dept_code varchar2(10)

)

--生成数据

insert into t_ods_dept values(1,'001','总部',null);

insert into t_ods_dept values(2,'00101','华南','001');

insert into t_ods_dept values(3,'00102','华北','001');

insert into t_ods_dept values(4,'00103','华西','001');

insert into t_ods_dept values(5,'0010101','深圳','00101');

insert into t_ods_dept values(6,'0010102','广州','00101');

insert into t_ods_dept values(7,'0010103','东莞','00101');

insert into t_ods_dept values(8,'0010201','北京','00102');

insert into t_ods_dept values(9,'0010202','天津','00102');

insert into t_ods_dept values(10,'0010301','重庆','00103');

commit;

--初始化机构表

begina

  -- Call the procedure

  p_abc_dim_dept(date'2019-05-01',1);

end;

select * from abc_dim_dept;

--5月10号把东莞机构划到了华北地区。

update t_ods_dept a set a.parent_dept_code='00102' where a.dept_code='0010103';

begin

  -- Call the procedure

  p_abc_dim_dept(date'2019-05-10',0);

end;

select * from abc_dim_dept;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31535951/viewspace-2644364/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31535951/viewspace-2644364/