最近在测试项目功能时，需要生成一些随机的问题总数、改善数、改善率的数据，因此需要用到SqlServer的循环，在这里记录一下。下面以随机生成十条数据为例，表结构如下:

CREATE TABLE re_class(
	guid NVARCHAR(50) primary key,
	totalCnt VARCHAR(10),
	improveCnt VARCHAR(10),
	improveRate VARCHAR(10)
)

下面以插入十条数据为例：

1. while
   用于简单的循环操作

基本结构：

DECLARE @i INT = 1; -- 循环变量
DECLARE @n INT = 10; 
	WHILE @i < @n  --循环条件
		BEGIN 
			--操作
		END

例子：

DECLARE @i INT = 1;
DECLARE @n INT = 10;
DECLARE @totalCnt VARCHAR(10);
DECLARE @improveCnt VARCHAR(10);
DECLARE @improveRate VARCHAR(10);
WHILE @i < @n
	BEGIN 
		SET @totalCnt = ABS(CHECKSUM(NEWID())) % (100 - 1) + 1;
		SET @improveCnt =  ABS(CHECKSUM(NEWID())) % @totalCnt;
		SET @improveRate = @improveCnt * 100 / @totalCnt;
		INSERT INTO re_class VALUES (NEWID(), @totalCnt, @improveCnt, @improveRate);
		SET @i = @i + 1;
	END

2. cursor
   cursor可以遍历表中的每一行

基本结构：

DECLARE myCursor CURSOR FOR 
SELECT value 
FROM [table];--指向哪张表

DECLARE @value NVARCHAR(30);
OPEN myCursor;--打开游标
FETCH NEXT FROM myCursor INTO @value;
WHILE @@FETCH_STATUS = 0 --循环条件
BEGIN 
	PRINT @value
	FETCH NEXT FROM myCursor INTO @value;--将下一个游标的数据放入@value变量中
END
--关闭并释放游标
ClOSE myCursor;
DEALLOCATE myCursor;

打印表中数据的例子：

DECLARE myCursor CURSOR FOR 
	SELECT * FROM re_class;
	DECLARE @guid NVARCHAR(50), @totalCnt VARCHAR(10),@improveCnt VARCHAR(10), @improveRate VARCHAR(10);
	OPEN myCursor;
	FETCH NEXT FROM myCursor INTO @guid, @totalCnt, @improveCnt, @improveRate;
	WHILE @@FETCH_STATUS = 0 
		BEGIN 
			PRINT 'guid=' + @guid + '@totalCnt=' + @totalCnt +
				'，@improveCnt=' + @improveCnt + '，improveRate=' + @improveRate + '%';
			FETCH NEXT FROM myCursor INTO @guid, @totalCnt, @improveCnt, @improveRate;
		END
	CLOSE myCursor;
	DEALLOCATE myCursor;