SQL游标原理和使用方法

SQL游标原理和使用方法51Testing软件测试网0Dy$omN]

在数据库开发过程中，当你检索的数据只是一条记录时，你所编写的事务语句代码往往使用SELECT INSERT语句。但是我们常常会遇到这样情况，即从某一结果集中逐一地读取一条记录。那么如何解决这种问题呢？游标为我们提供了一种极为优秀的解决方案。51Testing软件测试网}0p%?|yD)m;L
1.1游标和游标的优点
6]z5g#RP3V,n0   在数据库中，游标是一个十分重要的概念。游标提供了一种对从表中检索出的数据进行操作的灵活手段，就本质而言，游标实际上是一种能从包括多条数据记录的结果集中每次提取一条记录的机制。游标总是与一条T_SQL选择语句相关联因为游标由结果集（可以是零条、一条或由相关的选择语句检索出的多条记录）和结果集中指向特定记录的游标位置组成。当决定对结果集进行处理时，必须声明一个指向该结果集的游标。如果曾经用C语言写过对文件进行处理的程序，那么游标就像您打开文件所得到的文件句柄一样，只要文件打开成功，该文件句柄就可代表该文件。对于游标而言，其道理是相同的。可见游标能够实现按与传统程序读取平面文件类似的方式处理来自基础表的结果集，从而把表中数据以平面文件的形式呈现给程序。51Testing软件测试网dgPP|kd3K

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

1.2游标种类
h2w0]U ?F/IM0MS SQL SERVER支持三种类型的游标：Transact_SQL游标，API服务器游标和客户游标。51Testing软件测试网X*a1`E&[
（1）Transact_SQL游标51Testing软件测试网-pp,xiR!X"m$X5G
    Transact_SQL游标是由DECLARE CURSOR语法定义、主要用在Transact_SQL脚本、存储过程和触发器中。Transact_SQL游标主要用在服务器上，由从客户端发送给服务器的Transact_SQL语句或是批处理、存储过程、触发器中的Transact_SQL进行管理。Transact_SQL游标不支持提取数据块或多行数据。
z3_1]s,|zMR0（2）API游标
nWK]^9l%Ai&B/n0    API游标支持在OLE DB，ODBC以及DB_library中使用游标函数，主要用在服务器上。每一次客户端应用程序调用API游标函数，MS SQL SEVER的OLE DB提供者、ODBC驱动器或DB_library的动态链接库（DLL）都会将这些客户请求传送给服务器以对API游标进行处理。51Testing软件测试网0i S0[Zi%B F
（3）客户游标
2G_o{1N%@#C0   客户游标主要是当在客户机上缓存结果集时才使用。在客户游标中，有一个缺省的结果集被用来在客户机上缓存整个结果集。客户游标仅支持静态游标而非动态游标。由于服务器游标并不支持所有的Transact-SQL语句或批处理，所以客户游标常常仅被用作服务器游标的辅助。因为在一般情况下，服务器游标能支持绝大多数的游标操作。
K g"f6AQB$w(L \~;kl0   由于API游标和Transact-SQL游标使用在服务器端，所以被称为服务器游标，也被称为后台游标，而客户端游标被称为前台游标。在本章中我们主要讲述服务器（后台）游标。

select count(id) from info51Testing软件测试网'|U%P4?j2b'A8I d;n3\

select * from info51Testing软件测试网T3t m*sY8|N

--清除所有记录
3k{;kdv:S%ts0truncate table info51Testing软件测试网0H(XWvj

declare @i int
9y;i.Xtt.N\0set @i=151Testing软件测试网F7vB}8N
while @i<100000051Testing软件测试网F}.U`8X8q@ };G
begin51Testing软件测试网e }uSb6r\
 insert into info values('Justin'+str(@i),'深圳'+str(@i))51Testing软件测试网[
pfh8K.X
 set @i=@i+151Testing软件测试网PJ-x.`r`
end

1.3游标操作
9h0`4_1N%a2S/Q;o0使用游标有四种基本的步骤:声明游标、打开游标、提取数据、关闭游标。51Testing软件测试网7s1C4I8Xn0m7Y9_%|j
声明游标51Testing软件测试网`#W0F.bqW
    象使用其它类型的变量一样,使用一个游标之前,首先应当声明它。游标的声明包括两个部分:游标的名称;这个游标所用到的SQL语句。如要声明一个叫作Cus-tomerCursor的游标用以查询地址在北京的客户的姓名、帐号及其余额,您可以编写如下代码:51Testing软件测试网nsC)?tbm
