SQL游标原理和使用方法

数据库开发过程中,当你检索的数据只是一条记录时,你所编写的事务语句代码往往使用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,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

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等数据类型,这已超出了本节的范围。

时间: 2024-09-08 01:01:41

SQL游标原理和使用方法的相关文章

sql游标中循环使用方法

sql游标中循环使用方法 mysql教程> mysql> CREATE TABLE Employee(     ->     id            int,     ->     first_name    VARCHAR(15),     ->     last_name     VARCHAR(15),     ->     start_date    DATE,     ->     end_date      DATE,     ->    

ORACLE获取SQL绑定变量值的方法总结

  本文总结一下ORACLE数据库中如何获取SQL绑定变量值的方法,在SQL优化调优过程中,经常会用到这方面的知识点.在此梳理.总结一下,方面日后查找.翻阅.     方法1:查询V$SQL   V$SQL视图中的BIND_DATA字段用来存储绑定变量的值,但是从这个视图查询绑定变量的值,有很大的局限性:       1: 它的记录频率受_cursor_bind_capture_interval隐含参数控制,默认值为900,表示每900秒记录一次绑定值,也就是说在900内,绑定变量值的改变不会反

go语言的sql包原理与用法分析_Golang

本文实例讲述了go语言的sql包原理与用法.分享给大家供大家参考,具体如下: go的sql包是在pkg/database中,里面的两个包sql和sql/driver可以一起看.建议看这个两个包之前可以先看看sql文件夹下的doc.txt.这个文档说了几点比较重要的: 1 这两个包是真正Go风格的包. 2 这使用这两个包就不需要关于并发处理了,也不需要维护自己的数据库连接池了,一旦建立了一个连接,这个连接是可以在各个goroutine之间共用的. 3 sql/driver提供的是数据库的接口,具体

Hibernate延迟加载原理与实现方法_java

本文实例讲述了Hibernate延迟加载原理与实现方法.分享给大家供大家参考,具体如下: 为了进一步优化Hibernate的性能,可以使用: 延迟加载技术.管理数据抓取策略.进行缓存管理 等方面考虑来提高Hibernate的性能. 1. 延迟加载(load) 延迟加载(load)是Hibernate为提高程序执行效率而提供的一种机制,即只有真正使用该对象的数据时才会创建. 延迟加载的过程:通过代理(Proxy)机制来实现延迟加载.Hibernate从数据库获取某一个对象数据时.获取某一个对象的集

Oracle出现超出打开游标最大数的解决方法_oracle

本文实例讲述了Oracle出现超出打开游标最大数的解决方法.分享给大家供大家参考,具体如下: Java代码在执行conn.createStatement()和conn.prepareStatement()的时候,实际上都是相当与在数据库中打开了一个cursor.尤其是,假如你的createStatement和prepareStatement是在一个循环里面的话,就会非常轻易出现这个问题.因为游标一直在不停的打开,而且没有关闭. 一般来说,我们在写Java代码的时候,createStatement

Sql注入原理简介_动力节点Java学院整理

一.什么是sql注入呢? 所谓SQL注入,就是通过把SQL命令插入到Web表单递交或输入域名或页面请求的查询字符串,最终达到欺骗服务器执行恶意的SQL命令,比如先前的很多影视网站泄露VIP会员密码大多就是通过WEB表单递交查询字符暴出的,这类表单特别容易受到SQL注入式攻击.当应用程序使用输入内容来构造动态sql语句以访问数据库时,会发生sql注入攻击.如果代码使用存储过程,而这些存储过程作为包含未筛选的用户输入的字符串来传递,也会发生sql注入. 黑客通过SQL注入攻击可以拿到网站数据库的访问

PL/SQL游标(原创)

游标的相关概念及特性定义映射在结果集中某一行数据的具体位置,类似于C语言中的指针.即通过游标方式定位到结果集中某个特定的行,然后根据业务需求对该行进行相应特定的操作.游标的分类显示游标:即用户自定义游标,专门用于处理select语句返回的多行数据隐式游标:系统自动定义的游标,记录集只有单行数据,用于处理select into 和DML语句游标使用的一般过程:显示游标:声明, 打开, 读取, 关闭隐式游标:直接使用读取,声明.打开.关闭都是系统自动进行的显示游标的过程描述a.声明游标CURSOR

用户 &amp;#39;sa&amp;#39; 登录失败。原因: 未与信任 SQL Server 连接 的解决方法

用户 'sa' 登录失败.原因: 未与信任 SQL Server 连接 的解决方法如下: 一.控制面板->服务->MS SQL SERVER->登陆-->本地系统帐户-->重新启动MS SQL SERVER用windows验证登陆查询分析器-->执行 sp_password null,sa新密码,'sa' 二."无法连接到服务器,用户xxx登陆失败"该错误产生的原因是由于SQL Server使用了"仅 Windows"的身份验证方

Sql Server2000孤立用户解决方法

server|解决 Sql Server2000孤立用户解决方法关键词: 孤立用户                                          把数据库备份还原到另一个服务器时,可能会遇到孤立用户的问题.下面列出了MS的方法(来自帮助)和自己的方法方法一: 孤立用户疑难解答把数据库备份还原到另一个服务器时,可能会遇到孤立用户的问题.下面的方案显示并解决了这个问题: 通过执行 sp_addlogin,把登录 janetl 改名为 dbo. sp_addlogin 'janet