数据库的游标概念理解

1.游标和游标的优点

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

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

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 游标使用在服务器端，所以被称为服务器游标，也被称为后台游标，而客户端游标被称为前台游标。在本章中我们主要讲述服务器（后台）游标

游标使用示例(1)

　　create proc cursorTest

　　@_id int=0,

　　@_name varchar(50)=''

　　as

　　--创建游标

　　declare @cursor cursor

　　--设定游标欲操作的数据集

　　set @cursor=cursor for

　　select _id,_name from users

　　--打开游标

　　open @cursor

 　　--移动游标指向到第一条数据，提取第一条数据存放在变量中

　　fetch next from @cursor into @_id,@_name

　　--如果上一次操作成功则继续循环

　　while(@@fetch_status=0)begin

　　--操作提出的数据

　　print @_name

　　--继续提下一行

　　fetch next from @cursor into @_id,@_name

　　end

　　--关闭游标

 　　close @cursor

　　--删除游标

　　deallocate @curso

　　游标使用示例(2)

　　CREATE proc [dbo].[As_Proc_GetPlanDetail]

　　(

　　@ids varchar(max)--

　　)

　　as

　　create table #temp

　　(

　　dept varchar(100),

　　sorttitle varchar(200),

　　title varchar(200),

　　spec varchar(50),

　　model varchar(50),

　　budget decimal(18,2),

　　amount int,

　　sgamount int,

　　htamount int,

　　ysamount int,

　　fenfamount int

　　)

　　declare @did varchar(32)

　　declare @sorttitle varchar(200)

　　declare @title varchar(200)

　　declare @spec varchar(50)

　　declare @model varchar(50)

　　declare @budget decimal(18,2)

　　declare @amount int

　　declare @sgamount int

　　declare @htamount int

　　declare @ysamount int--验收数量

　　declare @fenfamount int--分发数量

　　declare cursor1 cursor for

　　select rowid,dbo.clip(ypdept,':',1) from As_year_plan where Charindex(rowid,@ids,0)>0 union select rowid,dbo.clip(department,':',1) from As_assets_requisition where Charindex(rowid,@ids,0)>0 -----------------------------------------------控制计划

　　declare @rowid varchar(32)

　　declare @dept varchar(100)

　　open cursor1

　　fetch next from cursor1 into @rowid,@dept

　　while @@fetch_status=0

　　begin

　　declare cursor2 cursor for select a.rowid, b.sorttitle,a.title,a.spec,a.model,a.budget,a.amount from As_assts_planDetai a left join As_assetsSort b on a.sortId=b.rowid where planID=@rowid

　　open cursor2

　　fetch next from cursor2 into @did, @sorttitle, @title,@spec, @model ,@budget ,@amount

　　while @@fetch_status=0

　　begin

　　select @sgamount= isnull(sum(a.amount),0) from As_assets_buyDetail a inner join As_assets_buy b on a.buyId=b.rowid where a.planId=@did and b.state=1

　　--print @did

　　select @htamount= isnull(sum(a.amount),0) from As_contract_detail a inner join As_stock_contract b on a.contId=b.rowid where b.state=1 and a.bydetId in(

　　select c.rowid from As_assets_buyDetail c inner join As_assets_buy d on c.buyId=d.rowid where c.planId=@did and d.state=1)

 

　　---验收数量

　　select @ysamount=count(1) from As_AssetsInfo a inner join As_contract_detail b on a. contractId=b.rowid where b.bydetId in(select rowid from As_assets_buyDetail where planId=@did)

　　--分发数量

　　select @fenfamount=count(1) from As_AssetsInfo a inner join As_contract_detail b on a. contractId=b.rowid where b.bydetId in(select rowid from As_assets_buyDetail where planId=@did) and a.drawState=1

　　insert into #temp(dept, sorttitle, title , spec, model , budget ,amount,sgamount,htamount, ysamount,fenfamount) values(@dept,@sorttitle, @title , @spec, @model , @budget ,@amount,@sgamount,@htamount,@ysamount,@fenfamount)

　　fetch next from cursor2 into @did, @sorttitle, @title,@spec, @model ,@budget ,@amount

　　end

　　close cursor2 --关闭游标

　　deallocate cursor2

　　fetch next from cursor1 into @rowid,@dept

游标详细说明

　　RS.OPEN SQL,CONN,A,B
　　参数A为设定游标的类型，其取值为： 0 仅向前游标，只能向前浏览记录，不支持分页、Recordset、BookMark

　　1 键集游标，其他用户对记录所做的修改将反映到记录集中，但其他用户增加或删除记录不会反映到记录集中。支持分页、Recordset、BookMark

　　2 动态游标功能最强，但耗资源也最多。用户对记录所做的修改，增加或删除记录都将反映到记录集中。支持全功能浏览。

　　3 静态游标，只是数据的一个快照，用户对记录所做的修改，增加或删除记录都不会反映到记录集中。支持向前或向后移动

　　参数B为记录集的锁定类型，其取值为：

 

　　1 锁定类型，默认的，只读，不能作任何修改

　　2 当编辑时立即锁定记录，最安全的方式

　　3 只有在调用Update方法时才锁定记录集，而在此前的其他操作仍可对当前记录进行更改、插入和删除等

　　4 当编辑时记录不会被锁定，而更改、插入和删除是在批处理方式下完成的

　　打开数据记录集方法其实不止一种，但是我们用的最多的就是

　　rs.open sql,1,1的方法，可是后面的数字参数很多人不解其意，下面我们来介绍一下。

　　其实open方法后面有多个参数

　　CursorType LockType CommandType

　　比如 rs.open sql,1,1

　　也可以写成

　　rs.cursorType = 1

　　rs.LockType = 1

　　rs.open sql

　　其中CursorType代表从一个表或者一个SQL查询结果返回的记录。

　　这个参数有四个值分别是：

　　adOpenForwardOnly 表示只允许在记录集内的记录间往前移动。这个是缺省值。

　　adOpenKeyset 反映由其它用户所做的对记录的改变或者删除动作，但并不反映由其它用户做作的添加新记录的动作。

　　adOpenDynamic 反映由其它用户所做的对记录的改变或者删除动作，包括添加的新记录

　　adOpenStatic 不反映其它用户对记录所做的修改，添加，删除动作。

　　这四个值VBSCRIPT预定义位

　　adOpenForwardOnly = 0

　　adOpenKeyset = 1

　　adOpenDynamic = 2

　　adOpenStatic = 3

　　lockType 表示当打开记录集时，数据提供者用于锁定数据库的类型：

　　adLockReadOnly 数据不能改变，这是缺省值！

　　adLockPessimistic 数据提供者在开始编辑数据的时候锁定记录

　　adLockOptimistic 仅当调用update方法时，数据提供者锁定记录

　　adLockBatchOptimistic 用于批处理修改

　　他们的常量值定义分别是：

　　adLockReadOnly = 1

　　adLockPessimistic = 2

　　adLockOptimistic = 3

　　adLockBatchOptimistic = 4

　　rs.open sql,conn,1,1 读取记录 select

　　rs.open sql,conn,1,3 只更新记录最好 update

　　rs.open sql,conn,2,3 插入和删除最好 insert delete

　　end

　　close cursor1 --关闭游标

　　deallocate cursor1 --释放游标

　　--查询临时表

　　select * from #temp