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