數據庫遍歷問題

[ieooo]

有一表TreeView,結構如下﹕
NodeID NodeName ParentNodeID
1 根節點 0
2 子節點1 1
3 子節點1_1 2
4 子節點1_2 2
5 子節點2 1
6 子節點2_1 5
7 子節點2_1_1 6
8 子節點2_2 5
ParentNodeID保存的是父節點的NodeID,現在要寫一Procedure,只要傳入一NodeID值﹐
便將該記錄及其所有子節點全部刪除。比方說傳入參數5﹐則將NodeID為5,6,7,8的記錄全部刪除。
請指點。

[ieooo]

Create proc DeleteTreeNode
@TreeNodeID int
as
declare @ParentNodeID int
declare @ChildrenNodeID int

begin
Begin tran DeleteNodes
If (select count(*) from TreeNodes where NodeID=@TreeNodeID) >= 1
begin
Delete TreeNodes where NodeID=@TreeNodeID
Set @ParentNodeID = @TreeNodeID

Declare @Cur_FindChildrenNode Cursor

FindNextNodeID:
Set @Cur_FindChildrenNode = Cursor for
select NodeID from TreeNodes
where ParentNode = @ParentNodeID

open @Cur_FindChildrenNode
Fetch @Cur_FindChildrenNode
Into @ChildrenNodeID

If @@Fetch_Status<>0
begin
Delete TreeNodes where NodeID=@ParentNodeID
if @@Error <> 0 Goto ErrorHandle
end

while @@Fetch_Status = 0
begin
if (select count(1) from TreeNodes where ParentNode=@ChildrenNodeID) >=1
begin
select top 1 @ParentNodeID = NodeID from TreeNodes where ParentNode=@ChildrenNodeID
if @@error <> 0
begin
Print '回傳NodeID錯誤。'
Goto ErrorHandle
end
else Goto FindNextNodeID
end
else
begin
Delete TreeNodes where NodeID=@ChildrenNodeID
if @@error <> 0 Goto ErrorHandle
else Fetch Next From @Cur_FindChildrenNode Into @ChildrenNodeID
end
end

Close @Cur_FindChildrenNode
DealLocate @Cur_FindChildrenNode

end
else
begin
print '不存在該ID值的記錄!'
end

select * from TreeNodes
Commit tran
Return 0
ErrorHandle:
RollBack Tran
return 0
end

該存儲過程只能刪除部分記錄﹐請大蝦指點。

[pbsql]

参考这个：

--树形数据查询示例
--作者: 邹建

