SQL 游标

--1, 定义游标:
DECLARE Cursor_UserInfo CURSOR scroll dynamic -- scroll表示可随意移动游标指针（否则只能向前），dynamic表示可以读写游标（否则游标只读）
FOR
SELECT UserID--,UserName,UserAddress
FROM UserInfo
WHERE UserName='aaa';

--2, 打开游标:
open Cursor_UserInfo;

--3, 提取数据:
declare @userID int
FETCH next from Cursor_UserInfo INTO @userID
print @@fetch_status;
while(@@fetch_status=-1)
  begin
    print 'UserID ID: ' +convert(varchar(20),@userID)
    fetch next from Cursor_UserInfo into @userID
 print 'UserID ID: ' + convert(varchar(20),@userID)
  end
--fetch first from Cursor_UserInfo into @userID
print convert(varchar(20),@userID)

--update product set productname='zzg' where current of my_cursor
--delete from product where current of my_cursor

--4, 关闭游标:
CLOSE Cursor_UserInfo;

--5, 删除游标资源
deallocate Cursor_UserInfo

--定位到指定位置的记录
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

 

 

上述游标的使用都是指只读游标,而象Oracle、Sybase等数据库却另外支持可作修改的游标。使用这样的数据库,您可以修改或删除当前游标所在的行。例如修改当前游标所在行的用户的余额,我们可以如下操作:
UPDATE customer
SET balance=1000
WHERE CURRENT of customerCursor;
删除当前行的操作如下:
DELETE FROM Customer
WHERE CURRENT OF CustomerCursor;
但是如果您当前使用的数据库是Sybase,您需要修改数据库的参数,将游标可修改的值定为1,才能执行上述操作。这一赋值在连接数据库的前后进行均可。
SQLCA.DBParm="Cursor Update=1"

 

资料引用:http://www.knowsky.com/344071.html