游标正例

游标

游标（cursor）是系统为用户开设的一个数据缓冲区，存放SQL语句的执行结果。
每个游标区都有一个名字,用户可以用SQL语句逐一从游标中获取记录，并赋给主变量，交由主语言进一步处理。

游标是处理结果集的一种机制吧，它可以定位到结果集中的某一行，多数据进行读写，也可以移动游标定位到你所需要的行中进行操作数据。一般复杂的存储过程，都会有游标的出现，他的用处主要有：

定位到结果集中的某一行。
对当前位置的数据进行读写。
可以对结果集中的数据单独操作，而不是整行执行相同的操作。
是面向集合的数据库管理系统和面向行的程序设计之间的桥梁。

%ISOPEN 检测游标是否打开。
%FOUND 检测游标结果集是否存在数据。
%NOTFOUND是否不存在数据。
%ROWCOUNT游标已提取的实际行数。

注：

在能够使用游标前，必须声明/定义它，过程中没有检索数据，只是定义要使用的select语句一旦声明就必须打开游标开始检索数据对于填有数据的游标，根据需要检索各行取出数据必须关闭游标再删除游标使用声明过的游标不需要再次声明，open打开就行了（只要没deallocate就行）

%FOUND 检测游标结果集是否存在数据。
%NOTFOUND 是否不存在数据。
%ROWCOUNT 游标已提取的实际行数。

建立临时数据表

create table #tmp (Id NVARCHAR(MAX),UserName NVARCHAR(MAX),Password NVARCHAR(MAX),NickName NVARCHAR(MAX)) –创建临时表
SELECT * FROM #tmp; –查询临时表
DROP TABLE #tmp –删除临时表

游标的生命周期

游标的生命周期包含有五个阶段：声明游标、打开游标、读取游标数据、关闭游标、释放游标。

1、游标格式

--声明游标
DECLARE cursor_name CURSOR [ LOCAL | GLOBAL ] 
     [ FORWARD_ONLY | SCROLL ] 
     [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ] 
     [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ] 
     [ TYPE_WARNING ] 
     FOR select_statement 
     [ FOR UPDATE [ OF column_name [ ,...n ] ] ]
--打开游标语法
open [ Global ] cursor_name | cursor_variable_name
--提取游标语法
Fetch
[ [Next|prior|Frist|Last|Absoute n|Relative n ]
from ]
[Global] cursor_name
[into @variable_name[,....]]
--关闭游标语法
close [ Global ] cursor_name | cursor_variable_name
--释放游标语法
deallocate  [ Global ] cursor_name | cursor_variable_name

--游标修改当前数据语法
Update 基表名 Set 列名=值[,...] Where Current of 游标名
--游标删除当前数据语法
Delete 基表名  Where Current of 游标名

参数说明：

参数名	描述
cursor_name	游标名称
Local	作用域为局部，只在定义它的批处理，存储过程或触发器中有效。
Global	作用域为全局，由连接执行的任何存储过程或批处理中，都可以引用该游标。
[Local/Global]	默认为local。
Forward_Only	指定游标智能从第一行滚到最后一行。Fetch Next是唯一支持的提取选项。如果在指定Forward_Only是不指定Static、KeySet、Dynamic关键字，默认为Dynamic游标。如果Forward_Only和Scroll没有指定，Static、KeySet、Dynamic游标默认为Scroll，Fast_Forward默认为Forward_Only
Static	静态游标
KeySet	键集游标
Dynamic	动态游标，不支持Absolute提取选项
Fast_Forward	指定启用了性能优化的Forward_Only、Read_Only游标。如果指定啦Scroll或For_Update，就不能指定他啦。
Read_Only	不能通过游标对数据进行删改。
Scroll_Locks	将行读入游标是，锁定这些行，确保删除或更新一定会成功。如果指定啦Fast_Forward或Static，就不能指定他啦。
Optimistic	指定如果行自读入游标以来已得到更新，则通过游标进行的定位更新或定位删除不成功。当将行读入游标时，sqlserver不锁定行，它改用timestamp列值的比较结果来确定行读入游标后是否发生了修改，如果表不行timestamp列，它改用校验和值进行确定。如果已修改改行，则尝试进行的定位更新或删除将失败。如果指定啦Fast_Forward,则不能指定他。
Type_Warning	指定将游标从所请求的类型隐式转换为另一种类型时向客户端发送警告信息。
For Update[of column_name ,….]	定义游标中可更新的列。
Frist/Last	结果集的第一行/最后一行
Prior/Next	当前位置的上一行/当前位置的下一行
Absoute n	从游标的第一行开始数，第n行
Relative n	从当前位置数，第n行
Into @variable_name[,…]	将提取到的数据存放到变量variable_name中

