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
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
--当一次读取赋给变量时，要注意表字段数与变量数一致
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
