|
?
声明游标
在这一步中，需要指定游标的属性和根据要求产生的结果集。有两种方法可以指定一个游标。
形式1 （ANSI 92）
DECLARE cursor_name [INSENSITIVE] [SCROLL] CURSOR
FOR select_statement
[FOR ｛READ ONLY | UPDATE ][OF column_list]｝]
形式2
DECLARE cursor_name CURSOR
[LOCAL | GLOBAL]
[FORWARD_ONLY | SCROLL]
[STATIC | KEYSET | DYNAMIC]
[READ_ONLY | SCROLL_LOCKS | OPTIMISTIC]
FOR select_statement
[FOR ｛READ ONLY | UPDATE ][OF column_list]｝]
INSENSITIVE关键字指明要为检索到的结果集建立一个临时拷贝，以后的数据从这个临时拷贝中获取。如果在后来游标处理的过程中，原有基表中数据发生了改变，那么它们对于该游标而言是不可见的。这种不敏感的游标不允许数据更改。
SCROLL关键字指明游标可以在任意方向上滚动。所有的fetch选项（first、last、next、relative、absolute）都可以在游标中使用。如果忽略该选项，则游标只能向前滚动（next）。
Select_statement指明SQL语句建立的结果集。Transact SQL语句COMPUTE、COMPUTE BY、FOR BROWSE和INTO在游标声明的选择语句中不允许使用。
READ ONLY指明在游标结果集中不允许进行数据修改。
UPDATE关键字指明游标的结果集可以修改。
OF column_list指明结果集中可以进行修改的列。缺省情况下（使用UPDATE关键字），所有的列都可进行修改。
LOCAL关键字指明游标是局部的，它只能在它所声明的过程中使用。
GLOBAL关键字使得游标对于整个连接全局可见。全局的游标在连接激活的任何时候都是可用的。只有当连接结束时，游标才不再可用。
FORWARD_ONLY指明游标只能向前滚动。
STATIC的游标与INSENSITIVE的游标是相同的。
KEYSET指明选取的行的顺序。SQL Server将从结果集中创建一个临时关键字集。如果对数据库的非关键字列进行了修改，则它们对游标是可见的。因为是固定的关键字集合，所以对关键字列进行修改或新插入列是不可见的。
DYNAMIC指明游标将反映所有对结果集的修改。
SCROLL_LOCK是为了保证游标操作的成功，而对修改或删除加锁。
OPTIMISTIC指明哪些通过游标进行的修改或者删除将不会成功。
注意：
· 如果在SELECT语句中使用了DISTINCT、UNION、GROUP BY语句，且在选择中包含了聚合表达式，则游标自动为INSENSITIVE的游标。
· 如果基表没有唯一的索引，则游标创建成INSENSITIVE的游标。
· 如果SELECT语句包含了ORDER BY，而被ORDER BY的列并非唯一的行标识，则DYNAMIC游标将转换成KEYSET游标。如果KEYSET游标不能打开，则将转换成INSENSITIVE游标。使用SQL ANSI-92语法定义的游标同样如此，只是没有INSENSITIVE关键字而已。
ii. 打开游标
打开游标就是创建结果集。游标通过DECLARE语句定义，但其实际的执行是通过OPEN语句。语法如下：
OPEN ｛ ｛ [GLOBAL] cursor_name ｝ | cursor_variable_name｝
GLOBAL指明一个全局游标。
Cursor_name是被打开的游标的名称。
Cursor_variable_name是所引用游标的变量名。该变量应该为游标类型。
在游标被打开之后，系统变量@@cursor_rows可以用来检测结果集的行数。@@cursor_rows为负数时，表示游标正在被异步迁移，其绝对值（如果@@cursor_rows为－５，则绝对值为５）为当前结果集的行数。异步游标使用户在游标被完全迁移时仍然能够访问游标的结果。
iii. 从游标中取值
在从游标中取值的过程中，可以在结果集中的每一行上来回移动和处理。如果游标定义成了可滚动的（在声明时使用SCROLL关键字），则任何时候都可取出结果集中的任意行。对于非滚动的游标，只能对当前行的下一行实施取操作。结果集可以取到局部变量中。Fetch命令的语法如下：
FETCH [NEXT | PRIOR| FIRST | LAST | ABSOLUTE ｛n | @nvar｝ | RELATIVE ｛n | @nvar｝]
FROM [GLOBAL] cursor_name｝ | cursor_variable_name｝
[INTO @variable_name ][,……n]]
NEXT指明从当前行的下一行取值。
PRIOR指明从当前行的前一行取值。
FIRST是结果集的第一行。
LAST是结果集的最后一行。
ABSOLUTE n表示结果集中的第n行，该行数同样可以通过一个局部变量传播。行号从0开始，所以n为0时不能得到任何行。
RELATIVE n表示要取出的行在当前行的前n行或后n行的位置上。如果该值为正数，则要取出的行在当前行前n行的位置上，如果该值为负数，则返回当前行的后n行。
INTO @cursor_variable_name表示游标列值存储的地方的变量列表。该列表中的变量数应该与DECLARE语句中选择语句所使用的变量数相同。变量的数据类型也应该与被选择列的数据类型相同。直到下一次使用FETCH语句之前，变量中的值都会一直保持。
每一次FETCH的执行都存储在系统变量@@fetch_status中。如果FETCH成功，则@@fetch_status被设置成0。@@fetch_status为-1表示已经到达了结果集的一部分（例如，在游标被打开之后，基表中的行被删除）。@@fetch_status可以用来构造游标处理的循环。
例如：
DECLARE @iname char(20), @fname char(20)
OPEN author_cur
FETCH FIRST FROM author_cur INTO @iname, @fname
WHILE @@fetch_status = 0
BEGIN
IF @fname = ‘Albert’
PRINT “Found Albert Ringer”
ELSE
Print “Other Ringer”
FETCH NEXT FROM author_cur INTO @iname, @fname
END
iv. 关闭游标
CLOSE语句用来关闭游标并释放结果集。游标关闭之后，不能再执行FETCH操作。如果还需要使用FETCH语句，则要重新打开游标。语法如下：
CLOSE [GLOBAL] cursor_name | cursor_variable_name
v. 释放游标
游标使用不再需要之后，要释放游标。DEALLOCATE语句释放数据结构和游标所加的锁。语法如下：
DEALLOCATE [GLOBAL] cursor_name | cursor_variable_name
下面给出游标的一个完整的例子：
USE master
GO
CREATE PROCEDURE sp_BuildIndexes
AS
DECLARE @TableName sysname, @msg varchar(100), @cmd varchar(100)
DECLARE table_cur CURSOR FOR
SELECT name FROM sysobjects WHERE type=’u’
OPEN table_cur
FETCH NEXT FROM table_cur INTO @TableName
WHILE @@fetch_status = 0
BEGIN
IF @@fetch_status = -2
CONTINUE
SELECT @msg = “Building indexes for table”+@TableName+”…”
PRINT @msg
SELECT @cmd = “DBCC DBREINDEX (‘”+@TableName+”")”
EXEC (@cmd)
PRINT “ “
FETCH NEXT FROM table_cur INTO @TableName
END
DEALLOCATE table_cur
GO
下面的脚本将为PUBS数据库执行sp_BuildIndexes
USE pubs
GO
EXEC ap_BuildIndexes
注意：上面也是创建用户定义的系统存储过程的示例。
使用临时表
临时表是在TempDB中创建的表。临时表的名称都以“#”开头。临时表的范围为创建临时表的连接。因为，临时表不能在两个连接之间共享，一旦连接关闭，临时表就会被丢弃。如果临时表被创建于存储过程之中，则临时表的范围在存储过程之中，或者被该存储过程调用的任何存储过程之中。如果需要在连接之间共享临时表，则需要使用全局的临时表。全局的临时表以“##”符号开头，它将一直存在于数据库中，直到SQL Server重新启动。一旦这类临时表创建之后，所有的用户都可以访问到。在临时表上不能明确地指明权限。 临时表提供了存储中间结果的能力。有时候，临时表还能通过将一个复杂的查询分解成两个查询而获得性能的改善。这可以通过首先将第一个查询的结果存在临时表中，然后在第二个查询中使用临时表来实现。当一个大表中的某个子集在一个在座过程中使用多次时，建议使用临时表。在这种情况下，在临时表中保持数据的子集，以在随后的连接中使用，这样能大大改善性能。还可以在临时表中创建索引。
?
?
?
在数据库开发过程中，当你检索的数据只是一条记录时，你所编写的事务语句代码往往使用SELECT INSERT 语句。但是我们常常会遇到这样情况，即从某一结果集中逐一地读取一条记录。那么如何解决这种问题呢？游标为我们提供了一种极为优秀的解决方案。
1.1 游标和游标的优点
??? 在数据库中，游标是一个十分重要的概念。游标提供了一种对从表中检索出的数据进行操作的灵活手段，就本质而言，游标实际上是一种能从包括多条数据记录的结果集中每次提取一条记录的机制。游标总是与一条T_SQL 选择语句相关联因为游标由结果集（可以是零条、一条或由相关的选择语句检索出的多条记录）和结果集中指向特定记录的游标位置组成。当决定对结果集进行处理时，必须声明一个指向该结果集的游标。如果曾经用 C 语言写过对文件进行处理的程序，那么游标就像您打开文件所得到的文件句柄一样，只要文件打开成功，该文件句柄就可代表该文件。对于游标而言，其道理是相同的。可见游标能够实现按与传统程序读取平面文件类似的方式处理来自基础表的结果集，从而把表中数据以平面文件的形式呈现给程序。
??? 我们知道关系数据库管理系统实质是面向集合的，在MS SQL SERVER 中并没有一种描述表中单一记录的表达形式，除非使用where 子句来限制只有一条记录被选中。因此我们必须借助于游标来进行面向单条记录的数据处理。
??? 由此可见，游标允许应用程序对查询语句select 返回的行结果集中每一行进行相同或不同的操作，而不是一次对整个结果集进行同一种操作；它还提供对基于游标位置而对表中数据进行删除或更新的能力；而且，正是游标把作为面向集合的数据库管理系统和面向行的程序设计两者联系起来，使两个数据处理方式能够进行沟通。
1.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
1.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,balance
FROM customer
WHERE province="北京";
ELSE
DECLARE CustomerCursor CURSOR FOR
SELECT acct_no,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,
: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,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,@address as address
--定位到当前记录前一条
fetch prior from my_cursor into @id,@address as address
--定位到当前记录后一条
fetch next from my_cursor into @id,@address as address
--定位到首记录
fetch first from my_cursor into @id,@address as address
--定位到尾记录
fetch last from my_cursor into @id,@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
1.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等数据类型,这已超出了本节的范围。
?
????
?
每一个游标必须有四个组成部分这四个关键部分必须符合下面的顺序；
1.DECLARE 游标
2.OPEN 游标
3.从一个游标中FETCH 信息
4.CLOSE 或DEALLOCATE 游标
通常我们使用DECLARE 来声明一个游标声明一个游标主要包括以下主要内容：
游标名字
数据来源（表和列）
选取条件
属性（仅读或可修改）
其语法格式如下：
DECLARE cursor_name [INSENSITIVE] [SCROLL] CURSOR
FOR select_statement
[FOR {READ ONLY | UPDATE [OF column_name [,...n]]}]
其中：
cursor_name
指游标的名字。
INSENSITIVE
表明MS SQL SERVER 会将游标定义所选取出来的数据记录存放在一临时表内（建立在tempdb 数据库下）。对该游标的读取操作皆由临时表来应答。因此，对基本表的修改并不影响游标提取的数据，即游标不会随着基本表内容的改变而改变，同时也无法通过
游标来更新基本表。如果不使用该保留字，那么对基本表的更新、删除都会反映到游标中。
另外应该指出，当遇到以下情况发生时，游标将自动设定INSENSITIVE 选项。
在SELECT 语句中使用DISTINCT、 GROUP BY、 HAVING UNION 语句；
使用OUTER JOIN；
所选取的任意表没有索引；
将实数值当作选取的列。
SCROLL
表明所有的提取操作（如FIRST、 LAST、 PRIOR、 NEXT、 RELATIVE、 ABSOLUTE）都可用。如果不使用该保留字，那么只能进行NEXT 提取操作。由此可见，SCROLL 极大地增加了提取数据的灵活性，可以随意读取结果集中的任一行数据记录，而不必关闭再
重开游标。
select_statement
是定义结果集的SELECT 语句。应该注意的是，在游标中不能使用COMPUTE、COMPU- TE BY、 FOR BROWSE、 INTO 语句。
READ ONLY
表明不允许游标内的数据被更新尽管在缺省状态下游标是允许更新的。而且在UPDATE或DELETE 语句的WHERE CURRENT OF 子句中，不允许对该游标进行引用。
UPDATE [OF column_name[,…n]]
定义在游标中可被修改的列，如果不指出要更新的列，那么所有的列都将被更新。当游标被成功创建后，游标名成为该游标的惟一标识，如果在以后的存储过程、触发器或Transact_SQL 脚本中使用游标，必须指定该游标的名字。
上面介绍的是SQL_92 的游标语法规则。下面介绍MS SQL SERVER 提供的扩展了的游标声明语法，通过增加另外的保留字，使游标的功能进一步得到了增强其语法规则为；
?
LOCAL
定义游标的作用域仅限在其所在的存储过程、触发器或批处理中。当建立游标的存储过程执行结束后，游标会被自动释放。因此，我们常在存储过程中使用OUTPUT 保留字，将游标传递给该存储过程的调用者，这样在存储过程执行结束后，可以引用该游标变量，在该种情况下，直到引用该游标的最后一个就是被释放时，游标才会自动释放。
GLOBAL
定义游标的作用域是整个会话层会话层指用户的连接时间它包括从用户登录到SQLSERVER 到脱离数据库的整段时间。选择GLOBAL 表明在整个会话层的任何存储过程、触发器或批处理中都可以使用该游标，只有当用户脱离数据库、时该游标才会被自动释放。
注意：如果既未使用GLOBAL也未使用LOCAL，那么SQL SERVER将使用default local cursor数据库选项，为了与以彰的版本歉容，该选项常设置为FALSE。
FORWARD_ONLY
选项指明在从游标中提取数据记录时，只能按照从第一行到最后一行的顺序，此时只能选用FETCH NEXT 操作。除非使用STATIC， KEYSET 和DYNAMIC 关键字，否则如果未指明是使用FORWARD_ONLY 还是使用SCROLL， 那么FORWARD_ONLY 将成为缺省选项，因为若使用STATIC KEYSET 和DYNAMIC 关键字，则变成了SCROLL 游标。另外如果使用了FORWARD_ONLY， 便不能使用FAST_FORWARD。
STATIC
选项的含义与INSENSITIVE 选项一样，MS SQL SERVER 会将游标定义所选取出来的数据记录存放在一临时表内（建立在tempdb 数据库下）。对该游标的读取操作皆由临时表来应答。因此对基本表的修改并不影响游标中的数据，即游标不会随着基本表内容的
改变而改变，同时也无法通过游标来更新基本表。
KEYSET
指出当游标被打开时，游标中列的顺序是固定的，并且MS SQL SERVER 会在tempdb内建立一个表，该表即为KEYSET KEYSET 的键值可惟一识别游标中的某行数据。当游标拥有者或其它用户对基本表中的非键值数据进行修改时，这种变化能够反映到游标中，所以游标用户或所有者可以通过滚动游标提限这些数据。
??? 当其它用户增加一条新的符合所定义的游标范围的数据时，无法由此游标读到该数据。因为Transact-SQL 服务器游标不支持INSERT 语句。
??? 如果在游标中的某一行被删除掉，那么当通过游标来提取该删除行时，@@FETCH_STATUS 的返回值为-2。 @@FETCH_STATUS 是用来判断读取游标是否成功的系统全局变量。
??? 由于更新操作包括两部分：删除原数据插入新数据，所以如果读取原数据，@@FETCH_STATUS 的返回值为-2； 而且无法通过游标来读取新插入的数据。但是如果使用了WHERE CURRENT OF 子句时，该新插入行数据便是可见的。
注意：如果基础表未包含惟一的索引或主键，则一个KEYSET游标将回复成STATIC游标。
DYNAMIC
指明基础表的变化将反映到游标中，使用这个选项会最大程度上保证数据的一致性。然而，与KEYSET 和STATIC 类型游标相比较，此类型游标需要大量的游标资源。
FAST_FORWARD
指明一个FORWARD_ONLY,READ_ONLY 型游标。此选项已为执行进行了优化。如果SCROLL 或FOR_UPDATE 选项被定义，则FAST_FORWARD 选项不能被定义。
SCROLL_LOCKS
指明锁被放置在游标结果集所使用的数据上当。数据被读入游标中时，就会出现锁。这个选项确保对一个游标进行的更新和删除操作总能被成功执行。如果FAST_FORWARD选项被定义，则不能选择该选项。另外，由于数据被游标锁定，所以当考虑到数据并发处理时，应避免使用该选项。
OPTIMISTIC
指明在数据被读入游标后，如果游标中某行数据已发生变化，那么对游标数据进行更新或删除可能会导致失败。如果使用了FAST_FORWARD 选项，则不能使用该选项。
TYPE_WARNING
指明若游标类型被修改成与用户定义的类型不同时，将发送一个警告信息给客户端。
注意：不可以将SQL_92的游标语法规则与MS SQL SERVER的游标扩展用法混合在一起使用。
??? 下面我们将总结一下声明游标时应注意的一些问题。
??? 如果在CURSOR 前使用了SCROLL 或INSENSITIVE 保留字，则不能在CURSOR 和FOR select_statement 之间使用任何的保留字。反之同理。
??? 如果用DECLARE CURSOR 声明游标时，没有选择READ_ONLY、 OPTIMISTIC 或SCROLL_LOCKS 选项时，游标的缺省情况为：
??? 如果SELECT 语句不支持更新，则游标为READ_ONLY；
??? STATIC 和FAST_FORWARD 类型的游标缺省为READ_ONLY；??
??? DYNAMIC 和KEYSET 游标缺省为OPTIMISTIC。
??? 我们仅能在Transact-SQL 语句中引用游标，而不能在数据库API 函数中引用。
??? 游标被声明以后，可以通过系统过程对其特性进行设置。
??? 对那些有权限对视图、表或某些列执行SELECT 语句的用户而言，它也具有使用游标的缺
?
?
游标使用方法：
use db1
declare my_cursor cursor scroll dynamic /*scroll表示可随意移动游标指针（否则只能向前），dynamic表示可以读写游标（否则游标只读）*/
for
select 姓名 from 个人资料
open my_cursor
declare @name sysname
fetch next from my_cursor into @name
while(@@fetch_status=0)
begin
print ’姓名: ’ + @name
fetch next from my_cursor into @name
end
fetch first from my_cursor into @name
print @name
/* update 个人资料 set 姓名=’zzg’ where current of my_cursor */
/* delete from 个人资料 where current of my_cursor */
close my_cursor
deallocate my_cursor
正文
?
?
SQL?? Server?? 2000联机帮助上现成的例子：??
???
? A.?? 使用简单游标和语法??
? 打开该游标时所生成的结果集包括?? pubs?? 数据库的?? authors?? 表中的所有行和列。可以更新该游标，对该游标所做的所有更新和删除均在提取中表现出来。因为没指定?? SCROLL?? 选项，FETCH?? NEXT?? 是唯一可用的提取选项。??
???
? DECLARE?? authors_cursor?? CURSOR??
??????? FOR?? SELECT?? *?? FROM?? authors??
? OPEN?? authors_cursor??
? FETCH?? NEXT?? FROM?? authors_cursor??
???
? B.?? 使用嵌套游标生成报表输出??
? 下例显示如何嵌套游标以生成复杂的报表。为每个作者声明内部游标。??
???
? SET?? NOCOUNT?? ON??
???
? DECLARE?? @au_id?? varchar(11),?? @au_fname?? varchar(20),?? @au_lname?? varchar(40),??
??????? @message?? varchar(80),?? @title?? varchar(80)??
???
? PRINT?? "--------?? Utah?? Authors?? report?? --------"??
???
? DECLARE?? authors_cursor?? CURSOR?? FOR????
? SELECT?? au_id,?? au_fname,?? au_lname??
? FROM?? authors??
? WHERE?? state?? =?? "UT"??
? ORDER?? BY?? au_id??
???
? OPEN?? authors_cursor??
???
? FETCH?? NEXT?? FROM?? authors_cursor????
? INTO?? @au_id,?? @au_fname,?? @au_lname??
???
? WHILE?? @@FETCH_STATUS?? =?? 0??
? BEGIN??
??????? PRINT?? "?? "??
??????? SELECT?? @message?? =?? "-----?? Books?? by?? Author:?? "?? +????
????????????? @au_fname?? +?? "?? "?? +?? @au_lname??
???
??????? PRINT?? @message??
???
??????? --?? Declare?? an?? inner?? cursor?? based????????
??????? --?? on?? au_id?? from?? the?? outer?? cursor.??
???
??????? DECLARE?? titles_cursor?? CURSOR?? FOR????
??????? SELECT?? t.title??
??????? FROM?? titleauthor?? ta,?? titles?? t??
??????? WHERE?? ta.title_id?? =?? t.title_id?? AND??
??????? ta.au_id?? =?? @au_id?????? --?? Variable?? value?? from?? the?? outer?? cursor??
???
??????? OPEN?? titles_cursor??
??????? FETCH?? NEXT?? FROM?? titles_cursor?? INTO?? @title??
???
??????? IF?? @@FETCH_STATUS?? <>?? 0????
????????????? PRINT?? "?????????????????? <<No?? Books>>"????????????
???
??????? WHILE?? @@FETCH_STATUS?? =?? 0??
??????? BEGIN??
???????????????
????????????? SELECT?? @message?? =?? "?????????????????? "?? +?? @title??
????????????? PRINT?? @message??
????????????? FETCH?? NEXT?? FROM?? titles_cursor?? INTO?? @title??
?????????
??????? END??
???
??????? CLOSE?? titles_cursor??
??????? DEALLOCATE?? titles_cursor??
?????????
??????? --?? Get?? the?? next?? author.??
??????? FETCH?? NEXT?? FROM?? authors_cursor????
??????? INTO?? @au_id,?? @au_lname??
? END??
???
? CLOSE?? authors_cursor??
? DEALLOCATE?? authors_cursor??
? GO??
???
? --------?? Utah?? Authors?? report?? --------??
?????
? -----?? Books?? by?? Author:?? Anne?? Ringer??
??????????????????? The?? Gourmet?? Microwave??
??????????????????? Is?? Anger?? the?? Enemy???
?????
? -----?? Books?? by?? Author:?? Albert?? Ringer??
??????????????????? Is?? Anger?? the?? Enemy???
??????????????????? Life?? Without?? Fear
DECLARE?? @tablename?? nvarchar(256)??
? DECLARE?? tnames_cursor?? CURSOR??
? FOR?? SELECT?? name?? FROM?? sysobjects??
??? WHERE?? objectproperty(id,?? 'IsUserTable')=1??
? ORDER?? BY?? name??
? OPEN?? tnames_cursor??
? FETCH?? NEXT?? FROM?? tnames_cursor?? INTO?? @tablename??
? WHILE?? (@@FETCH_STATUS?? =?? 0)??
? BEGIN??
??????? EXEC?? sp_spaceused?? @tablename??
??????? FETCH?? NEXT?? FROM?? tnames_cursor?? INTO?? @tablename??
? END??
? CLOSE?? tnames_cursor??
? DEALLOCATE?? tnames_cursor??
? ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~`??
? @tablename??
???
? Categories????????????
? CustomerCustomerDemo??????????????
? CustomerDemographics??
? Customer??????????????
? dtproperties??
? Employees??
? ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~??
? 结果??
? name rows reserved data index_size unused??
? ---------------------------------------------------------------??
? Categories 8 184?? KB 112?? KB 40?? KB 32KB??
? (1?? row(s)?? affected)??
???
? name rows reserved data index_size unused??
? ---------------------------------------------------------------??
? CustomerCustomerDemo 0 32?? KB 8KB 24?? KB 0?? KB??
? (1?? row(s)?? affected)??
? ...??
???
? 隐式游标转换??
? 游标引用了某一个视图，并有top子句??
? 变成静态型??
? 包含?? distinct,?? group?? by等选项的查询??
? 变成静态型??
? 查询引用了一个有触发器的表??
? 变成静态型??
? 查询产生了内部的工作表(order?? by)??
? 变成keyset型??
? 查询引用了链接服务器上的远程表??
? 变成keyset型??
???
? 游标的选择??
???
? 游表类型的选择依赖于几个因素，包括:????
? 结果集的大小????
? 可能被用到的数据所占的百分比??
? 打开游标的性能????
? 需要对游标进行的操作,?? 例如滚动或定点更新????
? 其他用户进行的数据更新的可见度?
?
游标的使用????
? 提到游标这个词,人们想到的是在屏幕上一个闪动的方框,用以指示用户将要输入字符的位置。而在关系型数据库的SQL语言中,游标却有另外的含义,它是存放结果集的数据对象。????
? 为什么要用到游标????
? 在某些PowerBuilder应用程序的开发中,您可能根本用不到游标这样一个对象。因为在其它工具开发中很多需用游标实现的工作,在PowerBuilder中却已有DataWin-dow来代劳了。事实上,DataWindow不仅可以替代游标进行从后台数据库查询多条记录的复杂操作,而且还远不止这些。但是同DataWindow和DataStore相比,游标也有其自身的优点,比如系统资源占用少,操作灵活,可根据需要定义变量类型如全局、实例或局部类型和访问类型如私有或公共等。????
? 游标的操作????
? 使用游标有四种基本的步骤:声明游标、打开游标、提取数据、关闭游标。????
? 声明游标????
? 象使用其它类型的变量一样,您可以编写如下代码:????
? DECLARE?? CustomerCursor?? CURSOR?? FOR????
? SELECT?? acct_no,balance????
? FROM?? customer????
? WHERE?? province="北京";????
? 在游标的声明中有一点值得注意的是,也不能用IF...END?? IF语句来声明两个同名的游标,如下列的代码就是错误的。????
? IF?? Is_prov="北京"THEN????
? DECLARE?? CustomerCursor?? CURSOR?? FOR????
? SELECT?? acct_no,balance????
? FROM?? customer????
? WHERE?? province="北京";????
? ELSE????
? DECLARE?? CustomerCursor?? CURSOR?? FOR????
? SELECT?? acct_no,balance????
? FROM?? customer????
? WHERE?? province〈〉"北京";????
? END?? IF????
? 打开游标????
? 声明了游标后在作其它操作之前,我们只需键入:????
? OPEN?? CustomerCursor;????
? 由于打开游标是对数据库进行一些SQL?? SELECT的操作,可以考虑将屏幕上显示的鼠标改为hourglass。????
? 提取数据????
? 当用OPEN语句打开了游标并在数据库中执行了查询后,我们只能逐条记录地得到查询结果。????
? 已经声明并打开一个游标后,我们就可以将数据放入任意的变量中。在FETCH语句中您可以指定游标的名称和目标变量的名称。如下例:????
? FETCH?? CustmerCur-sor????
? INTO:ls_acct_no,????
? :ls_name,????
? :ll_balance;????
? 从语法上讲,这样我们可以编写以下的代码:????
? lb_continue=True????
? ll_total=0????
? DO?? WHILE?? lb_continue????
? FETCH?? CustomerCur-sor????
? INTO:ls_acct_no,????
? :ll_balance;????
? If?? sqlca.sqlcode=0?? Then????
? ll_total+=ll_balance????
? Else????
? lb_continue=False????
? End?? If????
? LOOP????
? 循环体的结构有多种,稍麻烦了些。????
? 关闭游标????
? 在游标操作的最后请不要忘记关闭游标,以使系统释放游标占用的资源。关闭游标的语句很简单:????
? CLOSE?? CustomerCursor;????
? 使用Where子句子????
? 我们可以动态地定义游标中的Where子句的参数,我们该怎样做呢?????
? 我们在前面曾经提到过,如下所示:????
? DECLARE?? CustomerCursor?? CURSOR?? FOR????
? SELCECT?? acct_no,balance????
? FROM?? customer????
? WHERE?? province=:ls_province;????
? ∥定义ls_province的值????
? OPEN?? CustomerCursor;????
? 游标的类型????
? 同其它变量一样,游标变量的命名规范建议也同其它变量一样。????
? 游标的高级技巧????
? 尽管目前基于SQL语句的后台数据库所支持的语言都大致相当,使用的语句分别是FETCH?? FIRST,FETCH?? LAST、FETCH?? PRIOR和FETCH?? NEXT。当程序员用FETCH语句,其缺省是指FETCH?? NEXT。由于滚动是在数据库后台实现的,所以滚动游标为用户编程提供了极大的方便。????
? 对游标支持的另一个不同是可修改游标。上述游标的使用都是指只读游标,我们可以如下操作:????
? UPDATE?? customer????
? SET?? balance=1000????
? WHERE?? CURRENT?? of?? customerCursor;????
? 删除当前行的操作如下:????
? DELETE?? FROM?? Customer????
? WHERE?? CURRENT?? OF?? CustomerCursor;????
? 但是如果您当前使用的数据库是Sybase,才能执行上述操作。这一赋值在连接数据库的前后进行均可。????
? SQLCA.DBParm="Cursor?? Update=1"????
? 另外一个内容是动态游标,这已超出了本节的范围。
?
游标、游标的优点和种类?? ??? ? 在数据库开发过程中，当你检索的数据只是一条记录时，你所编写的事务语句代码往往使用SELECT?? INSERT?? 语句。但是我们常常会遇到这样情况，即从某一结果集中逐一地读取一条记录。那么如何解决这种问题呢？游标为我们提供了一种极为优秀的解决方案。?? ??? ? 13.1.1?? 游标和游标的优点?? ????????? 在数据库中，游标是一个十分重要的概念。游标提供了一种对从表中检索出的数据进行操作的灵活手段，就本质而言，游标实际上是一种能从包括多条数据记录的结果集中每次提取一条记录的机制。游标总是与一条T_SQL?? 选择语句相关联因为游标由结果集（可以是零条、一条或由相关的选择语句检索出的多条记录）和结果集中指向特定记录的游标位置组成。当决定对结果集进行处理时，必须声明一个指向该结果集的游标。如果曾经用?? C?? 语言写过对文件进行处理的程序，那么游标就像您打开文件所得到的文件句柄一样，只要文件打开成功，该文件句柄就可代表该文件。对于游标而言，其道理是相同的。可见游标能够实现按与传统程序读取平面文件类似的方式处理来自基础表的结果集，从而把表中数据以平面文件的形式呈现给程序。?? ??? ????????? 我们知道关系数据库管理系统实质是面向集合的，在MS?? SQL?? SERVER?? 中并没有一种描述表中单一记录的表达形式，除非使用where?? 子句来限制只有一条记录被选中。因此我们必须借助于游标来进行面向单条记录的数据处理。?? ????????? 由此可见，游标允许应用程序对查询语句select?? 返回的行结果集中每一行进行相同或不同的操作，而不是一次对整个结果集进行同一种操作；它还提供对基于游标位置而对表中数据进行删除或更新的能力；而且，正是游标把作为面向集合的数据库管理系统和面向行的程序设计两者联系起来，使两个数据处理方式能够进行沟通。?? ??? ? 13.1.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?? 游标使用在服务器端，所以被称为服务器游标，也被称为后台游标，而客户端游标被称为前台游标。在本章中我们主要讲述服务器（后台）游标。
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
