递归打平的方法

总体思想：

把一有递归关系的表递归关系变成一个字符串

一下实例是把表T_GOV_DEPT的递归关系整理打平后更新到表T_GOV_DEPT_EX中

以后递归查询就可以使用 where tree_ex_str like 字符串%（where tree_ex_str like fn_get_gov_tree_ex_str(i_get_gov_dept_id)||’%’）

 

1.建表 T_GOV_DEPT

-- Create table

create table T_GOV_DEPT

(

  gov_dept_id      INTEGER not null,

  dept_name        NVARCHAR2(30),

  dept_code        VARCHAR2(20),

  gov_dept_parent  INTEGER,

  contact          NVARCHAR2(50),

  phone            VARCHAR2(20),

  mobile           VARCHAR2(20),

  fax              VARCHAR2(20),

  email            VARCHAR2(30),

  address_id       INTEGER,

  default_map_area NVARCHAR2(200),

  status           VARCHAR2(1),

  description      NVARCHAR2(100),

  create_time      DATE,

  create_user      VARCHAR2(10),

  update_time      DATE,

  update_user      VARCHAR2(10),

  address_detail   NVARCHAR2(100),

  longitude        NUMBER,

  latitude         NUMBER

)

 

 

1.建表 T_GOV_DEPT_EX

-- Create table

create table T_GOV_DEPT_EX

(

  gov_dept_id      INTEGER not null,

  dept_name        NVARCHAR2(30),

  dept_code        VARCHAR2(20),

  gov_dept_parent  INTEGER,

  tree_ex_str      VARCHAR2(100),

  contact          NVARCHAR2(50),

  phone            VARCHAR2(20),

  mobile           VARCHAR2(20),

  fax              VARCHAR2(20),

  email            VARCHAR2(30),

  address_id       INTEGER,

  default_map_area NVARCHAR2(200),

  status           VARCHAR2(1),

  description      NVARCHAR2(100),

  create_time      DATE,

  create_user      VARCHAR2(10),

  update_time      DATE,

  update_user      VARCHAR2(10),

  address_detail   NVARCHAR2(100),

  longitude        NUMBER,

  latitude         NUMBER

)

 

 

2.建立一个函数根据id获得其递归字符串

CREATE OR REPLACE FUNCTION fn_get_gov_tree_ex_str(i_get_gov_dept_id INT)

  RETURN VARCHAR2 IS

  --create by Given 2012/11/26

   --功能：根据政府部门id 获得其递归字符串

  v_tree_ex_str VARCHAR2(100);

  v_row_cnt     INT;

BEGIN

 

  SELECT COUNT(1)

    INTO v_row_cnt

    FROM t_gov_dept_ex

   WHERE gov_dept_id = i_get_gov_dept_id;

 

  IF v_row_cnt > 0 THEN

    SELECT tree_ex_str

      INTO v_tree_ex_str

      FROM t_gov_dept_ex

     WHERE gov_dept_id = i_get_gov_dept_id;

  ELSE

    v_tree_ex_str := '@@@@@@@@@@@@@@@@@@@@';

  END IF;

 

  RETURN v_tree_ex_str;

END;

 

3.建立一个存储过程整理递归字符串

CREATE OR REPLACE PROCEDURE sp_gov_dept_compute_tree(i_dept_id   INT,

                                                     i_parent_id INT DEFAULT NULL) IS

  v_tree_ex_str VARCHAR2(100);

