【IT168 技术文档】 在数据库开发过程中，当你检索的数据只是一条记录时，你所编写的事务语句代码往往使用SELECT INSERT 语句。但是我们常常会遇到这样情况，即从某一结果集中逐一地读取一条记录。那么如何解决这种问题呢？ 游标 为我们提供了一种极为优秀的
【IT168 技术文档】 在数据库开发过程中，当你检索的数据只是一条记录时，你所编写的事务语句代码往往使用SELECT INSERT 语句。但是我们常常会遇到这样情况，即从某一结果集中逐一地读取一条记录。那么如何解决这种问题呢？ 游标 为我们提供了一种极为优秀的解决方案。
1. 游标 和 游标 的优点
在数据库中， 游标 是一个十分重要的概念。 游标 提供了一种对从表中检索出的数据进行操作的灵活手段，就本质而言， 游标 实际上是一种能从包括多条数据记录的结果集中每次提取一条记录的机制。 游标 总是与一条T_SQL 选择语句相关联因为 游标 由结果集（可以是零条、一条或由相关的选择语句检索出的多条记录）和结果集中指向特定记录的 游标 位置组成。当决定对结果集进行处理时，必须声明一个指向该结果集的 游标 。如果曾经用 C 语言写过对文件进行处理的程序，那么 游标 就像您打开文件所得到的文件句柄一样，只要文件打开成功，该文件句柄就可代表该文件。对于 游标 而言，其道理是相同的。可见 游标 能够实现按与传统程序读取平面文件类似的方式处理来自基础表的结果集，从而把表中数据以平面文件的形式呈现给程序。
我们知道关系数据库管理系统实质是面向集合的，在MS SQL SERVER 中并没有一种描述表中单一记录的表达形式，除非使用where 子句来限制只有一条记录被选中。因此我们必须借助于 游标 来进行面向单条记录的数据处理。
由此可见， 游标 允许应用程序对查询语句select 返回的行结果集中每一行进行相同或不同的操作，而不是一次对整个结果集进行同一种操作；它还提供对基于 游标 位置而对表中数据进行删除或更新的能力；而且，正是 游标 把作为面向集合的数据库管理系统和面向行的程序设计两者联系起来，使两个数据处理方式能够进行沟通。
2. 游标 种类
MS SQL SERVER 支持三种类型的 游标 ：Transact_SQL 游标 ，API 服务器 游标 和客户 游标 。
（1）Transact_SQL 游标
Transact_SQL 游标 是由DECLARE CURSOR 语法定义、主要用在Transact_SQL 脚本、 存储 过程和触发器中。Transact_SQL 游标 主要用在 服务器 上，由从客户端发送给服务器的Transact_SQL 语句或是批处理、存储过程、触发器中的Transact_SQL 进行管理。 Transact_SQL 游标 不支持提取数据块或多行数据。
（2）API 游标
API 游标 支持在OLE DB， ODBC 以及DB_library 中使用 游标 函数，主要用在服务器上。每一次客户端应用程序调用API 游标 函数，MS SQL SEVER 的OLE DB 提供者、ODBC 驱动 器或DB_library 的动态链接库（DLL） 都会将这些客户请求传送给服务器以对API 游标 进行处理。
（3）客户 游标
客户 游标 主要是当在客户机上缓存结果集时才使用。在客户 游标 中，有一个缺省的结果集被用来在客户机上缓存整个结果集。客户 游标 仅支持静态 游标 而非动态 游标 。由于服务器 游标 并不支持所有的Transact-SQL 语句或批处理，所以客户 游标 常常仅被用作服务器 游标 的辅助。因为在一般情况下，服务器 游标 能支持绝大多数的 游标 操作。
由于API 游标 和Transact-SQL 游标 使用在服务器端，所以被称为服务器 游标 ，也被称为后台 游标 ，而客户端 游标 被称为前台 游标 。在本章中我们主要讲述服务器（后台） 游标 。
select count (id) from info
select * from info
-- 清除所有记录
truncate table info
declare @i int
set @i = 1
while @i 1000000
begin
insert into info values ( ' Justin ' + str ( @i ), ' 深圳 ' + str ( @i ))
set @i = @i + 1
end
3. 游标 操作
使用 游标 有四种基本的步骤:声明 游标 、打开 游标 、提取数据、关闭 游标 。
声明 游标
象使用其它类型的变量一样,使用一个 游标 之前,首先应当声明它。 游标 的声明包括两个部分: 游标 的名称;这个 游标 所用到的SQL语句。如要声明一个叫作Cus-tomerCursor的 游标 用以查询地址在北京的客户的姓名、帐号及其余额,您可以编写如下代码:
DECLARE CustomerCursor CURSOR FOR
SELECT acct_no,name,balance
FROM customer
WHERE province = "北京";
在 游标 的声明中有一点值得注意的是,如同其它变量的声明一样,声明 游标 的这一段代码行是不执行的,您不能将debug时的断点设在这一代码行上,也不能用IF...END IF语句来声明两个同名的 游标 ,如下列的代码就是错误的。
IF Is_prov = "北京" THEN
DECLARE CustomerCursor CURSOR FOR
SELECT acct_no,name,balance
FROM customer
WHERE province = "北京";
ELSE
DECLARE CustomerCursor CURSOR FOR
SELECT acct_no,name,balance
FROM customer
WHERE province〈〉"北京";
END IF
打开 游标
声明了 游标 后在作其它操作之前,必须打开它。打开 游标 是执行与其相关的一段SQL语句,例如打开上例声明的一个 游标 ,我们只需键入:
OPEN CustomerCursor;
由于打开 游标 是对数据库进行一些SQL SELECT的操作,它将耗费一段时间,主要取决于您使用的系统性能和这条语句的复杂程度。如果执行的时间较长,可以考虑将 屏幕 上显示的 鼠标 改为hourglass。
提取数据
当用OPEN语句打开了 游标 并在数据库中执行了查询后,您不能立即利用在查询结果集中的数据。您必须用FETCH语句来取得数据。一条FETCH语句一次可以将一条记录放入程序员指定的变量中。事实上,FETCH语句是 游标 使用的核心。在DataWindow和DataStore中,执行了Retrieve()函数以后,查询的所有结果全部可以得到;而使用 游标 ,我们只能逐条记录地得到查询结果。
已经声明并打开一个 游标 后,我们就可以将数据放入任意的变量中。在FETCH语句中您可以指定 游标 的名称和目标变量的名称。如下例:
FETCH CustmerCur - sor
INTO :ls_acct_no,
:ls_name,
:ll_balance;
从语法上讲,上面所述的就是一条合法的取数据的语句,但是一般我们使用 游标 却还应当包括其它的部分。正如我们前面所谈到的, 游标 只能一次从后台数据库中取一条记录,而在多数情况下,我们所想要作的是在数据库中从第一条记录开始提取,一直到结束。所以我们一般要将 游标 提取数据的语句放在一个循环体内,直至将结果集中的全部数据提取后,跳出循环圈。通过检测SQLCA.SQL-CODE的值,可以得知最后一条FETCH语句是否成功。一般,当SQLCODE值为0时表明一切正常,100表示已经取到了结果集的末尾,而其它值均表明操作出了问题,这样我们可以编写以下的代码:
lb_continue = True
ll_total = 0
DO WHILE lb_continue
FETCH CustomerCur - sor
INTO :ls_acct_no,
:ls_name,
:ll_balance;
If sqlca.sqlcode = 0 Then
ll_total += ll_balance
Else
lb_continue = False
End If
LOOP
循环体的结构有多种,这里提到的是最常见的一种。也有的程序员喜爱将一条FETCH语句放在循环体的前面,循环体内再放置另外一条FETCH语句,并检测SQLCA.SQLCODE是否为100。但是这样做,维护时需同时修改两条FETCH语句,稍麻烦了些。
关闭 游标
在 游标 操作的最后请不要忘记关闭 游标 ,这是一个好的编程习惯,以使系统释放 游标 占用的资源。关闭 游标 的语句很简单:
CLOSE CustomerCursor;
使用Where子句子
我们可以动态地定义 游标 中的Where子句的参数,例如在本例中我们是直接定义了查询省份是北京的记录,但也许在应用中我们要使用一个下拉式列表框,由用户来选择要查询的省份,我们该怎样做呢?
我们在前面曾经提到过,DECLARE语句的作用只是定义一个 游标 ,在OPEN语句中这个 游标 才会真正地被执行。了解了这些,我们就可以很方便地实现这样的功能,在DECLARE的Where子句中加入变量作参数,如下所示:
DECLARE CustomerCursor CURSOR FOR
SELCECT acct_no,name,balance
FROM customer
WHERE province = :ls_province;
∥定义ls_province的值
OPEN CustomerCursor;
游标 的类型
同其它变量一样,我们也可以定义 游标 的访问类型:全局、共享、实例或局部, 游标 变量的命名规范建议也同其它变量一样。
-- 声明 游标
declare my_cursor cursor keyset for select * from info
-- 删除 游标 资源
deallocate my_cursor
-- 打开 游标 ,在 游标 关闭或删除前都有效
open my_cursor
-- 关闭 游标
close my_cursor
-- 声明局部变量
declare @id int , @name varchar ( 20 ), @address varchar ( 20 )
-- 定位到指定位置的记录
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
实例：
use database1
declare my_cursor cursor scroll dynamic
/**//* scroll表示可随意移动 游标 指 针（否则只能向前），dynamic表示可以读写 游标 （否则 游标 只读） */
for
select productname from product
open my_cursor
declare @pname sysname
fetch next from my_cursor into @pname
while ( @@fetch_status = 0 )
begin
print ' Product Name: ' + @pname
fetch next from my_cursor into @pname
end
fetch first from my_cursor into @pname
print @pname
/**//* update product set productname='zzg' where current of my_cursor */
/**//* delete from product where current of my_cursor */
close my_cursor
deallocate my_cursor
4. 游标 的高级技巧
尽管目前基于SQL语句的后台数据库所支持的语言都大致相当,但对 游标 的支持却有着一些差异,例如对滚动 游标 支持。所谓滚动 游标 ,就是程序员可以指定 游标 向前后任意一个方向滚动。如在Informix中,您甚至还可以将 游标 滚向结果集开头或末尾,使用的语句分别是FETCH FIRST,FETCH LAST、FETCH PRIOR和FETCH NEXT。当程序员用FETCH语句,其缺省是指FETCH NEXT。由于滚动是在数据库后台实现的,所以滚动 游标 为用户编程提供了极大的方便。
对 游标 支持的另一个不同是可修改 游标 。上述 游标 的使用都是指只读 游标 ,而象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 "
另外一个内容是动态 游标 ,也就是说您可以运行过程中动态地形成 游标 的SELECT语句。这同在PowerBuilder中动态地使用嵌入式SQL一样,需要用到DynamicStagin-gArea等数据类型,这已超出了本节的范围。