sql 几种循环方式

1:游标方式

ALTER PROCEDURE [dbo].[testpro]
as declare @yeardatestr varchar(20) --日期拼接
declare @meterno varchar(20) --仪表编号
declare @collectindataname varchar(30) --数据采集表
declare @collectindataname_back varchar(30) --数据采集备份表 declare @monsdt varchar(20)
declare @monedt varchar(20) begin set @yeardatestr = datename(YY,getdate()) + datename(MM,getdate()) set @monsdt=Convert(VarChar(4),DatePart(year,GETDATE()))+'-'+Convert(VarChar(2),DatePart(MONTH,DateAdd(MONTH,-2,GETDATE())))+'-01'
set @monedt=CONVERT(VARCHAR(10),DateAdd(DAY,-1,DateAdd(MONTH,2,Convert(datetime,@monsdt,121))),20) declare meters_cur cursor local fast_forward --定义游标
for
select distinct MeterNo from MeterInfo
open meters_cur --打开游标
fetch next from meters_cur into @meterno--从游标中取出数据
while @@fetch_status =0 --取出数据成功
begin dbcc SHRINKFILE(testdb_log,0) --收缩日志
fetch next from meters_cur into @meterno--下一条
end
close meters_cur--关闭游标
deallocate meters_cur--释放游标 end

2:goto方式

    nextValue:--循环起点关键字
select @num=ynum from pp_yunnum where prjsht=@prjsht IF(@flg=1)
set @yunsht='K' + @prjsht + '-' + Convert(varchar(10),@num+1)
ELSE
set @yunsht=REPLACE(@prjsht,'_','') + '-' + dbo.Lpad(@num+1,2,'') if exists (select 1 from pp_yun where pp_yun.sht=@yunsht)
begin
update pp_yunnum set ynum=ynum+1 where prjsht=@prjsht goto nextValue;--重新从nextValue出执行
end
上一篇:P2014 选课


下一篇:Codeforces Round #674 (Div. 3) F. Number of Subsequences 题解(dp)