Sql Server 游标概念与实例

xiaowei8782088

引言
　　先不讲游标的什么概念，看如下Sql Server2008 图例：

　　需求：两张表的O_ID是一一对应的，现在求将加薪的工资+原来的工资=现在的工资，也就是O_Salary=O_Salary+A_Salary,修改表OriginSalary的工资字段。
　　对于一些不熟悉游标的程序员来说，这个并不是什么很难的问题，这个问题用程序来实现可能也很简单。
　　我先说说，用ASP.NET程序解决这个问题的思路：
　　1. 先获得表OriginSalary的记录数，写个循环。
　　2.       写SQL语句“select * from dbo.OriginSalary as A left join dbo.AddSalary as B on A.O_ID=B.O_ID”获得视图。
　　3.       使用Dataset获得O_Salary=O_Salary+A_Salary。
　　4.       写UPDATE语句“update OriginSalary set O_Salary=”相加的值” where O_ID=”获得值”
　　5.       循环3次，完成此功能。
　　还有一种方法就是写存储过程，在这里我就不列出来了。
　　“难道数据库不能一行一行的处理数据吗？将表AddSalary的数据逐行的取出，然后表 OriginSalary数据逐行的修改？”答案当然是肯定。这就是游标概念。

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

1.2 游标的优点
　　从游标定义可以得到游标的如下优点，这些优点使游标在实际应用中发挥了重要作用：
　　1）允许程序对由查询语句select返回的行集合中的每一行执行相同或不同的操作，而不是对整个行集合执行同一个操作。
　　2）提供对基于游标位置的表中的行进行删除和更新的能力。
　　3）游标实际上作为面向集合的数据库管理系统（RDBMS）和面向行的程序设计之间的桥梁，使这两种处理方式通过游标沟通起来。


　　1.3 游标的使用

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


　　1.3.1 声明游标

　　最简单游标声明：
　　DECLARE <游标名>CURSOR FOR<SELECT语句>；
　　【注】select语句可以是简单查询，也可以是复杂的接连查询和嵌套查询
　　【例】已表AddSalary
　　Declare mycursor cursor for select * from AddSalary
　　这样我就对表AddSalary申明了一个游标mycursor
　　【高级备注】
　　DECLARE <游标名> [INSENSITIVE] [SCROLL] CURSORFOR<SELECT语句>

[INSENSITIVE]

　　表明MS SQL SERVER 会将游标定义所选取出来的数据记录存放在一临时表内（建立在tempdb 数据库下）。对该游标的读取操作皆由临时表来应答。因此，对基本表的修改并不影响游标提取的数据，即游标不会随着基本表内容的改变而改变，同时也无法通过游标来更新基本表。如果不使用该保留字，那么对基本表的更新、删除都会反映到游标中。
　　另外应该指出，当遇到以下情况发生时，游标将自动设定INSENSITIVE 选项。
　　a.在SELECT 语句中使用DISTINCT、 GROUP BY、 HAVING UNION 语句；
　　b.使用OUTER JOIN；
　　c.所选取的任意表没有索引；
　　d.将实数值当作选取的列。

[SCROLL]

　　表明所有的提取操作（如FIRST、 LAST、 PRIOR、 NEXT、>　　重开游标。

1.3.2 打开游标
　　OPEN mycursor


　　1.3.3 读取数据

　　FETCH [ NEXT | PRIOR | FIRST | LAST] FROM { 游标名  | @游标变量名 } [ INTO @变量名 [，…] ]
　　参数说明：
　　NEXT   取下一行的数据，并把下一行作为当前行(递增)。由于打开游标后，行指针是指向该游标第1行之前，所以第一次执行FETCH NEXT操作将取得游标集中的第1行数据。NEXT为默认的游标提取选项。
　　INTO @变量名[,…]  把提取操作的列数据放到局部变量中。列表中的各个变量从左到右与游标结果集中的相应列相关联。各变量的数据类型必须与相应的结果列的数据类型匹配或是结果列数据类型所支持的隐性转换。变量的数目必须与游标选择列表中的列的数目一致。
　　当游标被打开时，行指针将指向该游标集第1行之前，如果要读取游标集中的第1行数据，必须移动行指针使其指向第1行。
　　就本例而言，可以使用下列操作读取第1行数据：
　　Eg: Fetch next from mycursor 或则 Fetch first from mycursor
　　将取出的数据赋给变量：
　　//声明2个变量
　　declare @O_ID NVARCHAR(20)
　　declare @A_Salary float
　　//将取出的值传入刚才声明的2个变量
　　Fetch next from mycursor into @ O_ID,@ A_Salary


