table1结构如下 id int name varchar(50)declare @id int declare @name varchar(50) declare cursor1 cursor for --定义游标cursor1 select * from table1 --使用游标的对象(跟据需要填入select文) open cursor1 --打开游标fetch next from cursor1 into @id,@name --将游标向下移1行,获取的数据放入之前定义的变量@id,@name中while @@fetch_status=0 --判断是否成功获取数据 begin update table1 set name=name+'1' where id=@id --进行相应处理(跟据需要填入SQL文)fetch next from cursor1 into @id,@name --将游标向下移1行 endclose cursor1 --关闭游标 deallocate cursor1
递归还是在程序里写,循环删除可以在SQL里面写,
table1结构如下 id int name varchar(50)declare @id int declare @name varchar(50) declare cursor1 cursor for --定义游标cursor1 select top 2 from table1 --使用游标的对象(跟据需要填入select文) open cursor1 --打开游标fetch next from cursor1 into @id,@name --将游标向下移1行,获取的数据放入之前定义的变量@id,@name中while @@fetch_status=0 --判断是否成功获取数据 begindelete table1 where id=@id --进行相应处理(跟据需要填入SQL文) fetch next from cursor1 into @id,@name --将游标向下移1行 endclose cursor1 --关闭游标 deallocate cursor1
直接前台递归 后台执行一条以条的执行SQL语句就可以了。用事务
应该传一条这样的语句delete from table where id in(1,2,3,4,5...........)而不是循环调用:delete from tbale where id=1这样的方法
不需要用游标,游标慢 假如你要删除的表为 tablea,select id,0 as levl into #tmp from tablea where id= 1 declare @levl int set @levl = 0while exists (select 1 from tablea where parentid in (select id from #tmp where levl=@levl)) begininsert into #tmp select id,@levl+1 from tablea where parentid in (select id from #tmp where levl=@levl)set @levl=@levl+1enddelete from tablea where id in (select id from #tmp)
insert into tableName select 1,0 union all select 2,1 union all select 3,1 union all select 4,2 union all select 5,3 union all select 6,4 union all select 7,5 DECLARE @id INT SET @id=1 ; WITH mytb AS ( SELECT id , parentid FROM tableName WHERE id = @id UNION ALL SELECT a.id , a.parentid FROM tableName AS a , mytb AS b WHERE a.parentid = b.id ) DELETE FROM tableName WHERE EXISTS(SELECT 1 FROM mytb WHERE tablename.id=mytb.id)
为什么非要用游标呢,用递归不是更简单 CREATE PROC delTreeNode @id INT AS BEGIN WITH mytb AS( SELECT id, parentid FROM [dbo].[table] WHERE id = @id UNION ALL SELECT a.id, a.parentid FROM [dbo].[table] AS a, mytb AS b WHERE a.parentid = b.id )
DELETE FROM [dbo].[table] WHERE EXISTS( SELECT 1 FROM mytb WHERE [dbo].[table].id = mytb.id ) END
把[dbo].[table]替换掉
如果只是像楼主这样的话 sql表 ID char ParentID char 如果ParentID不涉及外键之类的问题我想 这样可以不create procedure @id char(6) as delete ParentID where id=@id不知道我这样的理解对不对
CREATE PROCEDURE [dbo].[my_HouseDel] @houseID_ int AS select houseID,0 as levl into #tmp from ut_House where houseID= 1 declare @levl int set @levl = 0while exists (select 1 from ut_House where parentID in (select houseID from #tmp where levl=@levl)) begininsert into #tmp select houseID,@levl+1 from ut_House where parentID in (select houseID from #tmp where levl=@levl)set @levl=@levl+1enddelete from ut_House where houseID in (select houseID from #tmp) GO
修改一下这句 select houseID,0 as levl into #tmp from ut_House where houseID=1 改成 select houseID,0 as levl into #tmp from ut_House where houseID=@houseID_可能我原来没表达清楚CREATE PROCEDURE [dbo].[my_HouseDel] @houseID_ int AS select houseID,0 as levl into #tmp from ut_House where houseID= @houseID_ declare @levl int set @levl = 0while exists (select 1 from ut_House where parentID in (select houseID from #tmp where levl=@levl)) begininsert into #tmp select houseID,@levl+1 from ut_House where parentID in (select houseID from #tmp where levl=@levl)set @levl=@levl+1enddelete from ut_House where houseID in (select houseID from #tmp) GO
id int
name varchar(50)declare @id int
declare @name varchar(50)
declare cursor1 cursor for --定义游标cursor1
select * from table1 --使用游标的对象(跟据需要填入select文)
open cursor1 --打开游标fetch next from cursor1 into @id,@name --将游标向下移1行,获取的数据放入之前定义的变量@id,@name中while @@fetch_status=0 --判断是否成功获取数据
begin
update table1 set name=name+'1'
where id=@id --进行相应处理(跟据需要填入SQL文)fetch next from cursor1 into @id,@name --将游标向下移1行
endclose cursor1 --关闭游标
deallocate cursor1
id int
name varchar(50)declare @id int
declare @name varchar(50)
declare cursor1 cursor for --定义游标cursor1
select top 2 from table1 --使用游标的对象(跟据需要填入select文)
open cursor1 --打开游标fetch next from cursor1 into @id,@name --将游标向下移1行,获取的数据放入之前定义的变量@id,@name中while @@fetch_status=0 --判断是否成功获取数据
begindelete table1 where id=@id --进行相应处理(跟据需要填入SQL文)
fetch next from cursor1 into @id,@name --将游标向下移1行
endclose cursor1 --关闭游标
deallocate cursor1
假如你要删除的表为 tablea,select id,0 as levl into #tmp from tablea where id= 1 declare @levl int
set @levl = 0while exists (select 1 from tablea where parentid in (select id from #tmp where levl=@levl))
begininsert into #tmp
select id,@levl+1 from tablea where parentid in (select id from #tmp where levl=@levl)set @levl=@levl+1enddelete from tablea where id in (select id from #tmp)
为什么用游标慢?游标的原理是?游标是不是新建立了一个临时表,然后把数据一条一条插入到里面,所有慢了?
我主要是不想在服务器端的.asp.cs文件再多次调用SQL语句来删除,想用一条存储过程在数据库端一次删除,从而提高效率.应该怎么做?
select 1,0 union all
select 2,1 union all
select 3,1 union all
select 4,2 union all
select 5,3 union all
select 6,4 union all
select 7,5
DECLARE @id INT
SET @id=1
;
WITH mytb
AS ( SELECT id ,
parentid
FROM tableName
WHERE id = @id
UNION ALL
SELECT a.id ,
a.parentid
FROM tableName AS a ,
mytb AS b
WHERE a.parentid = b.id
)
DELETE FROM tableName WHERE EXISTS(SELECT 1 FROM mytb WHERE tablename.id=mytb.id)
CREATE PROC delTreeNode
@id INT
AS
BEGIN
WITH mytb AS(
SELECT id,
parentid
FROM [dbo].[table]
WHERE id = @id
UNION ALL
SELECT a.id,
a.parentid
FROM [dbo].[table] AS a,
mytb AS b
WHERE a.parentid = b.id
)
DELETE
FROM [dbo].[table]
WHERE EXISTS(
SELECT 1
FROM mytb
WHERE [dbo].[table].id = mytb.id
)
END
把[dbo].[table]替换掉
sql表
ID char
ParentID char
如果ParentID不涉及外键之类的问题我想 这样可以不create procedure
@id char(6)
as
delete ParentID where id=@id不知道我这样的理解对不对
@houseID_ int
AS
select houseID,0 as levl into #tmp from ut_House where houseID= 1 declare @levl int
set @levl = 0while exists (select 1 from ut_House where parentID in (select houseID from #tmp where levl=@levl))
begininsert into #tmp
select houseID,@levl+1 from ut_House where parentID in (select houseID from #tmp where levl=@levl)set @levl=@levl+1enddelete from ut_House where houseID in (select houseID from #tmp)
GO
修改一下这句
select houseID,0 as levl into #tmp from ut_House where houseID=1
改成
select houseID,0 as levl into #tmp from ut_House where houseID=@houseID_可能我原来没表达清楚CREATE PROCEDURE [dbo].[my_HouseDel]
@houseID_ int
AS
select houseID,0 as levl into #tmp from ut_House where houseID= @houseID_ declare @levl int
set @levl = 0while exists (select 1 from ut_House where parentID in (select houseID from #tmp where levl=@levl))
begininsert into #tmp
select houseID,@levl+1 from ut_House where parentID in (select houseID from #tmp where levl=@levl)set @levl=@levl+1enddelete from ut_House where houseID in (select houseID from #tmp)
GO