SQL SERVER 中几种遍历表的方法

</pre>建表语句：</h3><div><pre name="code" class="sql">CREATE TABLE BlogType  (       ID int  identity(1,1) primary key,   TypeName varchar(50)      )

提供数据:

insert into BlogType (TypeName) values ('.Net'),('Java'),('PHP'),('Python'),('Object-c'),('SQL'),('C'),('C++');

使用游标：

--游标遍历  DECLARE @typeName varchar(50);  DECLARE type_cursor CURSOR FOR SELECT TypeName FROM BlogType;  OPEN type_cursor  FETCH NEXT FROM type_cursor INTO @typeName  WHILE(@@FETCH_STATUS=0)  BEGIN  PRINT @typeName;  FETCH NEXT FROM type_cursor INTO @typeName  END  CLOSE type_cursor  DEALLOCATE type_cursor;

模仿for循环的表变量

--模仿for循环的表变量DECLARE @pointer INT;DECLARE @rowCount INT;DECLARE @typeName VARCHAR(50)DECLARE @t TABLE( RowNumber INT, TypeName VARCHAR(50));INSERT @t        ( RowNumber, TypeName )SELECT RowNumber=ROW_NUMBER() OVER (ORDER BY bt.TypeName), bt.TypeName FROM BlogType bt WITH(NOLOCK) SELECT @rowCount= COUNT(1) FROM @t;SET @pointer=1;WHILE(@pointer<=@rowCount)BEGIN SELECT @typeName=TypeName FROM @t WHERE RowNumber=@pointer ; PRINT @typeName; SET @pointer=@pointer+1;END

使用表变量删除式遍历

--使用表变量删除式遍历DECLARE @typeName VARCHAR(50)DECLARE @t TABLE( TypeName VARCHAR(50));INSERT @t        (  TypeName)SELECT  TypeName FROM BlogType bt WITH(NOLOCK) WHILE EXISTS (SELECT TOP 1 * FROM @t)BEGIN SELECT TOP 1  @typeName=TypeName FROM @t; PRINT @typeName; DELETE FROM @t WHERE TypeName=@typeName;END

使用临时表删除式遍历

--使用临时表删除式遍历DECLARE @typeName VARCHAR(50);SELECT  TypeName INTO #t FROM BlogType bt WITH(NOLOCK);WHILE EXISTS (SELECT TOP 1 * FROM #t)BEGIN SELECT TOP 1  @typeName=TypeName FROM #t; PRINT @typeName; DELETE FROM #t WHERE TypeName=@typeName;ENDDROP TABLE #t;