mysql递归存储过程

-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `SP_ReNameFolder`(IN RootID INT,IN FolderName varchar(100),IN MaxDepth INT)
BEGIN
DECLARE ResultPath varchar(100);
DECLARE OldPath varchar(100);
CREATE TEMPORARY TABLE IF NOT EXISTS   
tmpLst(ID int,depth int);
set SQL_SAFE_UPDATES = 0;
SET @@max_sp_recursion_depth = MaxDepth;
DELETE FROM tmpLst where 1=1;     
CALL SP_iterative(RootID,0);  
 
        SELECT CONCAT(kp_ss.tb_kf_objectinfo.path,'\\',FolderName) INTO ResultPath
        FROM kp_ss.tb_kf_objectinfo where ID=(SELECT PID FROM kp_ss.tb_kf_objectinfo 
        WHERE id=RootID);
        SELECT PATH INTO OldPath FROM kp_ss.tb_kf_objectinfo WHERE ID=RootID;
        UPDATE kp_ss.tb_kf_objectinfo SET `Name`=FolderName WHERE ID=RootID;
        UPDATE kp_ss.tb_kf_objectinfo SET PATH=REPLACE(PATH,OldPath,ResultPath) 
        WHERE ID IN (SELECT ID FROM tmpLst);
set SQL_SAFE_UPDATES = 1;
END

µÝ¹é´æ´¢¹ý³Ì
-- --------------------------------------------------------------------------------
-- Routine DDL