MS SQL Server 游标及实例（一）

圣凤凌霜

　　一 游标简介
　　（游标）cursor 是系统为用户开设的一个数据缓冲区，存放sql语句的执行结果，每个游标区有一个个名字，用户可以用sql语句逐一从游标中获取记录，并赋给主变量，交由主语言进行处理
　　提供了一种从表中检索出的数据进行操作的灵活手段。本质：是一种能从包含多条数据记录的结果集中每次提取一条记录的机制。游标总是与一条sql选择语句相关联因为游标由结果集（可以是零条，一条，或由相关的选择语句检索出的多条记录）和结果集中指向特定记录的游标位置组成，当决定对结果集进行处理时，必须声明一个指向该结果集的游标。
　　如果曾经用 C 语言写过对文件进行处理的程序，那么游标就像您打开文件所得到的文件句柄一样，只要文件打开成功，该文件句柄就可代表该文件。对于游标而言，其道理是相同的。可见游标能够实现按与传统程序读取平面文件类似的方式处理来自基础表的结果集，从而把表中数据以平面文件的形式呈现给程序。
　　

　　　　二 游标分类
　　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
　　游标定义例子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 @cursor

　　　　游标例子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,>

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

　　end

　　close cursor2 --关闭游标

　　deallocate cursor2

　　fetch next from cursor1 into @rowid,@dept

　　end

　　close cursor1 --关闭游标

　　deallocate cursor1 --释放游标

　　--查询临时表

　　select * from #temp