SQL Server游标语句
SQL Server游标语句
1.声明游标
declare myCursor cursor
[global | local]
[forward_only | scroll]
[static | keyset | dynamic | fast_forward]
[read_only | optimistic | scroll_locks]
for select_statement
[for update [of column_name [,...n] ]]
注解：
global表示全局游标（默认），local表示本地游标
forward_ony表示只进游标，默认方式；scroll表示滚动游标
static表示静态游标（外部更新游标内不体现）；keyset表示键集驱动游标（外部更新游标内不体现）；dynamic表示动态游标，默认方式；fast_forward表示read_only + forward_only（外部更新游标内体现）
read_only表示只读（游标内不支持更新）；optimistic表示乐观并发控制；scroll_locks表示悲观并发控制（更新锁）
*static，forward_only和keyset，默认为（有且只能为）read_only，不支持for update
*dynamic，默认为optimistic
游标内只要不涉及delete, update操作，就不必考虑并发问题，锁机制；否则必须考虑，乐观并发控制，悲观并发控制。
乐观并发控制：数据被读入游标后，如果游标某行数据已经发生变化，那么游标内的更新、删除操作会失败。
悲观并发控制（事务内）：数据被读入游标后，如果游标某行数据已经发生变化，那么游标内的更新、删除操作总会成功。
定义游标变量
declare @myVariable cursor
set @myVariable = myCursor
2.打开游标
open currsor_name 或 @currsor_variable_name
3.读取游标中的数据
fetch [next | prior | first | last | absolute{n} | relative{n}]
from currsor_name 或 @currsor_variable_name
into @variable_name
4.关闭游标
close currsor_name 或 @currsor_variable_name
5.释放游标
deallocate currsor_name 或 @currsor_variable_name
例子1：静态游标（默认只读并发）
use test
go
declare @id int
declare @name varchar(20)
declare myCursor cursor
static
for select id, name from t1
open myCursor
fetch next from myCursor into @id, @name
while @@fetch_status = 0
begin
select @name --一直未变
select * from t1
fetch next from myCursor into @id, @name
waitfor delay '00:00:10'
end
close myCursor
deallocate myCursor
----------------------------------------------------------
use test
go
update t1 set t1.name = 'tttt'
----------------------------------------------------------
t1(id, name)
97 hello
14 hello
16 hello
111 hello
结果：
hello
hello
hello
hello
说明外部更新的操作在游标中并未体现。
例子2：静态游标（默认只读并发）
use test
go
declare @id int
declare @name varchar(20)
declare myCursor cursor
static
for select id, name from t1
for update
open myCursor
fetch next from myCursor into @id, @name
update set t1.name = 'oooo' where current of myCursor
while @@fetch_status = 0
begin
print @name
update set t1.name = 'oooo' where current of myCursor
fetch next from myCursor into @id, @name
waitfor delay '00:00:10'
end
close myCursor
deallocate myCursor
结果：出错。
Server: Msg 1048, Level 15, State 1, Line 16
Conflicting cursor options FOR UPDATE and STATIC.
说明静态游标内不允许更新，即不支持for update。
例子3：fast_forward（默认只读并发+只进游标）
use test
go
declare @id int
declare @name varchar(20)
declare myCursor cursor
fast_forward
for select id, name from t1
open myCursor
fetch next from myCursor into @id, @name
while @@fetch_status = 0
begin
select @name --会因外部改变而改变
select * from t1
fetch next from myCursor into @id, @name
waitfor delay '00:00:10'
end
close myCursor
deallocate myCursor
----------------------------------------------------------
use test
go
update t1 set t1.name = 'tttt'
----------------------------------------------------------
t1(id, name)
97 hello
14 hello
16 hello
111 hello
结果：
hello
hello
hello
tttt
说明外部的更新操作在游标中已体现，同样删除操作也会在游标中体现。
例子4：fast_forward（默认只读并发+只进游标），不支持for update
use test
go
declare @id int
declare @name varchar(20)
declare myCursor cursor
fast_forward
for select id, name from t1
for update
open myCursor
fetch next from myCursor into @id, @name
while @@fetch_status = 0
begin
print @name
fetch next from myCursor into @id, @name
waitfor delay '00:00:10'
end
close myCursor
deallocate myCursor
结果：出错。
Server: Msg 1048, Level 15, State 1, Line 16
Conflicting cursor options FAST_FORWARD and FOR UPDATE.
说明该游标不支持for update；游标外部可以更新。
例子5：键集驱动游标
use test
go
declare @id int
declare @name varchar(20)
declare myCursor cursor
keyset
for select id, name from t1
open myCursor
fetch next from myCursor into @id, @name
while @@fetch_status = 0
begin
print @name --从打开游标后，就一直未变
fetch next from myCursor into @id, @name
waitfor delay '00:00:10'
end
close myCursor
deallocate myCursor
----------------------------------------------------------
use test
go
update t1 set t1.name = 'tttt'
----------------------------------------------------------
t1(id, name)
97 hello
14 hello
16 hello
111 hello
结果：
hello
hello
hello
hello
说明外部的更新操作在游标中未体现，同样删除操作未体现。
例子6：键集驱动游标，不支持for update
use test
go
declare @id int
declare @name varchar(20)
declare myCursor cursor
keyset
for select id, name from t1
for update
open myCursor
fetch next from myCursor into @id, @name
while @@fetch_status = 0
begin
print @name
update t1 set t1.name = 'ooo' where current of myCursor
fetch next from myCursor into @id, @name
waitfor delay '00:00:10'
end
close myCursor
deallocate myCursor
结果：出错。
Server: Msg 16957, Level 16, State 4, Line 10
FOR UPDATE cannot be specified on a READ ONLY cursor.
例子7：动态游标（默认乐观并发）--特别注意
declare @id int
declare @name varchar(20)
declare myCursor cursor
dynamic
for select id, name from t1
for update
open myCursor
fetch next from myCursor into @id, @name
while @@fetch_status = 0
begin
select @name
select * from t1
update t1 set t1.name = 'oooo' where current of myCursor
fetch next from myCursor into @id, @name
waitfor delay '00:00:10'
end
close myCursor
deallocate myCursor
----------------------------------------------------------
use test
go
update t1 set t1.name = 'tttt'
----------------------------------------------------------
t1(id, name)
97 hello
14 hello
16 hello
111 hello
结果： 出错。
Server: Msg 16934, Level 10, State 1, Line 28
Optimistic concurrency check failed. The row was modified outside of this cursor.
Server: Msg 16947, Level 10, State 1, Line 28
No rows were updated or deleted.
The statement has been terminated.
t1：
97 tttt
14 tttt
16 oooo
111 oooo
例子：动态游标（只读并发），不支持for update
use test
go
declare @id int
declare @name varchar(20)
declare myCursor cursor
dynamic read_only
for select id, name from t1
for update
open myCursor
fetch next from myCursor into @id, @name
while @@fetch_status = 0
begin
print @name
fetch next from myCursor into @id, @name
update t1 set t1.name = 'oooo' where current of myCursor
waitfor delay '00:00:10'
end
close myCursor
deallocate myCursor
结果：出错。
Server: Msg 1048, Level 15, State 1, Line 16
Conflicting cursor options FOR UPDATE and READ_ONLY.
例子：动态游标（悲观并发）--一定要放在事务中，更新锁在事务内一直有效；否则，update之后就释放更新锁，易造成死锁。
begin tran
declare @id int
declare @name varchar(20)
declare myCursor cursor
dynamic scroll_locks
for select id, name from t1
for update
open myCursor
fetch next from myCursor into @id, @name
while @@fetch_status = 0
begin
select @name
select * from t1
update t1 set t1.name = 'oooo' where current of myCursor
fetch next from myCursor into @id, @name
waitfor delay '00:00:10'
end
close myCursor
deallocate myCursor
commit tran
go
-----------------------------------------------------------------------
use test
go
update t1 set t1.name = 'tttt'
结果：
97 tttt
14 tttt
16 tttt
111 tttt
先执行带有游标的事务，然后再执行更新。串行执行。
例子：省去参数的游标--动态游标
use test
go
declare @id int
declare @name varchar(20)
declare myCursor cursor
for select id, name from t1
open myCursor
fetch next from myCursor into @id, @name
while @@fetch_status = 0
begin
print @name
fetch next from myCursor into @id, @name
waitfor delay '00:00:10'
end
close myCursor
deallocate myCursor
-----------------------------------------------------------
use test
go
update t1 set t1.name = 'tttt'
结果：
hello
hello
tttt
tttt
说明默认为动态游标
转自 http://qiutian110119.blog.163.com/blog/static/54617311201062111113734/
