TransactSQL 游标.docx

1、TransactSQL 游标一,游标的 作用: 当在存储过程,函数,批处理,触发器中 使用 select 返回多条记录时,游标提供了一个可以对记录进行逐条处理的 方法.二,游标的使用方法:1,使用Declare Cursor语句,根据Select语句创建游标. 有两种操作方法,效果是一样的，请注意他们的区别 : 第一种:声明变量，然后设置游标 Declare MyVariable Cursor -声明MyVariable 这个变量 Set MyVariable =Cursor For -根据select创建游标的固定用法 SELECT id,name FROM usertable 第二种:声明

2、变量，声明游标，设置变更为游标 DECLARE MyVariable CURSOR -声明MyVariable 这个变量 DECLARE MyCursor CURSOR FOR -注意这里的MyCursor前没有符号，声明了一个游标 SELECT id,name FROM usertable SET MyVariable = MyCursor -在这里才把游标赋值给变量MyVariable2，使用Open语句填充该游标 Open MyVariable -使用之前必须先打开它 -open语句会执行Declare Cursor语句中指定的Select语句,并填充游标3,使用Fetch语句更改游标所

3、指向的记录,并将值存储在局部变量中: Fetch Next From MyVariable Into intID,chvName 说明: 此语句的语法是 Fetch 操作符 From 游标变量名 Into 变量名 a)操作符可以是: Next(下一条),Prior(前一条),First(第一条),Last(最后一条), 也可以指定绝对位置和相对位置 b)游标变量名就是之前创建并填充的MyVariable c)变量名:需要在使用之前声明,本例 用到的intID,chvName 在Declare MyVariable Cursor之前声明Declare intID int,chvName varc

4、har(50) d)游标刚好打开时 Fetch Next语句读取第一条记录4,对检索出来的信息进行处理 While (FETCH_STATUS=0) Begin if chvName=不必完美 begin update usetable set name=管理员 where id=intID end Fetch Next From MyVariable -移动到下一条,你可以反复使用这个语句 Into intID,chvName End 说明:FETCH_STATUS是一个全局变量, 它返回在当前连接期间执行的最后一条Fetch语句的执行状态. 它经常循环中 用于退出循环的条件. FETCH_

5、STATUS的值和其含义: 0(数字零),提取完全成功; -1, 最后一条已经读完或读取失败 -2,记录丢失(例如,其他人删除了该记录) 还有一个可能会用到的全局变量cursor_rows 游标中的记录数5,使用Close语句关闭该游标,释放大部分的资源(释放记录集以及锁) Close MyVariable 说明:此语句执行对象必须是一个已经打开的游标,否则将报错.6,使用Deallocate语句清楚该游标. Deallocate MyVariable 说明:Close语句执行之后,游标结构仍然存在,你还可以再次打开它,如果彻底不用了.你应该用Dealocate语句删除该结构.完成后的实例为:

6、 Declare intID int,chvName varchar(50) Declare MyVariable Cursor -声明MyVariable 这个变量 Set MyVariable =Cursor For -根据select创建游标的固定用法 SELECT id,name FROM usertable Open MyVariable -使用之前必须先打开它 Fetch Next From MyVariable Into intID,chvName While (FETCH_STATUS=0) Begin if chvName=不必完美 begin update usetable

7、 set name=管理员 where id=intID end Fetch Next From MyVariable -移动到下一条,你可以反复使用这个语句 Into intID,chvName EndClose MyVariableDeallocate MyVariable SQL游标原理和使用方法 收藏SQL游标原理和使用方法 在数据库开发过程中，当你检索的数据只是一条记录时，你所编写的事务语句代码往往使用SELECT INSERT 语句。但是我们常常会遇到这样情况，即从某一结果集中逐一地读取一条记录。那么如何解决这种问题呢？游标为我们提供了一种极为优秀的解决方案。 1.1 游标和游标的

8、优点 在数据库中，游标是一个十分重要的概念。游标提供了一种对从表中检索出的数据进行操作的灵活手段，就本质而言，游标实际上是一种能从包括多条数据记录的结果集中每次提取一条记录的机制。游标总是与一条T_SQL 选择语句相关联因为游标由结果集（可以是零条、一条或由相关的选择语句检索出的多条记录）和结果集中指向特定记录的游标位置组成。当决定对结果集进行处理时，必须声明一个指向该结果集的游标。如果曾经用 C 语言写过对文件进行处理的程序，那么游标就像您打开文件所得到的文件句柄一样，只要文件打开成功，该文件句柄就可代表该文件。对于游标而言，其道理是相同的。可见游标能够实现按与传统程序读取平面文件类似的方式

