递归树的平面化实验

2019-05-25 19:43:59 浏览数 (1)
  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. ***/
  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. );
  21. ALTER TABLE DIM_ORG ADD (PRIMARY KEY (ORG_PK));
  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. );
  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));
  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. );
  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));
  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;
  72. -- 建立平面化存储过程
  73. CREATE OR REPLACE PROCEDURE p_tree_complanate
  74. IS
  75. BEGIN
  76. -- 每次ETL时生成平面化表数据
  77. EXECUTE IMMEDIATE 'truncate table tmp_org_level';
  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);
  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;
  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;
  203. COMMIT;
  204. END;
  205. /
云推荐引擎

0 人点赞