递归树的平面化实验

1. /***  
2. 已有维度表：  
3. dim_org    -- 组织机构，组织为带有历史信息的递归树，其主键为SEQ_DIM_ORG_PK序列生成的代理键  
4. dim_person -- 人员表，带历史信息，org_pk关联到dim_org的代理键  
5. 目的：  
6. 数据以平面化完整树的形式交付给OLAP工具  
7. 功能：  
8. 依照dim_org定义固定的三级组织机构，每个人员关联第三级组织机构，dim_person.org_pk不足三级的补足三级，大于三级的归于第三级  
9. ***/  
10.   
11. -- 组织机构维度表  
12. CREATE TABLE DIM_ORG  
13. (  
14.   ORG_PK    NUMBER,  
15.   ORG_NAME  VARCHAR2(100 BYTE),  
16.   P_ORG_PK  NUMBER,  
17.   EFF_DATE  DATE,  
18.   EXP_DATE  DATE  
19. );  
20.   
21. ALTER TABLE DIM_ORG ADD (PRIMARY KEY (ORG_PK));  
22.   
23.   
24. -- 人员维度表  
25. CREATE TABLE DIM_PERSON  
26. (  
27.   PERSON_PK    NUMBER,  
28.   PERSON_NAME  VARCHAR2(30 BYTE),  
29.   EFF_DATE     DATE,  
30.   EXP_DATE     DATE,  
31.   ORG_PK       NUMBER  
32. );  
33.   
34. CREATE INDEX IDX_ORG_PK ON DIM_PERSON (ORG_PK);  
35. ALTER TABLE DIM_PERSON ADD (PRIMARY KEY (PERSON_PK));  
36. ALTER TABLE DIM_PERSON ADD (CONSTRAINT DIM_PERSON_R01 FOREIGN KEY (ORG_PK) REFERENCES DIM_ORG (ORG_PK));  
37.   
38.   
39. -- 建立组织机构平面化表  
40. CREATE TABLE tmp_org_level  
41. (  
42.    org_pk       NUMBER NOT NULL,  
43.    org_1_pk     NUMBER,  
44.    org_1_name   VARCHAR2 (100),  
45.    org_2_pk     NUMBER,  
46.    org_2_name   VARCHAR2 (100),  
47.    org_3_pk     NUMBER,  
48.    org_3_name   VARCHAR2 (100)  
49. );  
50.   
51. CREATE UNIQUE INDEX tmp_org_lavel_pk ON tmp_org_level (org_pk);  
52. ALTER TABLE tmp_org_level ADD (CONSTRAINT tmp_org_level_pk PRIMARY KEY (org_pk));  
53.   
54.   
55. -- 建立人员与组织机构平面化表的关联视图，提供给OLAP工具  
56. CREATE VIEW v_tree_complanate  
57. AS  
58.    SELECT person_pk,  
59.           person_name,  
60.           org_1_pk,  
61.           org_1_name,  
62.           org_2_pk,  
63.           org_2_name,  
64.           org_3_pk,  
65.           org_3_name  
66.      FROM    dim_person  
67.           JOIN  
68.              tmp_org_level  
69.           ON dim_person.org_pk = tmp_org_level.org_pk;  
70.   
71.   
72. -- 建立平面化存储过程  
73. CREATE OR REPLACE PROCEDURE p_tree_complanate  
74. IS  
75. BEGIN  
76.    -- 每次ETL时生成平面化表数据  
77.    EXECUTE IMMEDIATE 'truncate table tmp_org_level';  
78.   
79.    INSERT INTO tmp_org_level (org_pk,  
80.                               org_1_pk,  
81.                               org_1_name,  
82.                               org_2_pk,  
83.                               org_2_name,  
84.                               org_3_pk,  
85.                               org_3_name)  
86.       SELECT org_pk,  
87.              SUBSTR (c_pk_path,  
88.                        INSTR (c_pk_path,  
89.                               '/',  
90.                               1,  
91.                               1)  
92.                      + 1,  
93.                        INSTR (c_pk_path,  
94.                               '/',  
95.                               1,  
96.                               2)  
97.                      - INSTR (c_pk_path,  
98.                               '/',  
99.                               1,  
100.                               1)  
101.                      - 1)  
102.                 org_1_pk,  
103.              SUBSTR (c_name_path,  
104.                        INSTR (c_name_path,  
105.                               '/',  
106.                               1,  
107.                               1)  
108.                      + 1,  
109.                        INSTR (c_name_path,  
110.                               '/',  
111.                               1,  
112.                               2)  
113.                      - INSTR (c_name_path,  
114.                               '/',  
115.                               1,  
116.                               1)  
117.                      - 1)  
118.                 org_1_name,  
119.              SUBSTR (c_pk_path,  
120.                        INSTR (c_pk_path,  
121.                               '/',  
122.                               1,  
123.                               2)  
124.                      + 1,  
125.                        INSTR (c_pk_path,  
126.                               '/',  
127.                               1,  
128.                               3)  
129.                      - INSTR (c_pk_path,  
130.                               '/',  
131.                               1,  
132.                               2)  
133.                      - 1)  
134.                 org_2_pk,  
135.              SUBSTR (c_name_path,  
136.                        INSTR (c_name_path,  
137.                               '/',  
138.                               1,  
139.                               2)  
140.                      + 1,  
141.                        INSTR (c_name_path,  
142.                               '/',  
143.                               1,  
144.                               3)  
145.                      - INSTR (c_name_path,  
146.                               '/',  
147.                               1,  
148.                               3)  
149.                      - 1)  
150.                 org_2_name,  
151.              SUBSTR (c_pk_path,  
152.                        INSTR (c_pk_path,  
153.                               '/',  
154.                               1,  
155.                               3)  
156.                      + 1,  
157.                        INSTR (c_pk_path,  
158.                               '/',  
159.                               1,  
160.                               4)  
161.                      - INSTR (c_pk_path,  
162.                               '/',  
163.                               1,  
164.                               3)  
165.                      - 1)  
166.                 org_3_pk,  
167.              SUBSTR (c_name_path,  
168.                        INSTR (c_name_path,  
169.                               '/',  
170.                               1,  
171.                               3)  
172.                      + 1,  
173.                        INSTR (c_name_path,  
174.                               '/',  
175.                               1,  
176.                               4)  
177.                      - INSTR (c_name_path,  
178.                               '/',  
179.                               1,  
180.                               3)  
181.                      - 1)  
182.                 org_3_name  
183.         FROM (    SELECT org_pk,  
184.                          org_name,  
185.                          SYS_CONNECT_BY_PATH (org_pk, '/') || '/' c_pk_path,  
186.                          SYS_CONNECT_BY_PATH (org_name, '/') || '/' c_name_path  
187.                     FROM dim_org  
188.               START WITH p_Org_pk IS NULL  
189.               CONNECT BY PRIOR org_pk = p_org_pk);  
190.   
191.    -- 补足第二级  
192.    UPDATE tmp_org_level  
193.       SET org_2_pk = SEQ_DIM_ORG_PK.NEXTVAL,  
194.           org_2_name = org_1_name || '本部科室'  
195.     WHERE org_2_pk IS NULL;  
196.   
197.    -- 补足第三级  
198.    UPDATE tmp_org_level  
199.       SET org_3_pk = SEQ_DIM_ORG_PK.NEXTVAL,  
200.           org_3_name = org_2_name || '本部小组'  
201.     WHERE org_3_pk IS NULL;  
202.   
203.    COMMIT;  
204. END;  
205. /