SQL游标

一、概念

游标（cursor）它使用户可逐行访问SQL Server 返回的结果集。使用游标的一个主要的原因就是把集合操作转换为单个记录处理方式。用sql语言从数据库中检索数据后，结果放在内存的一块区域中，且结果往往是一个含有多个记录的集合。游标机制允许用户在sql server内逐行地访问这些记录，按照用户自己的意愿来显示和处理这些记录。

 

1. 游标的优点

（1）允许程序对由查询语句select返回的行集合中的每一行执行相同或不同的操作，而不是对整个行集合执行同一个操作。

（2）提供对基于游标位置的表中的行进行删除和更新的能力。

（3）游标实际上作为面向集合的数据库管理系统（RDBMS）和面向行的程序设计之间的桥梁，是这两种处理方式通过游标沟通起来。

 

2. 游标的使用

  使用顺序：声明游标、打开游标、读取数据、关闭游标、删除游标。

（1）最简单的游标声明：declare <游标名> cursor for  ;

  其中select语句可以使简单查询，也可以是复杂的连接查询和嵌套查询

  例子：delclare mycursor cursor for select * from table_name 

 

（2）高级备注：declare <游标名> [insensitive] [scroll] cursor for 

 1）insensitive

  表明会将游标定义所选取出来的数据记录存放在一临时表内（建立在tempdb数据库下）。对该游标的读取操作皆有临时表来应答。因此，对基本表的修改并不影响游标提取的数据，即游标不会随着基本表内容的改变而改变，同时也无法通过游标来更新基本表。如果不使用改保留字，n那么对基本表的更新、删除都会反映到游标中。

  当遇到以下情况发生时，游标将自动设定insensitive选项：

  ① 在select语句中使用distinct、group by、having union 语句；

  ② 使用outer join ；

  ③ 所选取的任意表没有索引；

  ④ 将实数值当作选取的列；

 2）scroll

  表名所有的提取操作（如：first、last、prior、next、relative、absolute）都可用。如果不使用该保留字，那么只能进行next提取操作。由此可见，scroll极大地增加了提取数据的灵活性，可以随意读取结果集中的任一行数据记录，而不必关闭再重开游标。

 

（3）打开游标

  open mycursor

（4）读取数据

  fetch [next | prior | first | last] from {游标名 | @游标变量名} [into @变量名 [, ...]]

  参数说明：

  ① next 取下一行的数据，并把下一行作为当前行（递增）。由于打开游标后，行指针是指向该游标第一行之前，所以第一次执行fetch next操作将取得游标集中的第一行数据。next为默认的游标提取选项。

  ② into @变量名[,...]把提取操作的列数据放到局部变量中。列表中的各个变量从左到右与游标结果集中的相应列相关联。各变量的数据类型必须与相应的结果列的数据类型匹配或是列数据类型所支持的隐性转换。变量的数目必须与游标选择列表中的列的数目一致。

  读取mycursor游标的数据

  当游标被打开时，行指针将指向该游标集第一行之前，如果要读取游标集中的第一行数据，必须移动行指针使其指向第一行。就本例而言，可以使用下列操作读取第一行数据：

  fetch next from mycursor 或 fetch first from mycursor

  这样我就取出了游标里的数据，但是光光这样还不够，我们还需要将取出的数据赋给变量：

  ① 声明2个变量declare @i_id nvarchar(20) delcare @i_name nvarchar(10)

  ② 将取出的值传入刚才声明的2个变量 fetch next from mycursor into @i_id, @i_name

 

（5）关闭游标

  close mycursor

 

（6）删除游标

  deallocate mycursor

 

三、示例

 

--创建存储过程
create proc pk_test
as 
  --声明两个变量
  declare @i_id nvarchar()
  declare @i_date datetime 
  --声明一个游标
  declare mycursor cursor for select i_id,i_date from one_table 
  --打开游标
  open mysursor
  --从游标里提取数据赋值到我们刚才声明的2两变量中
  fetch next from mycursor into @i_id,@i_date 
  --判断游标的状态（fetch语句成功；-fetch语句失败或此行不在结果集中；-2被提取的行不存在）
  while (@@fetch_status=)
  begin 
      print '游标成功取出一条数据'
      --显示出我们每次用游标取出的值
      print @i_id print @i_date
      --用游标去取下一条记录
      fetch next from mycursor int @i_id,@i_date
  end
  --关闭游标
  close mycursor
  --撤销游标
  deallocate mycursor
go
exec pk_test