缓慢变化维程序

最新推荐文章于 2024-12-01 22:19:41 发布

转载最新推荐文章于 2024-12-01 22:19:41 发布 · 176 阅读

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/