DECLARE cursor_name CURSOR [ LOCAL | GLOBAL ]      [ FORWARD_ONLY | SCROLL ]      [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]      [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]      [ TYPE_WARNING ]      FOR select_statement      [ FOR UPDATE [ OF column_name [ ,...n ] ] ][;]　

--定义后直接赋值Declare text_cursor cursor for select * from #tb--先定义后赋值Declare text_cursor cursorset @text_cursor=cursor for select * from #tb

      DROP TABLE #tb    CREATE TABLE #tb(        id int PRIMARY KEY,        col sysname)    INSERT #tb(        id, col)    SELECT 1, ‘AA‘ UNION ALL    SELECT 2, ‘BB‘ UNION ALL    SELECT 3, ‘CC‘ UNION ALL    SELECT 4, ‘DD‘  declare cur_tb  cursor local forward_only read_only type_warning dynamic --staticfor select* from #tb--游标打开前删除delete top(1) from #tb where id=4 select ‘before cursor open‘,* from #tbopen cur_tb--游标打开后删除delete top(1) from #tb where id=3 select ‘after cursor open‘,* from #tbfetch next from cur_tbfetch cur_tb while @@FETCH_STATUS=0begin delete top(1) from #tb where id=1  fetch cur_tbend

--当一次读取赋给变量时，要注意表字段数与变量数一致

declare @id nvarchar(800)
declare @col nvarchar(800)
declare cur_tb cursor local forward_only read_only type_warning dynamic --static
for select* from #tb
open cur_tb
fetch next from cur_tb into @id,@col
print @id
print @col

create table table1( --drop table 表1ID     int,NAME   varchar(10),QQ     varchar(10),PHONE  varchar(20))insert into table1 values(1   ,‘秦云‘    ,‘10102800‘     ,‘13500000‘)insert into table1 values(2   ,‘在路上‘  ,‘10378‘        ,‘13600000‘)insert into table1 values(3   ,‘LEO‘     ,‘10000‘        ,‘13900000‘)create table table2( --drop table 表2ID        int,NAME    varchar(10) ,sjsj datetime,gly    varchar(10))insert into table2  values(1,‘秦云‘   ,cast(‘2004-1-1‘ as datetime),‘李大伟‘)insert into table2  values(2,‘秦云‘   ,cast(‘2005-1-1‘ as datetime),‘马化腾‘)insert into table2  values (3,‘在路上‘ ,cast(‘2005-1-1‘ as datetime),‘马化腾‘)insert into table2  values(4,‘秦云‘   ,cast(‘2005-1-1‘ as datetime),‘李大伟‘)insert into table2  values(5,‘在路上‘ ,cast(‘2005-1-1‘ as datetime),‘李大伟‘)select * from table1select * from table2create function GetNameStr(@name nvarchar(10))returns nvarchar(800)asbegin   declare @nameStr nvarchar(800)   declare @tempStr nvarchar(800)   declare @flag int   declare myCur cursor      for select gly from table2 t2 where t2.name=@name   open myCur   fetch next from myCur into @tempStr        set @flag=0   while @@fetch_status=0    begin	 if @flag=0	   begin		set @nameStr=@tempStr	   end	 else	   begin		set @nameStr=@nameStr+‘,‘+@tempStr	   end	    	  set @flag=@flag+1	  fetch next from myCur into @tempStr   end   close myCur   deallocate myCur   return @nameStrend--游标写法得到的结果select t2.NAME,COUNT(t2.ID) as 上级次数,dbo.GetNameStr(t2.NAME) from table2 t2 where t2.NAME in (select t1.name from table1 t1) group by t2.NAME--通过面向对象写法得到的结果select name,count(*) num,gly=stuff((select distinct ‘,‘+gly from table2 t1 where t1.NAME=t2.name for xml path(‘‘)),1,1,‘‘) from table2 t2 group by name

--定义游标时，如果不是特别需要，使用LOCAL关键显式的将游标定义为局部游标，--	尽量避免使用全局（GLOBAL，这是数据库的默认行为）游标；没有特殊需要的话，--	尽量使用FORWARD_ONLY READ_ONLY STATIC游标, --FAST_FORWARD可以理解成FORWARD_ONLY的优化版本.FORWARD_ONLY执行的是静态计划，--而FAST_FORWARD是根据情况进行选择采用动态计划还是静态计划，--大多数情况下FAST_FORWARD要比FORWARD_ONLY性能略好.--READ_ONLY意味着声明的游标只能读取数据,游标不能做任何更新操作--    SCROLL_LOCKS是另一种极端，将读入游标的所有数据进行锁定，防止其他程序进行更改，以确保更新的绝对成功 --   OPTIMISTIC是相对比较好的一个选择，OPTIMISTIC不锁定任何数据，当需要在游标中更新数据时,如果底层表数据更新，则游标内数据更新不成功，如果，底层表数据未更新，则游标内表数据可以更新  --(1) 尽管使用游标比较灵活，可以实现对数据集中单行数据的直接操作，但游标会在下面几个方面影响系统的性能： --            使用游标会导致页锁与表锁的增加 --            导致网络通信量的增加--             增加了服务器处理相应指令的额外开销 --       (2) 使用游标时的优化问题： --            明确指出游标的用途：for read only或for update--             在for update后指定被修改的列 --http://www.cnblogs.com/CareySon/archive/2011/11/01/2231381.html --http://www.cnblogs.com/knowledgesea/p/3699851.html --http://blog.csdn.net/szstephenzhou/article/details/7244949