fetch状态

0：Fetch语句成功。
-1：Fetch语句失败或行不在结果集中。
-2：提取的行不存在。

这个状态值可以帮你判断提取数据的成功与否。

提取数据

--提取数据
fetch first from orderNum_02_cursor
fetch relative 3 from orderNum_02_cursor
fetch next from orderNum_02_cursor
fetch absolute 4 from orderNum_02_cursor
fetch next from orderNum_02_cursor
fetch last from orderNum_02_cursor 
fetch prior from orderNum_02_cursor
select * from bigorder where orderNum='ZEORD003402'

--提取数据赋值给变量
declare @OrderId int
fetch absolute 3 from orderNum_02_cursor into @OrderId
select @OrderId as id
select * from bigorder where orderNum='ZEORD003402'

2、示例1

-- 循环检索数据 
DECLARE @order_num INT;
DECLARE orders_cursor CURSOR
FOR SELECT order_num FROM orders ORDER BY order_num;
OPEN orders_cursor;
FETCH NEXT FROM orders_cursor INTO @order_num;
WHILE @@FETCH_STATUS=0
	BEGIN
		FETCH NEXT FROM orders_cursor INTO @order_num;
	END；
CLOSE orders_cursor
DEALLOCATE orders_cursor;

---------------------------------------------------
-- 进一步对数据实际处理
DECLARE @order_num INT;
DECLARE @order_total MONEY;  --每个订单合计
DECLARE @total MONEY;    --总合计
SET @total=0;
DECLARE orders_cursor CURSOR
FOR SELECT order_num FROM orders ORDER BY order_num;
OPEN orders_cursor;
-- fetch取每一个@order_num
FETCH NEXT FROM orders_cursor INTO @order_num;
WHILE @@FETCH_STATUS=0
BEGIN
	-- 执行存储过程计算每个订单合计存入@order_total
	EXEC ordertotal @order_num,1,@order_total OUTPUT 
	-- 每检索一个@order_total就set到@total上
	SET @total=@total+@order_total
	FETCH NEXT FROM orders_cursor INTO @order_num;
END
CLOSE orders_cursor
DEALLOCATE orders_cursor;
-- 返回结果
SELECT @total AS GrantTotal;

注：存储过程：例子1

3、示例2

--1.声明游标
declare orderNum_03_cursor cursor scroll
for (select OrderId ,userId from bigorder where orderNum='ZEORD003402')
--2.打开游标
open orderNum_03_cursor
--3.声明游标提取数据所要存放的变量
declare @OrderId int ,@userId varchar(15)
--4.定位游标到哪一行
fetch First from orderNum_03_cursor into @OrderId,@userId  --into的变量数量必须与游标查询结果集的列数相同
while @@fetch_status=0  --提取成功，进行下一条数据的提取操作 
 begin
   if @OrderId=122182
     begin
     Update bigorder Set UserId='123' Where Current of  orderNum_03_cursor  --修改当前行
     end
   if @OrderId=154074
      begin
      --删除当前行
      Delete bigorder Where Current of  orderNum_03_cursor  
      end
   --移动游标
   fetch next from orderNum_03_cursor into @OrderId ,@userId 
 end
--关闭游标
close orderNum_03_cursor
--释放游标
deallocate orderNum_03_cursor