9、处理来自基础表的结果集，从而把表中数据以平面文件的形式呈现给程序。 我们知道关系数据库管理系统实质是面向集合的，在MS SQL SERVER 中并没有一种描述表中单一记录的表达形式，除非使用where 子句来限制只有一条记录被选中。因此我们必须借助于游标来进行面向单条记录的数据处理。 由此可见，游标允许应用程序对查询语句select 返回的行结果集中每一行进行相同或不同的操作，而不是一次对整个结果集进行同一种操作；它还提供对基于游标位置而对表中数据进行删除或更新的能力；而且，正是游标把作为面向集合的数据库管理系统和面向行的程序设计两者联系起来，使两个数据处理方式能够进行沟通。 1.2 游标种类

10、 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_li

11、brary 中使用游标函数，主要用在服务器上。每一次客户端应用程序调用API 游标函数，MS SQL SEVER 的OLE DB 提供者、ODBC驱动器或DB_library 的动态链接库（DLL）都会将这些客户请求传送给服务器以对API游标进行处理。 （3） 客户游标 客户游标主要是当在客户机上缓存结果集时才使用。在客户游标中，有一个缺省的结果集被用来在客户机上缓存整个结果集。客户游标仅支持静态游标而非动态游标。由于服务器游标并不支持所有的Transact-SQL 语句或批处理，所以客户游标常常仅被用作服务器游标的辅助。因为在一般情况下，服务器游标能支持绝大多数的游标操作。 由于API 游标

12、和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 游标操作 使用游标有四种基本的步骤:声明游标、打开游标、提取数据、关闭游标

13、。 声明游标 象使用其它类型的变量一样,使用一个游标之前,首先应当声明它。游标的声明包括两个部分:游标的名称;这个游标所用到的SQL语句。如要声明一个叫作Cus-tomerCursor的游标用以查询地址在北京的客户的姓名、帐号及其余额,您可以编写如下代码: DECLARE CustomerCursor CURSOR FOR SELECT acct_no,name,balance FROM customer WHERE province=北京; 在游标的声明中有一点值得注意的是,如同其它变量的声明一样,声明游标的这一段代码行是不执行的,您不能将debug时的断点设在这一代码行上,也不能用IF.E

14、ND 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北京; 打开游标 声明了游标后在作其它操作之前,必须打开它。打开游标是执行与其相关的一段SQL语句,例如打开上例声明的一个

15、游标,我们只需键入: OPEN CustomerCursor; 由于打开游标是对数据库进行一些SQL SELECT的操作,它将耗费一段时间,主要取决于您使用的系统性能和这条语句的复杂程度。如果执行的时间较长,可以考虑将屏幕上显示的鼠标改为hourglass。 提取数据 当用OPEN语句打开了游标并在数据库中执行了查询后,您不能立即利用在查询结果集中的数据。您必须用FETCH语句来取得数据。一条FETCH语句一次可以将一条记录放入程序员指定的变量中。事实上,FETCH语句是游标使用的核心。在DataWindow和DataStore中,执行了 Retrieve()函数以后,查询的所有结果全部可以得

16、到;而使用游标,我们只能逐条记录地得到查询结果。 已经声明并打开一个游标后,我们就可以将数据放入任意的变量中。在FETCH语句中您可以指定游标的名称和目标变量的名称。如下例: FETCH CustmerCur-sor INTO:ls_acct_no, :ls_name, :ll_balance; 从语法上讲,上面所述的就是一条合法的取数据的语句,但是一般我们使用游标却还应当包括其它的部分。正如我们前面所谈到的,游标只能一次从后台数据库中取一条记录,而在多数情况下,我们所想要作的是在数据库中从第一条记录开始提取,一直到结束。所以我们一般要将游标提取数据的语句放在一个循环体内,直至将结果集中的全部

17、数据提取后,跳出循环圈。通过检测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

