递归打平

总体思路：

把一个有递归关系的原表的递归关系整理成递归字符串放到另外一表中

本实例：原表t_property_co 整理后的表t_property_co_ex

 

1.递归存储过程

CREATE DEFINER=`TowerCrane`@`%` PROCEDURE `sp_property_tree_ex_str`(i_dept_id INT,i_tree_ex_str VARCHAR(100))

BEGIN

 /*created by ChenXuDong at 2012-10-31*/

 DECLARE done INT DEFAULT 0;

 

 DECLARE v_dept_id   INT;

 DECLARE v_tree_ex_str VARCHAR(100);

 

 DECLARE cur1 CURSOR FOR SELECT dept_id FROM t_property_co WHERE parent_id = i_dept_id;

 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

 

 SET @@max_sp_recursion_depth = 100;

 

 SET v_tree_ex_str = CONCAT(IFNULL(i_tree_ex_str,''),i_dept_id,'-');

 

 INSERT INTO tmp_property_co_ex(dept_id,tree_ex_str)

 VALUES(i_dept_id,v_tree_ex_str);

 

 OPEN cur1;

 

 FETCH cur1 INTO v_dept_id;

 WHILE done=0 DO

    

   CALL sp_property_tree_ex_str(v_dept_id,v_tree_ex_str);

     

   FETCH cur1 INTO v_dept_id;

 

 END WHILE;

 

 CLOSE cur1;

END

 

2.调用递归存储过程整理递归关系到表t_property_co_ex

CREATE DEFINER=`TowerCrane`@`%` PROCEDURE `sp_property_co_ex`(i_dept_id INT, i_parent_id INT)

BEGIN

   /* Created by ChenXuDong at 2012-10-30 */  

   DECLARE v_tree_ex_str VARCHAR(100) DEFAULT NULL;

   

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

          SELECT tree_ex_str INTO v_tree_ex_str FROM t_property_co_ex WHERE dept_id = i_parent_id;

  

    /*临时表不存在就创建 */

   CREATE TEMPORARY TABLE IF NOT EXISTS tmp_property_co_ex

                   (

                            dept_id INT,

                            tree_ex_str varchar(100)

                   );

  

    /* 删除临时表记录*/

    DELETE FROM tmp_property_co_ex;

 

    CALL sp_property_tree_ex_str(i_dept_id,v_tree_ex_str);

 

    UPDATE t_property_co_ex a, tmp_property_co_ex b

    SET a.tree_ex_str   = b.tree_ex_str

    WHERE a.dept_id = b.dept_id

         /*AND a.status ='1'*/ ;

 

END

 

3.建立触发器添加修改删除时掉用上面的存储过程sp_property_co_ex

 

 

CREATE TRIGGER `trg_insert_property_ex` AFTER INSERT ON `t_property_co` FOR EACH ROW BEGIN

     INSERT INTO t_property_co_ex

      (    dept_id,dept_name,pinyin,parent_id,principal,mobile,phone,email,pca_code,address,

             status,description,update_time,update_object,create_time,create_object

       )

      SELECT NEW.dept_id,NEW.dept_name,NEW.pinyin,NEW.parent_id,NEW.principal,NEW.mobile,NEW.phone,NEW.email,NEW.pca_code,NEW.address,

             NEW.status,NEW.description,NEW.update_time,NEW.update_object,NEW.create_time,NEW.create_object;

 

    CALL  sp_property_co_ex(NEW.dept_id,NEW.parent_id);

END;

 

CREATE TRIGGER `trg_modify_t_property_ex` AFTER UPDATE ON `t_property_co` FOR EACH ROW BEGIN 

          UPDATE t_property_co_ex A

          SET

              A.dept_id            = NEW.dept_id, 

              A.parent_id         = NEW.parent_id,

              A.dept_name       = NEW.dept_name,

              A.pinyin          = NEW.pinyin,

              A.principal       = NEW.principal,

              A.mobile          = NEW.mobile,

              A.phone           = NEW.phone,

              A.email           = NEW.email,

              A.pca_code     = NEW.pca_code,

              A.address         = NEW.address,

              A.status          = NEW.status,

              A.description    = NEW.description,

              A.update_time    = NEW.update_time,

              A.update_object  = NEW.update_object,

              A.create_time    = NEW.create_time,

              A.create_object  = NEW.create_object

          WHERE A.dept_id = NEW.dept_id;

 

      IF NOT (IFNULL(NEW.dept_id,'')=IFNULL(OLD.dept_id,'') AND IFNULL(NEW.parent_id,'')=IFNULL(OLD.parent_id,'')) THEN

         CALL sp_property_co_ex(NEW.dept_id,NEW.parent_id);

      END IF;

END;

 

CREATE TRIGGER `trg_delete_property_co_ex` AFTER DELETE ON `t_property_co` FOR EACH ROW BEGIN

    /*此处是物理删除不负责层次关系，直接就删除了*/

    DELETE FROM t_property_co_ex  WHERE dept_id = OLD.dept_id;

END;

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

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