Sqlserver 游标

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 ] ] ]

 

2，声明一个动态游标

declare orderNum_02_cursor cursor scroll
for select OrderId from bigorder where orderNum='ZEORD003402'

3，打开游标

--打开游标语法
open [ Global ] cursor_name | cursor_variable_name

cursor_name:游标名，cursor_variable_name：游标变量名称，该变量引用了一个游标。

--打开游标
open orderNum_02_cursor

4，提取数据

--提取游标语法
Fetch
[ [Next|prior|Frist|Last|Absoute n|Relative n ]
from ]
[Global] cursor_name
[into @variable_name[,....]]

 

参数说明：

• Frist：结果集的第一行
• Prior：当前位置的上一行
• Next：当前位置的下一行
• Last：最后一行
• Absoute n：从游标的第一行开始数，第n行。
• Relative n：从当前位置数，第n行。
• Into @variable_name[,...] : 将提取到的数据存放到变量variable_name中。

例子：

--提取数据
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'

通过检测全局变量@@Fetch_Status的值，获得提取状态信息，该状态用于判断Fetch语句返回数据的有效性。当执行一条Fetch语句之后，@@Fetch_Status可能出现3种值：

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

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

declare @OrderId int
fetch absolute 3 from orderNum_02_cursor into @OrderId
while @@fetch_status=0  --提取成功，进行下一条数据的提取操作
 begin
   select @OrderId as id
   fetch  next from orderNum_02_cursor into @OrderId  --移动游标
 end

5.利用游标更新删除数据

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

游标更新删除当前数据

--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

6，关闭游标

　　游标打开后，服务器会专门为游标分配一定的内存空间存放游标操作的数据结果集，同时使用游标也会对某些数据进行封锁。所以游标一旦用过，应及时关闭，避免服务器资源浪费。

--关闭游标语法
close [ Global ] cursor_name | cursor_variable_name
--关闭游标
close orderNum_03_cursor

7，删除游标

　　删除游标，释放资源

--释放游标语法
deallocate  [ Global ] cursor_name | cursor_variable_name
--释放游标
deallocate orderNum_03_cursor

 

使用实例：

USE Test_DB;

DECLARE @jid CHAR(5)
DECLARE @pic NVARCHAR(64)
DECLARE My_Cursor CURSOR --定义游标
FOR (SELECT jid FROM journal WHERE isall in(1,2)) --查出需要的集合放到游标中
OPEN My_Cursor; --打开游标
FETCH NEXT FROM My_Cursor INTO @jid; --读取第一行数据
WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @pic=(SELECT TOP 1 smallpic FROM journalissue WHERE jid=@jid and (smallpic != '' and smallpic is not null) ORDER BY issueyear DESC,issueno DESC);
        PRINT (@jid +'    '+ @pic);
        IF(@jid != '' and @jid is not null and @pic != '' and @pic is not null)
        BEGIN
            UPDATE journal SET pic=@pic WHERE jid=@jid;
        END        
        FETCH NEXT FROM My_Cursor INTO @jid; --读取下一行数据
    END
CLOSE My_Cursor; --关闭游标
DEALLOCATE My_Cursor; --释放游标
GO

 

示例：

 1 USE [Community];
 2 
 3 DECLARE @UserInfoID bigint,@Name nvarchar(100),@UCAccountID bigint;
 4 
 5 
 6 DECLARE My_Cursor CURSOR --定义游标
 7 FOR (
 8     select distinct(UI.[UserInfoID]),UI.[Name],UIToUC.[UCAccountID] from [dbo].[CMAreaRelation] as AR 
 9         inner join [dbo].[UserInfo] as UI on UI.[UserInfoID]=AR.[Creator]
10         inner join [dbo].[UserInfoToUCAccount] as UIToUC on UIToUC.[UserInfoID]=UI.[UserInfoID]
11 ) --查出需要的集合放到游标中
12 OPEN My_Cursor; --打开游标
13 FETCH NEXT FROM My_Cursor INTO @UserInfoID,@Name,@UCAccountID; --读取第一行数据
14 WHILE @@FETCH_STATUS = 0
15     BEGIN
16         PRINT '@UserInfoID='+convert(varchar,isnull(@UserInfoID,0))+'， @Name='+isnull(@Name,'')+'， @UCAccountID='+convert(varchar,isnull(@UCAccountID,0)); --打印，方便查看（正式项目不需要该行）
17         --这里是根据每一行编写自定义的操作……
18 
19         FETCH NEXT FROM My_Cursor INTO @UserInfoID,@Name,@UCAccountID; --读取下一行数据
20     END
21 CLOSE My_Cursor; --关闭游标
22 DEALLOCATE My_Cursor; --释放游标
23 GO