sql 游标

--创建游标

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 cursor
set @text_cursor=cursor 
for select * from #tb

案例：动态和静态的游标区别 ---- 游标打开后，对数据表删除，动态的游标会及时的更新数据表，静态游标不会及时的更新数据表IF OBJECT_ID('tempdb..#tb') IS NOT NULL

<em id="__mceDel">  
    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 --static
for select* from #tb
--游标打开前删除
delete top(1) from #tb where id=4 select 'before cursor open',* from #tb
open cur_tb
--游标打开后删除
delete top(1) from #tb where id=3 select 'after cursor open',* from #tb
fetch next from cur_tb
fetch cur_tb while @@FETCH_STATUS=0
begin
 delete top(1) from #tb where id=1 
 fetch cur_tb
end<br><br>--当一次读取赋给变量时，要注意表字段数与变量数一致<br></em>

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 表1
 
ID     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 表2
 
ID        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 table1
select * from table2
 
create function GetNameStr(@name nvarchar(10))
returns nvarchar(800)
as
begin
   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 @nameStr
end
 
--游标写法得到的结果
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