DECLARE CustomerCursor CURSOR FOR
7q.N
w&Z,u6H&L [F0SELECT acct_no,name,balance51Testing软件测试网$y3u^;u@ R
FROM customer
Tg(Q#W;t!L0WHERE province="北京";51Testing软件测试网`*l._lW4ytD
    在游标的声明中有一点值得注意的是,如同其它变量的声明一样,声明游标的这一段代码行是不执行的,您不能将debug时的断点设在这一代码行上,也不能用IF...END IF语句来声明两个同名的游标,如下列的代码就是错误的。
(h'k N(ot0^/RY$z0IF Is_prov="北京"THEN51Testing软件测试网O~J"{kn/aaP)R
DECLARE CustomerCursor CURSOR FOR51Testing软件测试网DE:D?b;n ByCi {
SELECT acct_no,name,balance
;qPt.\u#z1o0FROM customer
zNe I+[!{'Ny0WHERE province="北京";51Testing软件测试网|/Q;Q8OV&i%Lx
ELSE
2?i0_G5Pa S:tr0DECLARE CustomerCursor CURSOR FOR51Testing软件测试网 unEmg+z
SELECT acct_no,name,balance51Testing软件测试网3U/K;eEM3x#S|
FROM customer51Testing软件测试网.d#@sXl'qSTl
WHERE province〈〉"北京";51Testing软件测试网BAQmZ&ESC FU
END IF51Testing软件测试网1xp]!Sv
打开游标51Testing软件测试网(y,U(~1KaAl H
y/XU lF
   声明了游标后在作其它操作之前,必须打开它。打开游标是执行与其相关的一段SQL语句,例如打开上例声明的一个游标,我们只需键入:
^8z t}6AJ7e{8zN5b0OPEN CustomerCursor;
#c1m0P(M8qRC!LIY9]H0   由于打开游标是对数据库进行一些SQL SELECT的操作,它将耗费一段时间,主要取决于您使用的系统性能和这条语句的复杂程度。如果执行的时间较长,可以考虑将屏幕上显示的鼠标改为hourglass。51Testing软件测试网#o(k&O3hEJ"~
提取数据51Testing软件测试网sV$f-\@R
    当用OPEN语句打开了游标并在数据库中执行了查询后,您不能立即利用在查询结果集中的数据。您必须用FETCH语句来取得数据。一条FETCH语句一次可以将一条记录放入程序员指定的变量中。事实上,FETCH语句是游标使用的核心。在DataWindow和DataStore中,执行了Retrieve()函数以后,查询的所有结果全部可以得到;而使用游标,我们只能逐条记录地得到查询结果。
h3c)Y_XT0    已经声明并打开一个游标后,我们就可以将数据放入任意的变量中。在FETCH语句中您可以指定游标的名称和目标变量的名称。如下例:
H!v6LqI+h3W0FETCH CustmerCur-sor51Testing软件测试网\Hai0dF
INTO:ls_acct_no,
|/a,AX+VFYz0:ls_name,51Testing软件测试网*@ lr,Ed^7G
:ll_balance;
%b_AV)AYoOz\0   从语法上讲,上面所述的就是一条合法的取数据的语句,但是一般我们使用游标却还应当包括其它的部分。正如我们前面所谈到的,游标只能一次从后台数据库中取一条记录,而在多数情况下,我们所想要作的是在数据库中从第一条记录开始提取,一直到结束。所以我们一般要将游标提取数据的语句放在一个循环体内,直至将结果集中的全部数据提取后,跳出循环圈。通过检测SQLCA.SQL-CODE的值,可以得知最后一条FETCH语句是否成功。一般,当SQLCODE值为0时表明一切正常,100表示已经取到了结果集的末尾,而其它值均表明操作出了问题,这样我们可以编写以下的代码:
u(V/Fmk^&K1E0lb_continue=True51Testing软件测试网0i"hPP,u o%s`[e
ll_total=051Testing软件测试网;krU+N3\ DobP
DO WHILE lb_continue
&OX3?'KT~$L&a0FETCH CustomerCur-sor51Testing软件测试网eDB3{"j
INTO:ls_acct_no,
r%xM{|'`3u0:ls_name,
*@$J-i*qOpg0:ll_balance;51Testing软件测试网-uC:^NP ag]
If sqlca.sqlcode=0 Then51Testing软件测试网rS2Yn%L*h
ll_total+=ll_balance51Testing软件测试网/x n$yI&t
Else51Testing软件测试网$hM%D3T7pf
lb_continue=False51Testing软件测试网W+AY#rN2Ni9WH6a
End If
&~.n I%c4@(s4MKAd0LOOP
YKB0U+t9g2D0   循环体的结构有多种,这里提到的是最常见的一种。也有的程序员喜爱将一条FETCH语句放在循环体的前面,循环体内再放置另外一条FETCH语句,并检测SQLCA.SQLCODE是否为100。但是这样做,维护时需同时修改两条FETCH语句,稍麻烦了些。
c}1pyP-z N0关闭游标51Testing软件测试网.OP:Z8}K
   在游标操作的最后请不要忘记关闭游标,这是一个好的编程习惯,以使系统释放游标占用的资源。关闭游标的语句很简单:
*Ua|x SF|0CLOSE CustomerCursor;51Testing软件测试网.G$N)_g)g^K
使用Where子句子51Testing软件测试网CB8^c+p*s b&B
   我们可以动态地定义游标中的Where子句的参数,例如在本例中我们是直接定义了查询省份是北京的记录,但也许在应用中我们要使用一个下拉式列表框,由用户来选择要查询的省份,我们该怎样做呢?51Testing软件测试网jk0vc1Hc
我们在前面曾经提到过,DECLARE语句的作用只是定义一个游标,在OPEN语句中这个游标才会真正地被执行。了解了这些,我们就可以很方便地实现这样的功能,在DECLARE的Where子句中加入变量作参数,如下所示:51Testing软件测试网$FS!p*r2M*q7y
[)K
DECLARE CustomerCursor CURSOR FOR
:d/j]'R*E6u-]0SELCECT acct_no,name,balance51Testing软件测试网~6tlu1g
FROM customer51Testing软件测试网'k DyR,T*c v J
WHERE province=:ls_province;51Testing软件测试网 l8j h]d1d]ppK
∥定义ls_province的值51Testing软件测试网o${&V-\5^4R"|
OPEN CustomerCursor;
1N:`5hy9a,ro5P0游标的类型51Testing软件测试网,B](UR
_
    同其它变量一样,我们也可以定义游标的访问类型:全局、共享、实例或局部,游标变量的命名规范建议也同其它变量一样。
$h"w:?h$Kz0--声明游标51Testing软件测试网jF6W
R~of1cE
declare my_cursor cursor keyset for select * from info51Testing软件测试网+U9Pj0[)X~
--删除游标资源51Testing软件测试网/{_7l%vl'V}
deallocate my_cursor

--打开游标,在游标关闭或删除前都有效
R,^c&D&s4U'L!|0open my_cursor
j e7T[$x |&by@})v0--关闭游标51Testing软件测试网+m}znEM
close my_cursor

--声明局部变量
eE0gh4A2n0declare @id int,@name varchar(20),@address varchar(20)51Testing软件测试网\vR&myf/D
--定位到指定位置的记录
L&d1c
m'N"X'O0fetch absolute 56488 from my_cursor into @id,@name,@address51Testing软件测试网(h6y0z ~)D[8v
select @id as id,@name as name,@address as address
PN0glzz0--定位到当前记录相对位置记录51Testing软件测试网w
~C `j:DS
fetch relative -88 from my_cursor into @id,@name,@address
1Mp3QX0A%h.bn6ov8O0select @id as id,@name as name,@address as address51Testing软件测试网 fmW9\Mpn/{BH
--定位到当前记录前一条
5r9@ vA9C&G%C`0fetch prior from my_cursor into @id,@name,@address51Testing软件测试网i4|dZ!g X{C @$O
select @id as id,@name as name,@address as address51Testing软件测试网5]KA;}f/k
--定位到当前记录后一条
4U%t/lO [3D&I0fetch next from my_cursor into @id,@name,@address51Testing软件测试网R X,L!U*C HZ#j(S
select @id as id,@name as name,@address as address51Testing软件测试网x J&J/\XCYM:B
--定位到首记录
$X|J4E'{i0fetch first from my_cursor into @id,@name,@address51Testing软件测试网u`]nmY
select @id as id,@name as name,@address as address51Testing软件测试网{?z;h A/C`
--定位到尾记录51Testing软件测试网8n+L{j1u4Ah/f,`
fetch last from my_cursor into @id,@name,@address51Testing软件测试网 \9K V|F
select @id as id,@name as name,@address as address

实例：
Z8h4l%GH0 use database151Testing软件测试网'T^$g\)cT H1u
declare my_cursor cursor scroll dynamic51Testing软件测试网}
vlA#a3b
 /**//*scroll表示可随意移动游标指       针（否则只能向前），dynamic表示可以读写游标（否则游标只读）*/51Testing软件测试网]%c0N!K.P-J[1R
for51Testing软件测试网CFs$Oo1kw
select productname from  product51Testing软件测试网+F'@ D:@;FL[

open my_cursor
?)IO'ZA
e0declare @pname sysname51Testing软件测试网1oV K(\o$~'i;|:x
fetch next from my_cursor into @pname
6n(yc2C9y`\0while(@@fetch_status=0)51Testing软件测试网mOc7_8vBg9|:K
  begin51Testing软件测试网L*R5`4A!~7c#F3w1s
  print 'Product Name: ' + @pname51Testing软件测试网,ZCF:tQ%x;d4eQ
    fetch next from my_cursor into @pname
P!B%q1u8m+U^+|0  end
{ gW![2`0fetch first from my_cursor into @pname51Testing软件测试网$a0XE4o.DWf
print @pname
]G0s#H pKN~}0/**//*update product set productname='zzg' where current of my_cursor */51Testing软件测试网k"u4Fyd
/**//*delete from product where current of my_cursor */
,S \A![%W1@d]0close my_cursor
J k0D+Lh0deallocate my_cursor

1.4游标的高级技巧

尽管目前基于SQL语句的后台数据库所支持的语言都大致相当,但对游标的支持却有着一些差异,例如对滚动游标支持。所谓滚动游标,就是程序员可以指定游标向前后任意一个方向滚动。如在Informix中,您甚至还可以将游标滚向结果集开头或末尾,使用的语句分别是FETCH FIRST,FETCH LAST、FETCH PRIOR和FETCH NEXT。当程序员用FETCH语句,其缺省是指FETCH NEXT。由于滚动是在数据库后台实现的,所以滚动游标为用户编程提供了极大的方便。51Testing软件测试网:[5yt8uM7}%BJ[g]
    对游标支持的另一个不同是可修改游标。上述游标的使用都是指只读游标,而象Oracle、Sybase等数据库却另外支持可作修改的游标。使用这样的数据库,您可以修改或删除当前游标所在的行。例如修改当前游标所在行的用户的余额,我们可以如下操作:51Testing软件测试网)H'OS E;kf
UPDATE customer
ob8T y"N ?~0B0SET balance=100051Testing软件测试网E"]`'h`ZYT
WHERE CURRENT of customerCursor;
a%thRc*i"wr F0  删除当前行的操作如下:
c{S S0vn!Z0DELETE FROM Customer
Gk V*U_R0WHERE CURRENT OF CustomerCursor;51Testing软件测试网krRVA(u%t(u
    但是如果您当前使用的数据库是Sybase,您需要修改数据库的参数,将游标可修改的值定为1,才能执行上述操作。这一赋值在连接数据库的前后进行均可。51Testing软件测试网2GewGSW
SQLCA.DBParm="Cursor Update=1"51Testing软件测试网&U*~/s|2EwG5pJ
   另外一个内容是动态游标,也就是说您可以运行过程中动态地形成游标的SELECT语句。这同在PowerBuilder中动态地使用嵌入式SQL一样,需要用到DynamicStagin-gArea等数据类型,这已超出了本节的范围。51Testing软件测试网*R u(KjVqlp