游标

游标格式
----------------------------
Declare 游标名 cursor for Select 字段1,字段2,...,字段n from  表名
Declare @变量1,@变量2,...,@变量n
open 游标名
fetch next  from 游标名 into @变量1,@变量2,...,@变量n
while @@FETCH_STATUS=0
 begin
  ...
  你的处理语句
  ...
  fetch next  from 游标名 into @变量1,@变量2,...,@变量n
end

close 游标名
deallocate 游标名

Eg:
----------------
 if exists (select * from sysobjects where id = OBJECT_ID('[t]') and OBJECTPROPERTY(id, 'IsUserTable') = 1) DROP TABLE [t]
 CREATE TABLE [t] (  [id] [int] NOT NULL , [T1] [nvarchar] (50) NULL , [T2] [nvarchar] (50) NULL )

 INSERT [t] ( [id] , [T1] , [T2] ) VALUES ( 8 , 'A' , '001' )
 INSERT [t] ( [id] , [T1] , [T2] ) VALUES ( 9 , 'D' , '001' )
 INSERT [t] ( [id] , [T1] , [T2] ) VALUES ( 10 , 'F' , '001' )
 INSERT [t] ( [id] , [T1] , [T2] ) VALUES ( 11 , 'D' , '002' )
 INSERT [t] ( [id] , [T1] , [T2] ) VALUES ( 12 , 'F' , '002' )
 INSERT [t] ( [id] , [T1] , [T2] ) VALUES ( 13 , 'F' , '003' )

---------------------------------
用游标实现
A,D,F,D,F,F

Declare T_SC cursor for Select T1 from  t
Declare @T varchar(8000)
Declare @T1 varchar(8000)
set @T=''
open T_SC
fetch next  from T_SC into @T1
while @@FETCH_STATUS=0
 begin
  set @T=@T+@T1+','
  fetch next  from T_SC into @T1
end

close T_SC
deallocate T_SC
print  @T

--------------------
用游标实现让所有Id=1 (当然 update T set Id=1 也办到)

Declare T_SC cursor for Select id from  t
Declare @id int
open T_SC
fetch next  from T_SC into @id
while @@FETCH_STATUS=0
 begin
  update T set id=1 from T where id=@id
  fetch next  from T_SC into @id
end

close T_SC
deallocate T_SC

--Test
select * from T

---------------------------------------------------------------------------------------------
嵌套游标
----------------------------
create table A
(
   id int
)

create table A_i
(
   id int,
   id_i int,
   T_name varchar(10)
)

insert A select 1
insert A select 2
insert A select 3

insert A_i select 1,1,'A'
insert A_i select 1,2,'B'
insert A_i select 1,3,'C'
insert A_i select 2,1,'D'
insert A_i select 2,2,'E'
insert A_i select 3,1,'F'
insert A_i select 3,3,'G'
insert A_i select 3,4,'H'

select * from A
select * from A_i

-------
用游标实现 ABCDEFGH  两层的

Declare T_SC cursor for Select id from A
Declare @Id int
Declare @Id_i int
Declare @T_name varchar(10)
Declare @T varchar(8000)
set @T=''
open T_SC
fetch next  from T_SC into @Id
while @@FETCH_STATUS=0
 begin
    Declare T_SC1 cursor for Select T_name from A_i where id=@Id      ---内部游标
    open T_SC1
       fetch next  from T_SC1 into @T_name
       while @@FETCH_STATUS=0
       begin
          set @T=@T+@T_name
          fetch next  from T_SC1 into @T_name
       end
    close T_SC1
    deallocate T_SC1
  fetch next  from T_SC into @Id
end
close T_SC
deallocate T_SC

print @T

---------------------------------------------------------------------------------------------
游标的帮助文件

1.声明游标
  DECLARE CursorName(游标名,不用@) CURSOR FOR
  select...(SQL语句)
2.打开游标,检索数据并填充游标
  OPEN CursorName(同上)

3.Fetch操作(移动游标)
  FETCH NEXT FROM CursorName(同上)
WHILE @@FETCH_STATUS = 0

FETCH FIRST
提取游标中的第一行。
FETCH NEXT
提取上次提取行之后的行。
FETCH PRIOR
提取上次提取行之前的行。
FETCH LAST
提取游标中的最后一行。
FETCH ABSOLUTE n
如果 n 为正整数，则提取游标中从第 1 行开始的第 n 行。如果 n 为负整数，则提取游标中的倒数第 n 行。如果 n 为 0，则没有行被提取。
FETCH RELATIVE n
提取上次所提取行之后的第 n 行。如果 n 为正数，则提取所上次提取行之后的第 n 行。如果 n 为负数，则提取上次所提取行之前的第 n 行。如果 n 为 0，则同一行被再次提取

4.关闭游标(Close命令是与open命令相对应) 注:关闭后可以用open命令再打开
  Close Cursorname(同上)
5.释放游标,释放相关的内存(Deallocate命令是与Declare命令相对应)
  Deallocate Cursorname(同上)

 

 

----------------------------------------
CURSOR SCROLL For的使用方法
 if exists (select * from sysobjects where id = OBJECT_ID('[t]') and OBJECTPROPERTY(id, 'IsUserTable') = 1) DROP TABLE [t]
 CREATE TABLE [t] (  [id] [int] NOT NULL , [T1] [nvarchar] (50) NULL , [T2] [nvarchar] (50) NULL )
go

 INSERT [t] ( [id] , [T1] , [T2] ) VALUES ( 8 , 'A' , '001' )
 INSERT [t] ( [id] , [T1] , [T2] ) VALUES ( 9 , 'D' , '001' )
 INSERT [t] ( [id] , [T1] , [T2] ) VALUES ( 10 , 'F' , '001' )
 INSERT [t] ( [id] , [T1] , [T2] ) VALUES ( 11 , 'D' , '002' )
 INSERT [t] ( [id] , [T1] , [T2] ) VALUES ( 12 , 'F' , '002' )
 INSERT [t] ( [id] , [T1] , [T2] ) VALUES ( 13 , 'F' , '003' )
go

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

declare @i int
set @i=1
Declare T_SC CURSOR SCROLL For Select T1 from  t        --注意 这个地方与 cursor for 不同
Declare @T varchar(8000)
Declare @T1 varchar(8000)
set @T=''
open T_SC
fetch next  from T_SC into @T1
while @i<3 --@@FETCH_STATUS=0
 begin
  set @T=@T+@T1+','
 
  fetch next  from T_SC into @T1
  if @@FETCH_STATUS<>0 and @i<3                 --如果上面的地方用cursor for 这个地方将出错
  begin
    fetch FIRST  from T_SC into @T1
    set @i=@i+1
  end
end
close T_SC
deallocate T_SC
print  @T