--生成测试数据
create table BOM(F_id int,F_name varchar(50),F_base int)
insert into BOM select 1,'a',0
insert into BOM select 2,'b',1
insert into BOM select 3,'c',2
insert into BOM select 4,'d',3
go--创建用户定义函数
create function f_getChild(@F_id VARCHAR(10))
returns @t table(F_id VARCHAR(10),F_base VARCHAR(10),Level INT)
as
begin
declare @i int,@ret varchar(8000)
set @i = 1
insert into @t select F_id,F_base,@i from BOM where F_base = @F_id
while @@rowcount<>0
begin
set @i = @i + 1
insert into @t
select
a.F_id,a.F_base,@i
from
BOM a,@t b
where
a.F_base=b.F_id and b.Level = @i-1
end
return
end
go--执行删除操作
delete from BOM where F_id in(select F_id from dbo.f_getChild(0))--查看执行结果
select * from BOM
--删除测试数据
drop function f_getChild
drop table BOM
go
create table BOM(F_id int,F_name varchar(50),F_base int)
insert into BOM select 1,'a',0
insert into BOM select 2,'b',1
insert into BOM select 3,'c',2
insert into BOM select 4,'d',3
go--创建用户定义函数
create function f_getChild(@F_id VARCHAR(10))
returns @t table(F_id VARCHAR(10),F_base VARCHAR(10),Level INT)
as
begin
declare @i int,@ret varchar(8000)
set @i = 1
insert into @t select F_id,F_base,@i from BOM where F_base = @F_id
while @@rowcount<>0
begin
set @i = @i + 1
insert into @t
select
a.F_id,a.F_base,@i
from
BOM a,@t b
where
a.F_base=b.F_id and b.Level = @i-1
end
return
end
go--执行删除操作
delete from BOM where F_id in(select F_id from dbo.f_getChild(0))--查看执行结果
select * from BOM
--删除测试数据
drop function f_getChild
drop table BOM
go
比如select * from biao where id=2
得到三个结果
我要每个结果的id号
create table BOM(F_id int,F_name varchar(50),F_base int)
insert into BOM select 1,'a',0
insert into BOM select 2,'b',1
insert into BOM select 3,'c',2
insert into BOM select 4,'d',3
gocreate proc del --创建存储过程
@node int
as
begin
declare @a int
while (exists(select 1 from bom where f_base=@node))
begin
select @a=f_id from bom where f_base=@node
delete from bom where f_base=@node
exec del @a
end
end
go--调用:
exec del 0--查结果:
declare @ID_B int
while @ID>0
begin
if not exists(select F_base from T where F_id=@ID)
set @ID_B=0
else
select @ID_B=F_base from T where F_id=@ID
Delete T where F_id=@ID
set @ID=@ID_B
end
go--exec sp_Test 3
F_name varchar(10),
F_base int
)
insert into tb1
select 1,'a',0 union all
select 2,'b',1 union all
select 3,'c',2 union all
select 4,'d',3
go--创建存储过程用来删除符合条件的记录
CREATE PROCEDURE SP_DeleteSub(@F_id int)
AS
DECLARE @CurrentID int
set @CurrentID=@F_id
while EXISTS(SELECT F_base from tb1 where F_base=@CurrentID)
begin
delete from tb1 where F_base=@CurrentID
set @CurrentID=@CurrentID+1
end
GO--删除测试
EXEC SP_DeleteSub 2--查看剩余记录以验证
SELECT * FROM TB1--删除自定义函数和演示表
drop procedure SP_DeleteSub
drop table tb1
/*
F_id F_name F_base
----------- ---------- -----------
1 a 0
2 b 1(所影响的行数为 2 行)
*/
insert into T select 1,'a',0
insert into T select 2,'a',1
insert into T select 3,'a',2
insert into T select 4,'a',3------------Function------------------
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GOCreate Function GetDeleteIds (@Ids Int)
Returns @RetIds Table (F_id Int)
As
Begin
Declare @Id Int
Declare @TmpIds Table (F_id Int)
Insert Into @TmpIds Select F_id From [T] Where F_id = @Ids
Set @Id = (Select Top 1 F_id From @TmpIds) While @Id Is Not Null
Begin
Delete From @TmpIds Where F_id = @Id
Insert Into @RetIds Values (@Id)
Insert Into @TmpIds Select F_id From [T] Where F_base = @Id
Set @Id = (Select Top 1 F_id From @TmpIds)
End
Return
EndGO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-----------------Fucntion End--------------------------
---------查看要删除ID------------------
select F_id from GetCompanyIds(1)
---------------删除记录------------------------
delete from T where F_id in (select F_id from GetCompanyIds(1))
--修改后
create table tb1( F_id int,
F_name varchar(10),
F_base int
)
insert into tb1
select 1,'a',0 union all
select 2,'b',1 union all
select 3,'c',2 union all
select 4,'d',3
go--创建存储过程用来删除符合条件的记录
CREATE PROCEDURE SP_DeleteSub(@F_id int)
AS
DECLARE @CurrentID int
declare @TempID int
set @CurrentID=@F_id
while EXISTS(SELECT F_base from tb1 where F_base=@CurrentID)
begin
select @TempID=F_id from tb1 where F_base=@CurrentID
delete from tb1 where F_base=@CurrentID
select @CurrentID=@TempID
end
GO--删除测试
EXEC SP_DeleteSub 3--查看剩余记录以验证
SELECT * FROM TB1--删除自定义函数和演示表
drop procedure SP_DeleteSub
drop table tb1
Create proc GetIds
@Ids int
AS
Begin
Create table #RetIds(F_id int)
Declare @Id Int
Declare @TmpIds Table (F_id Int)
Insert Into @TmpIds Select F_id From [T] Where F_id = @Ids
Set @Id = (Select Top 1 F_id From @TmpIds) While @Id Is Not Null
Begin
Delete From @TmpIds Where F_id = @Id
Insert Into #RetIds Values (@Id)
Insert Into @TmpIds Select F_id From [T] Where F_base = @Id
Set @Id = (Select Top 1 F_id From @TmpIds)
End select * from #RetIds
drop table #RetIds
End
GO
create trigger del_tri on t for delete
as
delete from t
where f_base in (select f_id from deleted)只不过要用到触发器递归,在数据库的属性窗口中有‘选项’选项卡,
选中‘递归触发器’