杭州的地区代码_聚合广告联盟_百度一下首页_百度推广价格价目表

最近在测试项目功能时，需要生成一些随机的问题总数、改善数、改善率的数据，因此需要用到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 < @nBEGIN 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 @valueFETCH 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;ENDCLOSE myCursor;DEALLOCATE myCursor;