递归树的平面化实验
- /***
- 已有维度表：
- dim_org -- 组织机构，组织为带有历史信息的递归树，其主键为SEQ_DIM_ORG_PK序列生成的代理键
- dim_person -- 人员表，带历史信息，org_pk关联到dim_org的代理键
- 目的：
- 数据以平面化完整树的形式交付给OLAP工具
- 功能：
- 依照dim_org定义固定的三级组织机构，每个人员关联第三级组织机构，dim_person.org_pk不足三级的补足三级，大于三级的归于第三级
- ***/
- -- 组织机构维度表
- CREATE TABLE DIM_ORG
- (
- ORG_PK NUMBER,
- ORG_NAME VARCHAR2(100 BYTE),
- P_ORG_PK NUMBER,
- EFF_DATE DATE,
- EXP_DATE DATE
- );
- ALTER TABLE DIM_ORG ADD (PRIMARY KEY (ORG_PK));
- -- 人员维度表
- CREATE TABLE DIM_PERSON
- (
- PERSON_PK NUMBER,
- PERSON_NAME VARCHAR2(30 BYTE),
- EFF_DATE DATE,
- EXP_DATE DATE,
- ORG_PK NUMBER
- );
- CREATE INDEX IDX_ORG_PK ON DIM_PERSON (ORG_PK);
- ALTER TABLE DIM_PERSON ADD (PRIMARY KEY (PERSON_PK));
- ALTER TABLE DIM_PERSON ADD (CONSTRAINT DIM_PERSON_R01 FOREIGN KEY (ORG_PK) REFERENCES DIM_ORG (ORG_PK));
- -- 建立组织机构平面化表
- CREATE TABLE tmp_org_level
- (
- org_pk NUMBER NOT NULL,
- org_1_pk NUMBER,
- org_1_name VARCHAR2 (100),
- org_2_pk NUMBER,
- org_2_name VARCHAR2 (100),
- org_3_pk NUMBER,
- org_3_name VARCHAR2 (100)
- );
- CREATE UNIQUE INDEX tmp_org_lavel_pk ON tmp_org_level (org_pk);
- ALTER TABLE tmp_org_level ADD (CONSTRAINT tmp_org_level_pk PRIMARY KEY (org_pk));
- -- 建立人员与组织机构平面化表的关联视图，提供给OLAP工具
- CREATE VIEW v_tree_complanate
- AS
- SELECT person_pk,
- person_name,
- org_1_pk,
- org_1_name,
- org_2_pk,
- org_2_name,
- org_3_pk,
- org_3_name
- FROM dim_person
- JOIN
- tmp_org_level
- ON dim_person.org_pk = tmp_org_level.org_pk;
- -- 建立平面化存储过程
- CREATE OR REPLACE PROCEDURE p_tree_complanate
- IS
- BEGIN
- -- 每次ETL时生成平面化表数据
- EXECUTE IMMEDIATE 'truncate table tmp_org_level';
- INSERT INTO tmp_org_level (org_pk,
- org_1_pk,
- org_1_name,
- org_2_pk,
- org_2_name,
- org_3_pk,
- org_3_name)
- SELECT org_pk,
- SUBSTR (c_pk_path,
- INSTR (c_pk_path,
- '/',
- 1,
- 1)
- + 1,
- INSTR (c_pk_path,
- '/',
- 1,
- 2)
- - INSTR (c_pk_path,
- '/',
- 1,
- 1)
- - 1)
- org_1_pk,
- SUBSTR (c_name_path,
- INSTR (c_name_path,
- '/',
- 1,
- 1)
- + 1,
- INSTR (c_name_path,
- '/',
- 1,
- 2)
- - INSTR (c_name_path,
- '/',
- 1,
- 1)
- - 1)
- org_1_name,
- SUBSTR (c_pk_path,
- INSTR (c_pk_path,
- '/',
- 1,
- 2)
- + 1,
- INSTR (c_pk_path,
- '/',
- 1,
- 3)
- - INSTR (c_pk_path,
- '/',
- 1,
- 2)
- - 1)
- org_2_pk,
- SUBSTR (c_name_path,
- INSTR (c_name_path,
- '/',
- 1,
- 2)
- + 1,
- INSTR (c_name_path,
- '/',
- 1,
- 3)
- - INSTR (c_name_path,
- '/',
- 1,
- 3)
- - 1)
- org_2_name,
- SUBSTR (c_pk_path,
- INSTR (c_pk_path,
- '/',
- 1,
- 3)
- + 1,
- INSTR (c_pk_path,
- '/',
- 1,
- 4)
- - INSTR (c_pk_path,
- '/',
- 1,
- 3)
- - 1)
- org_3_pk,
- SUBSTR (c_name_path,
- INSTR (c_name_path,
- '/',
- 1,
- 3)
- + 1,
- INSTR (c_name_path,
- '/',
- 1,
- 4)
- - INSTR (c_name_path,
- '/',
- 1,
- 3)
- - 1)
- org_3_name
- FROM ( SELECT org_pk,
- org_name,
- SYS_CONNECT_BY_PATH (org_pk, '/') || '/' c_pk_path,
- SYS_CONNECT_BY_PATH (org_name, '/') || '/' c_name_path
- FROM dim_org
- START WITH p_Org_pk IS NULL
- CONNECT BY PRIOR org_pk = p_org_pk);
- -- 补足第二级
- UPDATE tmp_org_level
- SET org_2_pk = SEQ_DIM_ORG_PK.NEXTVAL,
- org_2_name = org_1_name || '本部科室'
- WHERE org_2_pk IS NULL;
- -- 补足第三级
- UPDATE tmp_org_level
- SET org_3_pk = SEQ_DIM_ORG_PK.NEXTVAL,
- org_3_name = org_2_name || '本部小组'
- WHERE org_3_pk IS NULL;
- COMMIT;
- END;
- /
本文参与 腾讯云自媒体分享计划，分享自作者个人站点/博客。
原始发表：2016年12月28日，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读