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;
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;