首页 文章

需要使用mysql存储过程基于父节点id删除子节点

提问于
浏览 次
0

Question:

我需要根据父节点id删除子节点 . 所以请帮助我 .

Note:

我在下面的存储过程中根据父节点id选择子节点 .

CREATE DEFINER=`root`@`localhost` PROCEDURE `Hierarchy`(IN `GivenID` INT, IN `initial` INT)
BEGIN

    DECLARE done INT DEFAULT 0;
    DECLARE next_id INT;
    -- CURSOR TO LOOP THROUGH RESULTS --
    DECLARE cur1 CURSOR FOR SELECT GID FROM civicsoft_group WHERE Parent_Group_ID = GivenID;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    -- CREATE A TEMPORARY TABLE TO HOLD RESULTS --
    IF initial=1 THEN
        -- MAKE SURE TABLE DOESN'T CONTAIN OUTDATED INFO IF IT EXISTS (USUALLY ON ERROR) --
        DROP TABLE IF EXISTS OUT_TEMP; 
        CREATE TEMPORARY TABLE OUT_TEMP (TGID int,TParent_Group_ID int, TGroup_Name varchar(32),TGroup_Type varchar(1),TProject_Status tinyint(1),TEID int,TEmp_Group_ID int,TEmp_Name varchar(100),TEmp_Type varchar(2));
    END IF;

    -- ADD OURSELF TO THE TEMPORARY TABLE --
    INSERT INTO OUT_TEMP SELECT cg.GID,cg.Parent_Group_ID,cg.Group_Name,cg.Group_Type,cg.Project_Status,ce.EID,ce.Emp_Group_ID,ce.Emp_Name,ce.Emp_Type FROM civicsoft_group cg LEFT JOIN civicsoft_employee ce ON cg.GID = ce.Emp_Group_ID  WHERE cg.GID = GivenID;

    -- AND LOOP THROUGH THE CURSOR --
    OPEN cur1;
    read_loop: LOOP
        FETCH cur1 INTO next_id;

        -- NO ROWS FOUND, LEAVE LOOP --
        IF done THEN
        LEAVE read_loop;
        END IF;

        -- NEXT ROUND --

    SET @@GLOBAL.max_sp_recursion_depth = 255;
    SET @@session.max_sp_recursion_depth = 255; 

        CALL Hierarchy(next_id, 0);     
    END LOOP;

    CLOSE cur1;

    -- THIS IS THE INITIAL CALL, LET'S GET THE RESULTS --
    IF initial=1 THEN
SELECT * FROM OUT_TEMP;
        -- CLEAN UP AFTER OURSELVES --
        DROP TABLE OUT_TEMP; 
    END IF;
END
mysql

1 回答

  • 0

    如果您在引用父表的子表中添加了外键，则最简单的方法是 . 当父和子在同一个表中时，这也适用 . 在此外键中启用 ON DELETE CASCADE 选项 . 然后只需删除父条目，MySQL也会删除子项 .

    回复于

相关问题