　　1.3.4 关闭游标

　　CLOSE mycursor

            1.3.5 删除游标
　　DEALLOCATE mycursor      

1.3.6 实例
　　用游标取出表2 AddSalary的数据。
　　为了运行创建的游标，将游标写在存储过程里，方便我们看到游标的整个使用过程。
　　在sqlserver2008中新建一个存储过程：
　　

1 CREATE PROCEDURE PK_Test　　

2 AS　　
3 --声明2个变量
　　
4 declare @O_ID nvarchar(20)
　　
5 declare @A_Salary float
　　
6
　　
7 --声明一个游标mycursor，select语句中参数的个数必须要和从游标取出的变量名相同
　　
8 declare mycursor cursor for select O_ID,A_Salary from AddSalary
　　
9
　　
10 --打开游标
　　
11 open mycursor
　　
12
　　
13 --从游标里取出数据赋值到我们刚才声明的2个变量中
　　
14 fetch next from mycursor into @O_ID,@A_Salary
　　
15
　　
16 --判断游标的状态
　　
17 --0 fetch语句成功
　　
18 -- -1 fetch语句失败或此行不在结果集中
　　
19 -- -2被提取的行不存在
　　
20 while (@@fetch_status=0)
　　
21 begin
　　
22
　　
23 --显示出我们每次用游标取出的值
　　
24 print '游标成功取出一条数据'
　　
25 print @O_ID
　　
26 print @A_Salary
　　
27
　　
28 --用游标去取下一条记录
　　
29 fetch next from mycursor into @O_ID,@A_Salary
　　
30 end
　　
31 --关闭游标
　　
32 close mycursor
　　
33 --撤销游标
　　
34 deallocate mycursor
　　
35 GO
　　

　　那我们就运行这个存储过程，看看游标到底是怎么取值的：
　　我们打开SQLSERVER2008的查询分析器，制定好数据库后，我们执行存储过程
　　Exec PK_Test
　　效果如下：

　　现在我们写一个存储过程使用游标解决引言中的问题：
　　

CREATE PROCEDURE PK_SalaryAdd　　

AS　　
declare @O_ID nvarchar(20),@A_Salary float
　　
declare mycursor cursor for select O_ID,A_Salary from AddSalary
　　
open mycursor
　　
fetch next from mycursor into @O_ID,@A_Salary
　　
while(@@fetch_status = 0)
　　
begin
　　
Update OriginSalary set O_Salary=O_Salary+@A_Salary where O_ID=@O_ID
　　
fetch next from mycursor into @O_ID,@A_Salary
　　
end
　　
close mycursor
　　
deallocate mycursor
　　
GO
　　

　　执行存储过程
　　Exec PK_SalaryAdd
　　效果如下：

　　执行存储过程，看到我们影响了3行数据
　　用sql语句，看看表OriginSalary现在的结果：   

　　【简单示例补充】
　　select * from dbo.bank
　　1 张三 10001
　　2 李四 10001
　　3 王五 10001
　　4 小儿 10001
　　

declare @bankid int　　
declare @username varchar(50)
　　
declare @rmbnum varchar(50)
　　
declare cursor1 cursor for   --定义游标cursor1
　　
select * from dbo.bank  ----使用游标的对象(跟据需要填入select表)
　　
open cursor1      --打开游标
　　
fetch next from cursor1 into @bankid ,@username,@rmbnum  --将游标向下移1行，获取的数据放入之前定义的变量@bankid ,@name,@rmbnum 中
　　
while @@fetch_status=0           --判断是否成功获取数据
　　
begin
　　
update dbo.bank set rmbnum=@rmbnum+1
　　
where bankid=@bankid                           --进行相应处理(跟据需要填入SQL文)
　　
fetch next from cursor1 into @bankid ,@username,@rmbnum   --将游标向下移1行，获取的数据放入之前定义的变量@bankid ,@name,@rmbnum 中
　　
end
　　
close cursor1                   --关闭游标
　　
deallocate cursor1   --删除游标与游标名称或游标变量之间的关联。
　　

　　运行代码后
　　select * from dbo.bank
　　1 张三 10002
　　2 李四 10002
　　3 王五 10002
　　4 小儿 10002
　　0 FETCH 语句成功
　　-1 FETCH 语句失败或此行不在结果集中
　　-2 被提取的行不存在