Sql Server For循环

目录

• Sql Server 循环实现
  • 标号不利用游标
  • 游标

Sql Server 循环实现

标号不利用游标

declare @begin int
declare @loopIndex int
declare @SN nvarchar(50)
declare @Ith float
set @begin=1
select @loopIndex = COUNT([Test].dbo.QXIth.SN) from dbo.QXIth --循环的最大次数
begin
	while @begin<@loopIndex
		begin
			 select @SN=temp.SN,@Ith = temp.Ith from (select *,ROW_NUMBER() over(order by SN) as 行数 from [Test].dbo.QXIth)as temp where temp.行数=@begin
			 if exists(select 1 from [Test].dbo.奇芯 where 序列号=@SN)
				begin
				update dbo.奇芯 set 门限电流 = @Ith where dbo.奇芯.序列号 =@SN;
				
				end
			else begin
				insert into dbo.奇芯 (序列号,门限电流)values(@SN,@Ith)
				end
			set @begin=@begin+1;
			continue;
		end
end
print @loopIndex

游标

declare @SN nvarchar(50)
declare @Ith float
declare @num int
set @num=0
declare cursorTemp  cursor for select SN,Ith from [Test].[dbo].[QXIth]
open cursorTemp
fetch next from cursorTemp into @SN,@Ith 
while @@FETCH_STATUS=0--这里计数会多一个,
	begin
		if exists(select 1 from [Test].[dbo].[奇芯] where [Test].[dbo].[奇芯].[序列号]=@SN)
			begin
			update dbo.奇芯 set 门限电流 = @Ith where dbo.奇芯.序列号 =@SN;
			set @num =@num+1
			end
		else begin
			insert into dbo.奇芯 (序列号,门限电流)values(@SN,@Ith)
		end
		FETCH NEXT FROM  cursorTemp INTO @SN,@Ith
	end
close cursorTemp
DEALLOCATE cursorTemp
print @num