MSSQL示例（四）定义服务器游标VS游标变量

-- =============================================

-- Author:  tomtom
-- Create date: 2015.2.28
-- Description:定义服务器游标VS游标变量

-- =============================================

----1.示例数据准备

create table t1
(
id int
)
go

create table t2
(
name int
)
go

insert into t1
select 1
insert into t2
select 21 union all
select 11

select * from t1
select * from t2

----2.1定义服务器游标DECLARE my_cur CURSOR
--定义时必须加FOR子句了，并且不能作为变量被赋值如set cur_t1 =
declare cur_t1 CURSOR FOR select * from t1
open cur_t1
declare @name1 int

fetch next from cur_t1 into @name1
select @name1
select @name1=-1

fetch next from cur_t1 into @name1 --没有行了，@name1还是上次的值
select @name1

close cur_t1
deallocate cur_t1

----2.2游标变量示例DECLARE @my_cur CURSOR
--重新设置后，不重新打开将提示“游标未打开。”
declare @cur_t2 CURSOR 
set @cur_t2 = CURSOR FOR select * from t2
open @cur_t2
declare @name int

fetch next from @cur_t2 into @name
select @name

--re set cursor
set @cur_t2 = CURSOR FOR select * from t1
open @cur_t2
fetch next from @cur_t2 into @name
select @name

close @cur_t2
deallocate @cur_t2