游标的使用案例

 

declare c_checkReview cursor FAST_FORWARD for --定义游标
select OperationCheckId,UpperTime,c.MonitorLevelId from Dan_OperationConditionCheck a inner join
Dan_MonitorEvaluate c on(a.ReviewStatus=3 and a.Dan_MonitorEvaluateId=c.Dan_MonitorEvaluateId);

open c_checkReview; --打开游标

fetch next from c_checkReview into @OperationCheckId,@UpperTime,@MonitorLevelId;

WHILE @@FETCH_STATUS=0
begin
set @TimeLimit=-1; set @TimeCha=-1;
select @TimeLimit=TimeLimit from #temp where CheckLevel=@MonitorLevelId; --查到两条记录可能会报错
if(@TimeLimit >0)
begin
select @TimeCha=datediff(hour,getdate(),@UpperTime)
if(@TimeCha>0)
begin
update Dan_OperationConditionCheck set ReviewStatus=0 where OperationCheckId = @OperationCheckId;
insert into Dan_CheckReviewInfo(CheckReviewInfoId,OperationCheckId,ReviewStatus,ReviewConclusion,
CreateTime,ReviewTime,ReviewUserId) values(newid(),@OperationCheckId,0,'自动审核',GETDATE(),
GETDATE(),'00000000-0000-0000-0000-000000000000')
end
end
fetch next from c_checkReview into @OperationCheckId,@UpperTime,@MonitorLevelId;
end

CLOSE c_checkReview; --关闭游标

DEALLOCATE c_checkReview; --删除游标
drop table #temp; --删除临时表