游标的使用范例

declare @orderN varchar(50)--临时变量，用来保存游标值
declare ee cursor for --申明游标 为orderNum
select FID from estate_base_RoomNumbers where PayNumber is null or PayNumber='' --打开游标
OPEN ee;
fetch next from ee into @orderN ----开始循环游标变量
while(@@fetch_status=0)---返回被 FETCH  语句执行的最后游标的状态，而不是任何当前被连接打开的游标的状态。
BEGIN
 declare @PayNumber  nvarchar(50) ; 
 DECLARE @row int
 SET @row = 1--记录总数为9999999
 WHILE @row<=999
BEGIN
 SET @PayNumber = 0 IF LEN(@PayNumber)<>10


 set @PayNumber =RIGHT(100000000 + CONVERT(bigint, ABS(CHECKSUM(NEWID()))), 10)
 
 if(select count(*) from  estate_base_RoomNumbers  where PayNumber=@PayNumber)=0 and LEN(@PayNumber)=10
 break;


 
SET @row = @row+1 


END 


update estate_base_RoomNumbers set PayNumber=@PayNumber where FID=@orderN --操作数据库


fetch  next from ee into @orderN


END


CLOSE ee;
DEALLOCATE ee;