1。游标与循环如何互相转换啊?有一位说可以用临时表代替游标功能,这又从何做起?
select identity(int,1,1) as id, * into #T from yourtable where ...
declare @i int
declare @tot int
select @tot = count(*) from #T
set @i = 1
while @i <= @tot
begin
select * from #t where id = @i
......
set @i = @i + 1
end
select identity(int,1,1) as id, * into #T from yourtable where ...
declare @i int
declare @tot int
select @tot = count(*) from #T
set @i = 1
while @i <= @tot
begin
select * from #t where id = @i
......
set @i = @i + 1
end
确切的说应该是比临时表循环更耗费系统资源。
因为游标是存放在内存中,临时表存放在硬盘的临时数据区。所以前者更耗费系统内存资源。
游标以建立,就将相关的记录锁住,直到取消游标;
而循环只对正在处理的记录进行锁定,没有处理的记录不加锁。