SQL Server 游标例子

最新推荐文章于 2024-03-28 13:45:00 发布

原创最新推荐文章于 2024-03-28 13:45:00 发布 · 154 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#数据库

Database 专栏收录该内容

104 篇文章

订阅专栏

本文详细介绍了SQL中游标的使用方法，包括声明、打开、关闭和删除游标等基本操作，以及如何通过不同方式定位游标来获取特定记录。通过一个具体示例展示了游标的滚动读写特性。

-声明游标

declare my_cursor cursor keyset for select * from info

--删除游标资源

deallocate my_cursor

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

open my_cursor

--关闭游标

close my_cursor

--释放游标
deallocate 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

实例：

use database1

declare my_cursor cursor scroll dynamic

 /**//*scroll表示可随意移动游标指针（否则只能向前），dynamic表示可以读写游标（否则游标只读）*/

for

select productname from  product

open my_cursor

declare @pname sysname

fetch next from my_cursor into @pname

while(@@fetch_status=0)

  begin

    print 'Product Name: ' + @pname

    fetch next from my_cursor into @pname

  end

fetch first from my_cursor into @pname

print @pname

/**//*update product set productname='zzg' where current of my_cursor */

/**//*delete from product where current of my_cursor */

close my_cursor

deallocate my_cursor