TransactSQL 游标.docx
《TransactSQL 游标.docx》由会员分享，可在线阅读，更多相关《TransactSQL 游标.docx（13页珍藏版）》请在冰豆网上搜索。
TransactSQL游标
一,游标的作用:
当在存储过程,函数,批处理,触发器中使用select返回多条记录时,游标提供了一个可以对记录进行逐条处理的方法.
二,游标的使用方法:
1,使用DeclareCursor语句,根据Select语句创建游标.
有两种操作方法,效果是一样的，请注意他们的区别:
第一种:
声明变量，然后设置游标
Declare@MyVariableCursor--声明@MyVariable这个变量
Set@MyVariable=CursorFor--根据select创建游标的固定用法
SELECTid,nameFROMusertable
第二种:
声明变量，声明游标，设置变更为游标
DECLARE@MyVariableCURSOR--声明@MyVariable这个变量
DECLAREMyCursorCURSORFOR--注意这里的MyCursor前没有@符号，声明了一个游标
SELECTid,nameFROMusertable
SET@MyVariable=MyCursor--在这里才把游标赋值给变量@MyVariable
2，使用Open语句填充该游标
Open@MyVariable--使用之前必须先打开它
--open语句会执行DeclareCursor语句中指定的Select语句,并填充游标
3,使用Fetch语句更改游标所指向的记录,并将值存储在局部变量中:
FetchNextFrom@MyVariable
Into@intID,@chvName
说明:
此语句的语法是Fetch操作符From游标变量名Into变量名
a）操作符可以是:
Next（下一条）,Prior（前一条）,First（第一条）,Last（最后一条）,
也可以指定绝对位置和相对位置
b）游标变量名就是之前创建并填充的@MyVariable
c）变量名:
需要在使用之前声明,本例用到的@intID,@chvName
在Declare@MyVariableCursor之前声明Declare@intIDint,@chvNamevarchar（50）
d）游标刚好打开时FetchNext语句读取第一条记录
4,对检索出来的信息进行处理
While（@@FETCH_STATUS=0）
Begin
if@chvName='不必完美'
begin
updateusetablesetname='管理员'whereid=@intID
end
FetchNextFrom@MyVariable--移动到下一条,你可以反复使用这个语句
Into@intID,@chvName
End
说明:
@@FETCH_STATUS是一个全局变量,
它返回在当前连接期间执行的最后一条Fetch语句的执行状态.
它经常循环中用于退出循环的条件.
@@FETCH_STATUS的值和其含义:
0（数字零）,提取完全成功;
-1,最后一条已经读完或读取失败
-2,记录丢失（例如,其他人删除了该记录）
还有一个可能会用到的全局变量@@cursor_rows游标中的记录数
5,使用Close语句关闭该游标,释放大部分的资源（释放记录集以及锁）
Close@MyVariable
说明:
此语句执行对象必须是一个已经打开的游标,否则将报错.
6,使用Deallocate语句清楚该游标.
Deallocate@MyVariable
说明:
Close语句执行之后,游标结构仍然存在,你还可以再次打开它,如果彻底不用了.你应该用Dealocate语句删除该结构.
完成后的实例为:
Declare@intIDint,@chvNamevarchar（50）
Declare@MyVariableCursor--声明@MyVariable这个变量
Set@MyVariable=CursorFor--根据select创建游标的固定用法
SELECTid,nameFROMusertable
Open@MyVariable--使用之前必须先打开它
FetchNextFrom@MyVariable
Into@intID,@chvName
While（@@FETCH_STATUS=0）
Begin
if@chvName='不必完美'
begin
updateusetablesetname='管理员'whereid=@intID
end
FetchNextFrom@MyVariable--移动到下一条,你可以反复使用这个语句
Into@intID,@chvName
End
Close@MyVariable
Deallocate@MyVariable
SQL游标原理和使用方法收藏
SQL游标原理和使用方法
在数据库开发过程中，当你检索的数据只是一条记录时，你所编写的事务语句代码往往使用SELECTINSERT语句。
但是我们常常会遇到这样情况，即从某一结果集中逐一地读取一条记录。
那么如何解决这种问题呢？
游标为我们提供了一种极为优秀的解决方案。
1.1游标和游标的优点
在数据库中，游标是一个十分重要的概念。
游标提供了一种对从表中检索出的数据进行操作的灵活手段，就本质而言，游标实际上是一种能从包括多条数据记录的结果集中每次提取一条记录的机制。
游标总是与一条T_SQL选择语句相关联因为游标由结果集（可以是零条、一条或由相关的选择语句检索出的多条记录）和结果集中指向特定记录的游标位置组成。
当决定对结果集进行处理时，必须声明一个指向该结果集的游标。
如果曾经用C语言写过对文件进行处理的程序，那么游标就像您打开文件所得到的文件句柄一样，只要文件打开成功，该文件句柄就可代表该文件。
对于游标而言，其道理是相同的。
可见游标能够实现按与传统程序读取平面文件类似的方式处理来自基础表的结果集，从而把表中数据以平面文件的形式呈现给程序。
我们知道关系数据库管理系统实质是面向集合的，在MSSQLSERVER中并没有一种描述表中单一记录的表达形式，除非使用where子句来限制只有一条记录被选中。
因此我们必须借助于游标来进行面向单条记录的数据处理。
由此可见，游标允许应用程序对查询语句select返回的行结果集中每一行进行相同或不同的操作，而不是一次对整个结果集进行同一种操作；它还提供对基于游标位置而对表中数据进行删除或更新的能力；而且，正是游标把作为面向集合的数据库管理系统和面向行的程序设计两者联系起来，使两个数据处理方式能够进行沟通。
1.2游标种类
MSSQLSERVER支持三种类型的游标：
Transact_SQL游标，API服务器游标和客户游标。
（1）Transact_SQL游标
Transact_SQL游标是由DECLARECURSOR语法定义、主要用在Transact_SQL脚本、存储过程和触发器中。
Transact_SQL游标主要用在服务器上，由从客户端发送给服务器的Transact_SQL语句或是批处理、存储过程、触发器中的Transact_SQL进行管理。
Transact_SQL游标不支持提取数据块或多行数据。
（2）API游标
API游标支持在OLEDB，ODBC以及DB_library中使用游标函数，主要用在服务器上。
每一次客户端应用程序调用API游标函数，MSSQLSEVER的OLEDB提供者、ODBC驱动器或DB_library的动态链接库（DLL）都会将这些客户请求传送给服务器以对API游标进行处理。
（3）客户游标
客户游标主要是当在客户机上缓存结果集时才使用。
在客户游标中，有一个缺省的结果集被用来在客户机上缓存整个结果集。
客户游标仅支持静态游标而非动态游标。
由于服务器游标并不支持所有的Transact-SQL语句或批处理，所以客户游标常常仅被用作服务器游标的辅助。
因为在一般情况下，服务器游标能支持绝大多数的游标操作。
由于API游标和Transact-SQL游标使用在服务器端，所以被称为服务器游标，也被称为后台游标，而客户端游标被称为前台游标。
在本章中我们主要讲述服务器（后台）游标。
selectcount（id）frominfo
select*frominfo
--清除所有记录
truncatetableinfo
declare@iint
set@i=1
while@i<1000000
begin
insertintoinfovalues（'Justin'+str（@i）,'深圳'+str（@i））
set@i=@i+1
end
1.3游标操作
使用游标有四种基本的步骤:
声明游标、打开游标、提取数据、关闭游标。
声明游标
象使用其它类型的变量一样,使用一个游标之前,首先应当声明它。
游标的声明包括两个部分:
游标的名称;这个游标所用到的SQL语句。
如要声明一个叫作Cus-tomerCursor的游标用以查询地址在北京的客户的姓名、帐号及其余额,您可以编写如下代码:
DECLARECustomerCursorCURSORFOR
SELECTacct_no,name,balance
FROMcustomer
WHEREprovince="北京";
在游标的声明中有一点值得注意的是,如同其它变量的声明一样,声明游标的这一段代码行是不执行的,您不能将debug时的断点设在这一代码行上,也不能用IF...ENDIF语句来声明两个同名的游标,如下列的代码就是错误的。
IFIs_prov="北京"THEN
DECLARECustomerCursorCURSORFOR
SELECTacct_no,name,balance
FROMcustomer
WHEREprovince="北京";
ELSE
DECLARECustomerCursorCURSORFOR
SELECTacct_no,name,balance
FROMcustomer
WHEREprovince〈〉"北京";
打开游标
声明了游标后在作其它操作之前,必须打开它。
打开游标是执行与其相关的一段SQL语句,例如打开上例声明的一个游标,我们只需键入:
OPENCustomerCursor;
由于打开游标是对数据库进行一些SQLSELECT的操作,它将耗费一段时间,主要取决于您使用的系统性能和这条语句的复杂程度。
如果执行的时间较长,可以考虑将屏幕上显示的鼠标改为hourglass。
提取数据
当用OPEN语句打开了游标并在数据库中执行了查询后,您不能立即利用在查询结果集中的数据。
您必须用FETCH语句来取得数据。
一条FETCH语句一次可以将一条记录放入程序员指定的变量中。
事实上,FETCH语句是游标使用的核心。
在DataWindow和DataStore中,执行了Retrieve（）函数以后,查询的所有结果全部可以得到;而使用游标,我们只能逐条记录地得到查询结果。
已经声明并打开一个游标后,我们就可以将数据放入任意的变量中。
在FETCH语句中您可以指定游标的名称和目标变量的名称。
如下例:
FETCHCustmerCur-sor
INTO:
ls_acct_no,
:
ls_name,
:
ll_balance;
从语法上讲,上面所述的就是一条合法的取数据的语句,但是一般我们使用游标却还应当包括其它的部分。
正如我们前面所谈到的,游标只能一次从后台数据库中取一条记录,而在多数情况下,我们所想要作的是在数据库中从第一条记录开始提取,一直到结束。
所以我们一般要将游标提取数据的语句放在一个循环体内,直至将结果集中的全部数据提取后,跳出循环圈。
通过检测SQLCA.SQL-CODE的值,可以得知最后一条FETCH语句是否成功。
一般,当SQLCODE值为0时表明一切正常,100表示已经取到了结果集的末尾,而其它值均表明操作出了问题,这样我们可以编写以下的代码:
lb_continue=True
ll_total=0
DOWHILElb_continue
FETCHCustomerCur-sor
INTO:
ls_acct_no,
:
ls_name,
:
ll_balance;
Ifsqlca.sqlcode=0Then
ll_total+=ll_balance
Else
lb_continue=False
EndIf
LOOP
循环体的结构有多种,这里提到的是最常见的一种。
也有的程序员喜爱将一条FETCH语句放在循环体的前面,循环体内再放置另外一条FETCH语句,并检测SQLCA.SQLCODE是否为100。
但是这样做,维护时需同时修改两条FETCH语句,稍麻烦了些。
关闭游标
在游标操作的最后请不要忘记关闭游标,这是一个好的编程习惯,以使系统释放游标占用的资源。
关闭游标的语句很简单:
CLOSECustomerCursor;
使用Where子句子
我们可以动态地定义游标中的Where子句的参数,例如在本例中我们是直接定义了查询省份是北京的记录,但也许在应用中我们要使用一个下拉式列表框,由用户来选择要查询的省份,我们该怎样做呢?
我们在前面曾经提到过,DECLARE语句的作用只是定义一个游标,在OPEN语句中这个游标才会真正地被执行。
了解了这些,我们就可以很方便地实现这样的功能,在DECLARE的Where子句中加入变量作参数,如下所示:
DECLARECustomerCursorCURSORFOR
SELCECTacct_no,name,balance
FROMcustomer
WHEREprovince=:
ls_province;
∥定义ls_province的值
OPENCustomerCursor;
游标的类型
同其它变量一样,我们也可以定义游标的访问类型:
全局、共享、实例或局部,游标变量的命名规范建议也同其它变量一样。
--声明游标
declaremy_cursorcursorkeysetforselect*frominfo
--删除游标资源
deallocatemy_cursor
--打开游标,在游标关闭或删除前都有效
openmy_cursor
--关闭游标
closemy_cursor
--声明局部变量
declare@idint,@namevarchar（20）,@addressvarchar（20）
--定位到指定位置的记录
fetchabsolute56488frommy_cursorinto@id,@name,@address
select@idasid,@nameasname,@addressasaddress
--定位到当前记录相对位置记录
fetchrelative-88frommy_cursorinto@id,@name,@address
select@idasid,@nameasname,@addressasaddress
--定位到当前记录前一条
fetchpriorfrommy_cursorinto@id,@name,@address
select@idasid,@nameasname,@addressasaddress
--定位到当前记录后一条
fetchnextfrommy_cursorinto@id,@name,@address
select@idasid,@nameasname,@addressasaddress
--定位到首记录
fetchfirstfrommy_cursorinto@id,@name,@address
select@idasid,@nameasname,@addressasaddress
--定位到尾记录
fetchlastfrommy_cursorinto@id,@name,@address
select@idasid,@nameasname,@addressasaddress
实例：
usedatabase1
declaremy_cursorcursorscrolldynamic
/**//*scroll表示可随意移动游标指 针（否则只能向前），dynamic表示可以读写游标（否则游标只读）*/
for
selectproductnamefrom product
openmy_cursor
declare@pnamesysname
fetchnextfrommy_cursorinto@pname
while（@@fetch_status=0）
begin
print'ProductName:
'+@pname
fetchnextfrommy_cursorinto@pname
end
fetchfirstfrommy_cursorinto@pname
print@pname
/**//*updateproductsetproductname='zzg'wherecurrentofmy_cursor*/
/**//*deletefromproductwherecurrentofmy_cursor*/
closemy_cursor
deallocatemy_cursor
1.4游标的高级技巧
尽管目前基于SQL语句的后台数据库所支持的语言都大致相当,但对游标的支持却有着一些差异,例如对滚动游标支持。
所谓滚动游标,就是程序员可以指定游标向前后任意一个方向滚动。
如在Informix中,您甚至还可以将游标滚向结果集开头或末尾,使用的语句分别是FETCHFIRST,FETCHLAST、FETCHPRIOR和FETCHNEXT。
当程序员用FETCH语句,其缺省是指FETCHNEXT。
由于滚动是在数据库后台实现的,所以滚动游标为用户编程提供了极大的方便。
对游标支持的另一个不同是可修改游标。
上述游标的使用都是指只读游标,而象Oracle、Sybase等数据库却另外支持可作修改的游标。
使用这样的数据库,您可以修改或删除当前游标所在的行。
例如修改当前游标所在行的用户的余额,我们可以如下操作:
UPDATEcustomer
SETbalance=1000
WHERECURRENTofcustomerCursor;
删除当前行的操作如下:
DELETEFROMCustomer
WHERECURRENTOFCustomerCursor;
但是如果您当前使用的数据库是Sybase,您需要修改数据库的参数,将游标可修改的值定为1,才能执行上述操作。
这一赋值在连接数据库的前后进行均可。
SQLCA.DBParm="CursorUpdate=1"
另外一个内容是动态游标,也就是说您可以运行过程中动态地形成游标的SELECT语句。
这同在PowerBuilder中动态地使用嵌入式SQL一样,需要用到DynamicStagin-gArea等数据类型,这已超出了本节的范围。
游标的使用
提到游标这个词,人们想到的是在屏幕上一个闪动的方框,用以指示用户将要输入字符的位置。
而在关系型数据库的SQL语言中,游标却有另外的含义,它是存放结果集的数据对象。
为什么要用到游标
在某些PowerBuilder应用程序的开发中,您可能根本用不到游标这样一个对象。
因为在其它工具开发中很多需用游标实现的工作,在PowerBuilder中却已有DataWin-dow来代劳了。
事实上,DataWindow不仅可以替代游标进行从后台数据库查询多条记录的复杂操作,而且还远不止这些。
但是同DataWindow和DataStore相比,游标也有其自身的优点,比如系统资源占用少,操作灵活,可根据需要定义变量类型如全局、实例或局部类型和访问类型如私有或公共等。
游标的操作
使用游标有四种基本的步骤:
声明游标、打开游标、提取数据、关闭游标。
声明游标
象使用其它类型的变量一样,使用一个游标之前,首先应当声明它。
游标的声明包括两个部分:
游标的名称;这个游标所用到的SQL语句。
如要声明一个叫作Cus-tomerCursor的游标用以查询地址在北京的客户的姓名、帐号及其余额,您可以编写如下代码:
DECLARECustomerCursorCURSORFOR
SELECTacct_no,name,balance
FROMcustomer
WHEREprovince="北京";
在游标的声明中有一点值得注意的是,如同其它变量的声明一样,声明游标的这一段代码行是不执行的,您不能将debug时的断点设在这一代码行上,也不能用IF...ENDIF语句来声明两个同名的游标,如下列的代码就是错误的。
IFIs_prov="北京"THEN
DECLARECustomerCursorCURSORFOR
SELECTacct_no,name,balance
FROMcustomer
WHEREprovince="北京";
ELSE
DECLARECustomerCursorCURSORFOR
SELECTacct_no,name,balance
FROMcustomer
WHEREprovince〈〉"北京";
ENDIF
打开游标
声明了游标后在作其它操作之前,必须打开它。
打开游标是执行与其相关的一段SQL语句,例如打开上例声明的一个游标,我们只需键入:
OPENCustomerCursor;
由于打开游标是对数据库进行一些SQLSELECT的操作,它将耗费一段时间,主要取决于您使用的系统性能和这条语句的复杂程度。
如果执行的时间较长,可以考虑将屏幕上显示的鼠标改为hourglass。
提取数据
当用OPEN语句打开了游标并在数据库中执行了查询后,您不能立即利用在查询结果集中的数据。
您必须用FETCH语句来取得数据。
一条FETCH语句一次可以将一条记录放入程序员指定的变量中。
事实上,FETCH语句是游标使用的核心。
在DataWindow和DataStore中,执行了Retrieve（）函数以后,查询的所有结果全部可以得到;而使用游标,我们只能逐条记录地得到查询结果。
已经声明并打开一个游标后,我们就可以将数据放入任意的变量中。
在FETCH语句中您可以指定游标的名称和目标变量的名称。
如下例:
FETCHCustmerCur-sor
INTO:
ls_acct_no,
:
ls_name,
:
ll_balance;
从语法上讲,上面所述的就是一条合法的取数据的语句,但是
升级会员 