問題描述

我正在嘗試編寫一個(gè)遞歸過程，該過程將刪除節(jié)點(diǎn)及其所有子節(jié)點(diǎn)(如果它們?cè)诒碇?.我嘗試執(zhí)行以下操作

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

但由于某種原因，這不起作用.它刪除了節(jié)點(diǎn)，但孩子們?nèi)匀淮嬖?表,,設(shè)計(jì)".

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
);

你能指出我犯的錯(cuò)誤并建議一個(gè)工作程序嗎?UPD:我將游標(biāo)設(shè)為 LOCAL 并且在進(jìn)入 while 循環(huán)之前我正在獲取一次，但它仍然沒有刪除所有的孩子.

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.

推薦答案

我認(rèn)為你有語法問題.我是這樣修復(fù)的

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

這篇關(guān)于刪除 SQL 表中的樹節(jié)點(diǎn)的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！