if exists (select * from dbo.sysobjects where id = object_id(N'tb') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table tb
GO

--示例数据
create table tb(id int identity(1,1),pid int,name varchar(20))
insert tb select 0,'中国'
union all select 0,'美国'
union all select 0,'加拿大'
union all select 1,'北京'
union all select 1,'上海'
union all select 1,'江苏'
union all select 6,'苏州'
union all select 7,'常熟'
union all select 6,'南京'
union all select 6,'无锡'
union all select 2,'纽约'
union all select 2,'旧金山'
go

/*--树形数据处理

级别及排序字段

--邹建 2003-12(引用请保留此信息)--*/

/*--调用示例

--调用函数实现分级显示
select replicate('-',b.[level]*4)+a.name
from tb a,f_id()b
where a.id=b.id
order by b.sid
--*/
create function f_id()
returns @re table(id int,[level] int,sid varchar(8000))
as
begin
declare @l int
set @l=0
insert @re select id,@l,right(10000+id,4)
from tb where pid=0
while @@rowcount>0
begin
set @l=@l+1
insert @re select a.id,@l,b.sid+right(10000+a.id,4)
from tb a,@re b
where a.pid=b.id and b.[level]=@l-1
end
return
end
go

--调用函数实现分级显示
select a.*,tree=replicate('-',b.[level]*4)+a.name
from tb a,f_id()b
where a.id=b.id
order by b.sid
go

--删除测试
drop table tb
drop function f_id

[netcoder]

手上正好有个例子，你看看，修改一下表名\字段名就好用
转：


--循环删除
--建立测试表
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[表]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[表]
GO

CREATE TABLE [dbo].[表] (
[ID] [int] NOT NULL ,
[ParentID] [int] NULL
) ON [PRIMARY]
GO

--插入数据
insert 表
select 1,0
union select 2,1
union select 3,2
union select 4,3
union select 5,4

--实现循环删除的存储过程
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[pro_test]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[pro_test]
GO
CREATE procedure pro_test
@n int
as
declare @sql varchar(8000)
declare @cur varchar(8)
set @cur='cur'+ cast(@n as varchar)
delete 表 where ID=@n
set @sql='declare @s varchar(100)'
+ ' declare @id int'
+ ' declare ' + @cur + ' cursor'
+ ' FOR SELECT ID FROM 表 WHERE ParentID='+cast(@n as varchar)
+ ' OPEN '+ @cur
+ ' FETCH NEXT FROM ' + @cur + ' INTO @id'
+ ' WHILE (@@FETCH_STATUS=0)'
+ ' BEGIN'
+ ' SET @s=''pro_test ''+ cast(@id as varchar)'
+ ' EXEC(@s)'
+ ' FETCH NEXT FROM ' + @cur + ' INTO @id'
+ ' END'
+ ' CLOSE ' +@cur
+ ' DEALLOCATE ' + @cur
exec(@sql)
GO
--测试语句
SELECT * FROM 表
EXEC pro_test 2
SELECT * FROM 表
--删除测试表及存储过程
DROP PROCEDURE pro_test
DROP TABLE 表

/--结果
（所影响的行数为 5 行）

ID ParentID
----------- -----------
1 0
2 1
3 2
4 3
5 4

（所影响的行数为 5 行）


（所影响的行数为 1 行）


（所影响的行数为 1 行）


（所影响的行数为 1 行）


（所影响的行数为 1 行）

ID ParentID
----------- -----------
1 0

（所影响的行数为 1 行）

--/

[ieooo]

ALTER Proc DeleteNodes_3
@root_id int
as
declare @parent_node_id int
declare @children_nodes_id int

begin
begin Tran
if exists(Select NodeId from TreeNodes where NodeId=@root_id)
begin
--創建游標變量
Declare @Cur_Find_ChildrenNodes Cursor
Set @Cur_Find_ChildrenNodes = Cursor For
Select NodeID
From TreeNodes
Where ParentNode = @root_id

--打開游標變量
Open @Cur_Find_ChildrenNodes
Fetch @Cur_Find_ChildrenNodes Into @children_nodes_id

--如果沒有子節點﹐則直接刪除該記錄
While (@@Fetch_Status=0)
Begin
if exists(Select NodeId from TreeNodes where ParentNode=@children_nodes_id)
begin
exec DeleteNodes_3 @children_nodes_id
end
else
Delete TreeNodes where NodeID=@children_nodes_id
Fetch Next From @Cur_Find_ChildrenNodes Into @children_nodes_id

If @@error<>0 goto ErrorHandle
End

Close @Cur_Find_ChildrenNodes
DealLocate @Cur_Find_ChildrenNodes

--繼續查找擊父節點的其他子節點
Delete From TreeNodes Where NodeID=@root_id
end

Commit tran return 0
ErrorHandle:
Rollback tran return 0
end

[wyb0026]

CREATE PROCEDURE SP_Del_Task @pTskId CHAR(12),@value CHAR(1)
AS

DECLARE @tskId char(12)

DECLARE task_cursor CURSOR LOCAL FOR --游标定义
SELECT TSK_ID
FROM TSK_TASK
WHERE TSK_PID =@pTskId

OPEN task_cursor --打开游标

FETCH NEXT FROM task_cursor INTO @tskId --

WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE TSK_TASK SET TSK_VALID=@value WHERE TSK_ID=@tskId
EXEC SP_Del_Task @tskId,@value
FETCH NEXT FROM task_cursor INTO @tskId
END

CLOSE task_cursor

DEALLOCATE task_cursor
GO