不用游标也能遍历记录的sql语句实例

--声明变量表@tb
declare @tb table(id int,name varchar(50))


--添加测试数据
insert into @tb
select  6,'aa' union all
select  7,'bb' union all
select  8,'cc' union all
select  9,'dd' union all
select  10,'abc' union all
select  11,'ddef' union all
select  12,'fda' union all
select  13,'rewr' union all
select  14,'eyt' union all
select  15,'jjy' union all
select  16,'bbbxd' union all
select  17,'xxx' union all
select  18,'ffff' union all
select  19,'wwwwwwww' union all
select  20,'aaaaaaaaaa' 


/*
查看表中数据
select * from @tb 
*/


--声明循环用的“指针”
declare @min varchar(5)
--赋初值
select  @min=min(id) from @tb  
--开始循环
while @min is not null
begin
  print @min  --打印当前“指针”的值
  select  @min=min(id) from @tb where id>@min  --更新“指针”内容，使之移到下一记录

end

----------------------------------------------------------------------------------------------

declare @temp table   
(   
   [id] int IDENTITY(1,1),   
   [Name] varchar(10)   
)   
declare @tempId int,@tempName varchar(10)   
 
insert into @temp values('a')   
insert into @temp values('b')   
insert into @temp values('c')   
insert into @temp values('d')   
insert into @temp values('e')   
 
--select * from @temp   
 
WHILE EXISTS(select [id] from @temp)   
begin   
SET ROWCOUNT 1    
select @tempId = [id],@tempName=[Name] from @temp   
SET ROWCOUNT 0   
delete from @temp where [id] = @tempId   
 
print 'Name:----'+@tempName   
end  

---

--定义表变量

DECLARE @temp TABLE

    (

      [id] INT IDENTITY(1, 1) ,

      [Name] VARCHAR(10)

    )  

DECLARE @tempId INT ,

    @tempName VARCHAR(10)  

DECLARE test_Cursor CURSOR LOCAL FOR

SELECT   [id],[name] FROM @temp

--插入数据值

INSERT  INTO @temp

VALUES  ( 'a' )  

INSERT  INTO @temp

VALUES  ( 'b' )  

INSERT  INTO @temp

VALUES  ( 'c' )  

INSERT  INTO @temp

VALUES  ( 'd' )  

INSERT  INTO @temp

VALUES  ( 'e' )  

 

--打开游标

OPEN test_Cursor

WHILE @@FETCH_STATUS = 0

    BEGIN  

        FETCH NEXT FROM test_Cursor INTO @tempId,@tempname

        PRINT 'Name:----' + @tempName  

    END 

CLOSE test_Cursor

DEALLOCATE test_Cursor