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

DROP TABLE #t;