Oracle中两种树表现形式数据互相转换的存储过程

　　作者：wallimn(http://wallimn.iteye.com)　时间：2010-10-22
　　本人原创，欢迎转载，转载请保留本人信息。

　　数据库中表现层次关系（树型结构）有两种方式：一种是使用每级定长的代码，一种是使用父子节点关系。表中的数据如下图所示：

　　今天写了一个两种表现形式代码互相转换的存储过程。发布一下，希望对类似需求的朋友有所帮助。

--包头
CREATE OR REPLACE PACKAGE tree
AS
   --两种树层次关系表现形式转换
   --by wallimn, 2010-10-22
   
   --层次码转父子节点表示法
   --p_len：层次码每级的长度
   PROCEDURE code2id (p_len IN NUMBER);

   --由父节点ID、当前节点ID表现的层关系生成单一的层次代码
   --p_startNodeId：树的起点
   --层次码的长度
   PROCEDURE id2code (p_startnodeid IN NUMBER, p_len IN NUMBER);
END tree;
/
--包体
CREATE OR REPLACE PACKAGE BODY tree
AS
/*
--数据处理表，用于存放待处理的数据及处理结果。
create table t_wp_t_tree
(
  nodeid   number,
  nodepid  number,
  code     varchar2(255 byte),
  name     varchar2(255 byte)
);
comment on table t_wp_t_tree is '树两种表示形式转换的临时表';
alter table t_wp_t_tree add (
  unique (code),
  unique (nodeid));
*/
--层次码转父子节点表示法
--p_len：层次码每级的长度
   PROCEDURE code2id (p_len IN NUMBER)
   IS
      TYPE nodeid_table_type IS TABLE OF t_wp_t_tree.nodeid%TYPE
         INDEX BY BINARY_INTEGER;

      TYPE code_table_type IS TABLE OF t_wp_t_tree.code%TYPE
         INDEX BY BINARY_INTEGER;

      nodeid_table   nodeid_table_type;
      code_table     code_table_type;
   BEGIN
      --使用批量操作的方式来处理，效率比较好。
      SELECT ROW_NUMBER () OVER (ORDER BY code) * 10 nodeid, code
      BULK COLLECT INTO nodeid_table, code_table
        FROM t_wp_t_tree;

      --去除可能的干扰
      UPDATE t_wp_t_tree
         SET nodeid = NULL,
             nodepid = NULL;

      --批量更新当前节点的ID
      FORALL i IN 1 .. code_table.COUNT
         UPDATE t_wp_t_tree
            SET nodeid = nodeid_table (i)
          WHERE code = code_table (i);

      --再查找更新父节点的ID
      UPDATE t_wp_t_tree t
         SET nodepid =
                  (SELECT x.nodeid
                     FROM t_wp_t_tree x
                    WHERE x.code = SUBSTR (t.code, 1, LENGTH (t.code) - p_len));
   EXCEPTION
      WHEN OTHERS
      THEN
         RAISE;
   END;

--由父节点ID、当前节点ID表现的层关系生成单一的层次代码
--p_startNodeId：树的起点
--层次码的长度
   PROCEDURE id2code (p_startnodeid IN NUMBER, p_len IN NUMBER)
   IS
      tmpvar         NUMBER;
      v_level        NUMBER;
      v_num          NUMBER;
      v_parentcode   t_wp_t_tree.code%TYPE;
   BEGIN
      v_level := 0;

      FOR rec_item IN (SELECT     nodeid, nodepid, LEVEL
                             FROM t_wp_t_tree
                       START WITH nodeid = p_startnodeid
                       CONNECT BY PRIOR nodeid = nodepid
                         ORDER BY LEVEL, nodeid)
      LOOP
         IF (v_level <> rec_item.LEVEL)
         THEN
            v_num := POWER (10, p_len - 1);
            v_level := rec_item.LEVEL;
         END IF;

         --第一个节点没有父节点。会引发异常
         BEGIN
            SELECT code
              INTO v_parentcode
              FROM t_wp_t_tree t
             WHERE t.nodeid = rec_item.nodepid;
         EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
               v_parentcode := NULL;
            WHEN OTHERS
            THEN
               RAISE;
         END;

         UPDATE t_wp_t_tree
            SET code = v_parentcode || v_num
          WHERE nodeid = rec_item.nodeid;

         v_num := v_num + 1;
      END LOOP;
   END;
END tree;
/