BEGIN

  /* Created by ChenXuDong at 2012-11-24 */

 

  /* 找部门的节点的扩展字符串 */

  v_tree_ex_str := fn_get_gov_tree_ex_str(i_parent_id);

 

  IF v_tree_ex_str = '@@@@@@@@@@@@@@@@@@@@' THEN

    v_tree_ex_str := NULL;

  END IF;

 

  --更新t_gov_dept_ex表的tree字符串

  UPDATE t_gov_dept_ex a

  SET tree_ex_str = ( SELECT b.tree_ex_str

                         FROM (

                                   SELECT gov_dept_id,

                                         nvl(v_tree_ex_str,'') || ltrim(sys_connect_by_path(gov_dept_id, '-'), '-') || '-' AS tree_ex_str

                                   FROM t_gov_dept_ex

                                   START WITH gov_dept_id = i_dept_id

                                   CONNECT BY nocycle PRIOR gov_dept_id = gov_dept_parent

                               ) b

                         WHERE b.gov_dept_id = a.gov_dept_id

                        )

  WHERE EXISTS(SELECT 1

               FROM (

                       SELECT gov_dept_id

                       FROM t_gov_dept_ex

                       START WITH gov_dept_id = i_dept_id

                       CONNECT BY nocycle PRIOR gov_dept_id = gov_dept_parent

                      ) b

                WHERE b.gov_dept_id = a.gov_dept_id

               );

END;

 

 

4.建立触发器

  当添加删除或者递归关系有变化时调用存储过程（sp_gov_dept_compute_tree）进行整理

CREATE OR REPLACE TRIGGER trg_gov_dept_change

  AFTER INSERT OR UPDATE OR DELETE ON t_gov_dept

  FOR EACH ROW

BEGIN

   /* Created by ChenXuDong at 2012-11-24 */

  IF inserting THEN

    --INSERT触发

 

    INSERT INTO t_gov_dept_ex

      (gov_dept_id,

       dept_name,

       dept_code,

       gov_dept_parent,

       contact,

       phone,

       mobile,

       fax,

       email,

       address_id,

       default_map_area,

       status,

       description,

       create_time,

       create_user,

       update_time,

       update_user,

       address_detail,

       longitude,

       latitude)

    VALUES

      (:new.gov_dept_id,

       :new.dept_name,

       :new.dept_code,

       :new.gov_dept_parent,

       :new.contact,

       :new.phone,

       :new.mobile,

       :new.fax,

       :new.email,

       :new.address_id,

       :new.default_map_area,

       :new.status,

       :new.description,

       :new.create_time,

       :new.create_user,

       :new.update_time,

       :new.update_user,

       :new.address_detail,

       :new.longitude,

       :new.latitude);

 

    sp_gov_dept_compute_tree(:new.gov_dept_id, :new.gov_dept_parent);

 

  ELSIF updating THEN

    --UPDATE触发

    UPDATE t_gov_dept_ex

       SET gov_dept_id      = :new.gov_dept_id,

           gov_dept_parent  = :new.gov_dept_parent,

           dept_name        = :new.dept_name,

           dept_code        = :new.dept_code,

           contact          = :new.contact,

           phone            = :new.phone,

           mobile           = :new.mobile,

           fax              = :new.fax,

           email            = :new.email,

           address_id       = :new.address_id,

           default_map_area = :new.default_map_area,

           status           = :new.status,

           description      = :new.description,

           create_time      = :new.create_time,

           create_user      = :new.create_user,

           update_time      = :new.update_time,

           update_user      = :new.update_user,

           address_detail   = :new.address_detail,

           longitude        = :new.longitude,

           latitude         = :new.latitude

     WHERE gov_dept_id = :new.gov_dept_id;

 

    IF NOT (NVL(:new.gov_dept_id,-1) = NVL(:old.gov_dept_id,-1) AND NVL(:new.gov_dept_parent,-1) = NVL(:old.gov_dept_parent,-1)) THEN

        sp_gov_dept_compute_tree(:new.gov_dept_id, :new.gov_dept_parent);

    END IF;

   

  ELSIF deleting THEN

    --DELETE触发

    DELETE FROM t_gov_dept_ex WHERE gov_dept_id = :old.gov_dept_id;

 

  END IF;

END;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/11745285/viewspace-751810/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/11745285/viewspace-751810/