SQL游标操作

select count(id) from info

select * from info

--清除所有记录
truncate table info

declare @i int
set @i=1
while @i<1000000
begin
 insert into info values('Justin'+str(@i),'深圳'+str(@i))
 set @i=@i+1
end

--声明游标
declare my_cursor cursor keyset for select * from info
--删除游标资源
deallocate my_cursor

--打开游标,在游标关闭或删除前都有效
open my_cursor
--关闭游标
close my_cursor

--声明局部变量
declare @id int,@name varchar(20),@address varchar(20)
--定位到指定位置的记录
fetch absolute 56488 from my_cursor into @id,@name,@address
select @id as id,@name as name,@address as address
--定位到当前记录相对位置记录
fetch relative -88 from my_cursor into @id,@name,@address
select @id as id,@name as name,@address as address
--定位到当前记录前一条
fetch prior from my_cursor into @id,@name,@address
select @id as id,@name as name,@address as address
--定位到当前记录后一条
fetch next from my_cursor into @id,@name,@address
select @id as id,@name as name,@address as address
--定位到首记录
fetch first from my_cursor into @id,@name,@address
select @id as id,@name as name,@address as address
--定位到尾记录
fetch last from my_cursor into @id,@name,@address
select @id as id,@name as name,@address as address