我如何重写以下 SP 来面对 MySQL 中的递归限制:
CREATE DEFINER = `root`@`localhost` PROCEDURE `SP_DeleteParentDirectory`(pParentID INT,pIsFolder INT,pReferenceID INT)
BEGIN
SET @@SESSION.max_sp_recursion_depth = 255;
SET @IsFolder= NULL;
SET @ChildID= NULL;
SET @ReferenceID= NULL;
IF ( pIsFolder= 2) THEN
SELECT FileID INTO @ChildID FROM filesinfo WHERE ParentID= pParentID LIMIT 1;
WHILE (@ChildID IS NOT NULL) DO
SELECT filesinfo.IsFolder,filesinfo.ReferenceID INTO @IsFolder,@ReferenceID FROM filesinfo WHERE filesinfo.FileID = @ChildID;
CALL SP_DeleteParentDirectory(@ChildID,@IsFolder,@ReferenceID);
SELECT filesinfo.FileID INTO @ChildID FROM filesinfo WHERE ParentID= pParentID LIMIT 1;
END While;
END IF;
DELETE FROM filesinfo Where filesinfo.FileID= pParentID;
IF EXISTS( SELECT * FROM filesrefrences WHERE filesrefrences.ReferenceID = pReferenceID AND filesrefrences.RefrenceCount>1) THEN
UPDATE filesrefrences
SET RefrenceCount= RefrenceCount-1 WHERE filesrefrences.ReferenceID = pReferenceID;
ELSE
SET @ReferenceKey= NULL;
Select filesrefrences.RefrenceKey INTO @ReferenceKey FROM filesrefrences WHERE filesrefrences.ReferenceID= pReferenceID;
IF NOT EXISTS(SELECT * FROM deletefilesreferences WHERE deletefilesreferences.FileReference= @ReferenceKey) THEN
INSERT INTO deletefilesreferences (deletefilesreferences.FileReference)VALUES(@ReferenceKey);
END IF;
DELETE FROM filesrefrences where filesrefrences.ReferenceID = pReferenceID;
END IF;
-- CALL SP_DeleteFileInfo(pParentID,pReferenceID);
-- SELECT * FROM deletefilesreferences;
-- CALL SP_GetDeletedReferences();
END;
不使用这个 sts :
"设置@@SESSION.max_sp_recursion_深度 = 255;”
我的意思是可以与层次结构数据交互而无需递归。
如果设置 max_sp_recursion_depth,Mysql 允许在一个 PROCEDURE 中最多 255 次递归。
这是有风险的,因为将来某个时候所需的递归深度可能会超过 255。
所以最好用一个过程替换你的函数,使用INOUTreturn_result 的变量。
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)