18、End If LOOP 循环体的结构有多种,这里提到的是最常见的一种。也有的程序员喜爱将一条FETCH语句放在循环体的前面,循环体内再放置另外一条FETCH语句,并检测SQLCA.SQLCODE是否为100。但是这样做,维护时需同时修改两条FETCH语句,稍麻烦了些。 关闭游标 在游标操作的最后请不要忘记关闭游标,这是一个好的编程习惯,以使系统释放游标占用的资源。关闭游标的语句很简单: CLOSE CustomerCursor; 使用Where子句子 我们可以动态地定义游标中的Where子句的参数,例如在本例中我们是直接定义了查询省份是北京的记录,但也许在应用中我们要使用一个下拉式列表框,由用

19、户来选择要查询的省份,我们该怎样做呢? 我们在前面曾经提到过,DECLARE语句的作用只是定义一个游标,在OPEN语句中这个游标才会真正地被执行。了解了这些,我们就可以很方便地实现这样的功能,在DECLARE的Where子句中加入变量作参数,如下所示: DECLARE CustomerCursor CURSOR FOR SELCECT acct_no,name,balance FROM customer WHERE province=:ls_province; 定义ls_province的值 OPEN CustomerCursor; 游标的类型 同其它变量一样,我们也可以定义游标的访问类型:全

20、局、共享、实例或局部,游标变量的命名规范建议也同其它变量一样。 -声明游标 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,a

21、ddress 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 n

22、ext 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 实例： u

23、se database1 declare my_cursor cursor scroll dynamic /*/*scroll表示可随意移动游标指针（否则只能向前），dynamic表示可以读写游标（否则游标只读）*/ for select productname fromproduct 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

24、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语句的后台数据库所支持的语言都大致相当,但对游标的支持却有着一些差异,例如对滚动游标支持。所谓滚动游标,就是

25、程序员可以指定游标向前后任意一个方向滚动。如在Informix中,您甚至还可以将游标滚向结果集开头或末尾,使用的语句分别是FETCH FIRST,FETCH LAST、FETCH PRIOR和FETCH NEXT。当程序员用FETCH语句,其缺省是指FETCH NEXT。由于滚动是在数据库后台实现的,所以滚动游标为用户编程提供了极大的方便。 对游标支持的另一个不同是可修改游标。上述游标的使用都是指只读游标,而象Oracle、Sybase等数据库却另外支持可作修改的游标。使用这样的数据库,您可以修改或删除当前游标所在的行。例如修改当前游标所在行的用户的余额,我们可以如下操作: UPDATE cu

26、stomer SET balance=1000 WHERE CURRENT of customerCursor; 删除当前行的操作如下: DELETE FROM Customer WHERE CURRENT OF CustomerCursor; 但是如果您当前使用的数据库是Sybase,您需要修改数据库的参数,将游标可修改的值定为1,才能执行上述操作。这一赋值在连接数据库的前后进行均可。 SQLCA.DBParm=Cursor Update=1 另外一个内容是动态游标,也就是说您可以运行过程中动态地形成游标的SELECT语句。这同在PowerBuilder中动态地使用嵌入式SQL一样,需要用到

27、DynamicStagin-gArea等数据类型,这已超出了本节的范围。 游标的使用提到游标这个词,人们想到的是在屏幕上一个闪动的方框,用以指示用户将要输入字符的位置。而在关系型数据库的SQL语言中,游标却有另外的含义,它是存放结果集的数据对象。 为什么要用到游标 在某些PowerBuilder应用程序的开发中,您可能根本用不到游标这样一个对象。因为在其它工具开发中很多需用游标实现的工作,在PowerBuilder中却已有DataWin-dow来代劳了。事实上,DataWindow不仅可以替代游标进行从后台数据库查询多条记录的复杂操作,而且还远不止这些。但是同DataWindow和DataSt

28、ore相比,游标也有其自身的优点,比如系统资源占用少,操作灵活,可根据需要定义变量类型如全局、实例或局部类型和访问类型如私有或公共等。 游标的操作 使用游标有四种基本的步骤:声明游标、打开游标、提取数据、关闭游标。 声明游标 象使用其它类型的变量一样,使用一个游标之前,首先应当声明它。游标的声明包括两个部分:游标的名称;这个游标所用到的SQL语句。如要声明一个叫作Cus-tomerCursor的游标用以查询地址在北京的客户的姓名、帐号及其余额,您可以编写如下代码: DECLARE CustomerCursor CURSOR FOR SELECT acct_no,name,balance FRO

29、M 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

30、_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; 从语法上讲,上面所述的就是一条合法的取数据的语句,但是