删除 SQL 表中的树节点

2021-09-10 00:00:00 tsql sql-server

我正在尝试编写一个递归过程，该过程将删除节点及其所有子节点(如果它们在表中).我尝试执行以下操作

I am trying to write a recursive procedure that would delete the node and all it's children if they are such in the table. I tried doing the following 

  CREATE PROCEDURE DeleteFile 
    @FileID INTEGER,
    @UserID VARCHAR(MAX) 
AS
    DELETE FROM [FileTree] WHERE [ID] = @FileID AND [UserID]=@UserID;
    IF EXISTS(SELECT * FROM [FileTree] WHERE [ParentID] = @FileID AND [UserID]=@UserID)
    BEGIN
       DECLARE FileCursor CURSOR LOCAL FOR 
            SELECT [ID],[UserID] FROM [FileTree] WHERE [ParentID] = @FileID AND [UserID]=@UserID;
       OPEN FileCursor 
       FETCH NEXT FROM FileCursor INTO @FileID , @UserID 
       WHILE @@FETCH_STATUS =0
       BEGIN
            EXEC DeleteFile @FileID,@UserID;
            FETCH NEXT FROM FileCursor INTO @FileID , @UserID ;
       END
    END
    ELSE
    return

但由于某种原因，这不起作用.它删除了节点，但孩子们仍然存在.表,,设计".

But for some reason this is not working. It deletes the node but the kids remain. Table ,,design" . 

CREATE TABLE [FileTree] (
    [ID]       INT           IDENTITY  NOT NULL,
    [Name]     VARCHAR (MAX) NOT NULL,
    [ParentID] INT           NULL,
    [UserID]   VARCHAR (MAX) NOT NULL
);

你能指出我犯的错误并建议一个工作程序吗?UPD:我将游标设为 LOCAL 并且在进入 while 循环之前我正在获取一次，但它仍然没有删除所有的孩子.

Can you please indicate the errors I made and suggest a working procedure ? UPD: I made the cursor LOCAL and I am fetching one time before going into the while loop, it still does not delete all the children.

推荐答案

我认为你有语法问题.我是这样修复的

I think you have syntax problem.i fixed it like this

CREATE PROCEDURE DeleteFile 
    @FileID INTEGER,
    @UserID VARCHAR(MAX) 
AS
BEGAIN
    DELETE FROM [FileTree] WHERE [ID] = @FileID AND [UserID]=@UserID;
    IF EXISTS(SELECT * FROM [FileTree] WHERE [ParentID] = @FileID AND [UserID]=@UserID)
    BEGIN
       DECLARE FileCursor CURSOR LOCAL FOR 
            SELECT [ID],[UserID] FROM [FileTree] WHERE [ParentID] = @FileID AND [UserID]=@UserID;
       OPEN FileCursor 
       FETCH NEXT FROM FileCursor INTO @FileID , @UserID 
       WHILE @@FETCH_STATUS =0
       BEGIN
            EXEC DeleteFile @FileID,@UserID;
            FETCH NEXT FROM FileCursor INTO @FileID , @UserID ;
       END
    END
  END

相关文章