MySQL存储过程递归实现,MySql ::存储过程递归

how can I rewrite the following SP to face recursive limitation in 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;

without using this sts :

"SET @@SESSION.max_sp_recursion_depth = 255;"

I mean that can interact with hierarchy data without recursion.

解决方案

Mysql allows up to 255 recursion in a PROCEDURE if you set max_sp_recursion_depth.

Its risky because there is a chance that the needed recursion depth may exceed 255 sometime in future.

So Its better you replace your function with a procedure, using an INOUT variable for the return_result.