MSSQL游标

http://blog.163.com/berserk_ng/blog/static/44035727200772911340577/

1. 关系数据库中的操作会对整个行集起作用。由 SELECT语句返回的行集包括满足该语句的 WHERE子句中条件的所有行。这种由语句返回的完整行集称为结果集。应用程序，特别是交互式联机应用程序，并不总能将整个结果集作为一个单元来有效地处理。这些应用程序需要一种机制以便每次处理一行或一部分行。游标就是提供这种机制的对结果集的一种扩展.

2. 游标可以实现的功能::

  (1)允许定位在结果集的特定行。

  (2)从结果集的当前位置检索一行或一部分行.

  (3)支持对结果集中当前位置的行进行数据修改。

  (4)为由其他用户对显示在结果集中的数据库数据所做的更改提供不同级别的可见性支持

3.游标的使用:

(1)定义游标变量:

    Declare cursor_name  cursor for

(1)打开游标:

    Opencursor_name

(2)使用游标

    Fetchnext 紧跟当前行返回结果行，并且当前行递增为返回行。如果 FETCH NEXT为对游标的第一次提取操作，则返回结果集中的第一行。NEXT为默认的游标提取选项。

    Fetchprior 返回紧邻当前行前面的结果行，并且当前行递减为返回行。如果 FETCHPRIOR为对游标的第一次提取操作，则没有行返回并且游标置于第一行之前。

   Fetchfirst  返回游标中的第一行并将其作为当前行。

   Fetchlast  返回游标中的最后一行并将其作为当前行.

  Fetch  ABSOLUTE { n | @nvar}

如果 n 或 @nvar 为正数，则返回从游标头开始的第n 行，并将返回行变成新的当前行。如果 n 或 @nvar为负数，则返回从游标末尾开始的第 n行，并将返回行变成新的当前行。如果 n 或 @nvar 为 0，则不返回行。n必须是整数常量，并且 @nvar 的数据类型必须为 smallint、tinyint 或int。

  Fetch  RELATIVE { n | @nvar}

如果 n 或 @nvar 为正数，则返回从当前行开始的第n 行，并将返回行变成新的当前行。如果 n 或 @nvar为负数，则返回当前行之前第 n 行，并将返回行变成新的当前行。如果 n或 @nvar 为 0，则返回当前行。在对游标完成第一次提取时，如果在将 n或 @nvar 设置为负数或 0 的情况下指定 FETCH RELATIVE，则不返回行。n必须是整数常量，@nvar 的数据类型必须为 smallint、tinyint 或int.

 

(3)关闭游标

       Close cursor_name

(4)释放游标

   DEALLOCATE cursor_name

4.游标的行为:

(1)可滚动 SCROLL

如果在 DECLARE 语句指定了关键字SCROLL，则游标支持所有的提取选项。

(2)敏感性 INSENSITIVE

   游标的敏感性行为定义了对基行（用于建立游标）所做的更新是否对游标可见。敏感性也定义了能否通过游标进行更新.

   如果在 Transact-SQL DECLARE 语句中指定了 INSENSITIVE关键字，游标将不反映数据修改。游标为只读且不支持更新。

5.游标进行定位更新:

 UPDATE 或 DELETE 语句中的Transact-SQL WHERE CURRENT OF子句来实现定位更新; 

6.索引:索引是与表或视图关联的磁盘上结构，可以加快从表或视图中检索行的速度。索引包含由表或视图中的一列或多列生成的键。这些键存储在一个结构（B树）中，使 SQL Server 可以快速有效地查找与键值关联的行。

7以下情况适合创建索引:

(1)用作查询条件的列,例如主键.

(2)频繁按照范围搜索的列.

(3)连接中频繁使用的列.

8.以下列中不考虑建立索引:

   (1)很少或者从来不作为查询条件的列,因为系统很少或者从来不用这个列的值作为条件查找行.

   (2)在小表中通过索引查找行可能会比在简单地进行全表扫描还慢.

   (3)只从很小的范围内取值的列.

注:  虽然索引很主要,但不是越多越好.一是因为创建了索引要花费时间和占用存储空间,二是因为索引虽然加快了检索的速度,却减慢了数据修改的速度.

 

参考例题：

   usemaster

go

if db_id('sales') is not null

drop database sales

go

create database sales

go

use sales

go

/*

  创建表orders

*/

if exists(select name from sysobjects wherename='orders' and type='u')

drop table orders

go

create table orders

(

  o_id int identity(1151,1)primary key,

  g_namevarchar(30),

  g_num int ,

  g_price money

)

use sales

go

select * from orders

insert into orders(g_name,g_num,g_price)values('cpu',13,123)

insert into orders(g_name,g_num,g_price)values('cpu',20,100)

insert into orders(g_name,g_num,g_price)values('memory',20,320)

insert into orders(g_name,g_num,g_price)values('memory',30,370)

 

/*

  声明游标

*/

declare orders_cursor scrollcursor  --可滚动游标,支持其它的任何操作

for select * from orders

 

/*打开游标*/

open orders_cursor

 

/*

 提取数据

*/

declare @o_id int,@g_name varchar(30),@g_numint,@g_price money

fetch next from orders_cursor into@o_id,@g_name,@g_num,@g_price--提取数据

 

while(@@fetch_status=0)

begin

   printcast(@o_id as varchar(20)) + '  '+ @g_name+'  '+cast(@g_num asvarchar(30))+'  '+cast(@g_price asvarchar(30))

   fetchnext from orders_cursor into@o_id,@g_name,@g_num,@g_price

end

close orders_cursor

deallocate orders_cursor

 

/*游标的其它特性*/

 

/*

  声明游标

*/

declare orders_cursor scrollcursor --可滚动游标,支持其它的任何操作,如果不加scroll

               --关键字,只可以使用fetch next 操作

for select * from orders

 

/*打开游标*/

open orders_cursor

 

/*

 提取数据

*/

declare @o_id int,@g_name varchar(30),@g_numint,@g_price money

fetch next from orders_cursor into@o_id,@g_name,@g_num,@g_price--提取数据

 

while(@@fetch_status=0)

begin

   printcast(@o_id as varchar(20)) + '  '+ @g_name+'  '+cast(@g_num asvarchar(30))+'  '+cast(@g_price asvarchar(30))

   fetchnext from orders_cursor into@o_id,@g_name,@g_num,@g_price

end

close orders_cursor

deallocate orders_cursor

 

update orders set g_num=179 whereg_name='cpu'

/*****************************************************************************

    游标的其它特性行为是否敏感INSENSITIVE

*****************************************************************************/

/*

  声明游标

*/

declareorders_cursor   INSENSITIVEscroll   cursor --如果敏感的话,对基表的所有操作都会影响

                                       --到游标中,增加insensitive表示对基表的操作

                                      --不会影响到游标中,反之亦然.

for select * from orders

 

--修改数据进行判断

--update orders set g_num=178 whereg_name='cpu'

 

/*打开游标*/

open orders_cursor

 

/*

 提取数据

*/

declare @o_id int,@g_name varchar(30),@g_numint,@g_price money

fetch next from orders_cursor into@o_id,@g_name,@g_num,@g_price--提取数据

 

while(@@fetch_status=0)

begin

   printcast(@o_id as varchar(20)) + '  '+ @g_name+'  '+cast(@g_num asvarchar(30))+'  '+cast(@g_price asvarchar(30))

   fetchnext from orders_cursor into@o_id,@g_name,@g_num,@g_price

end

close orders_cursor

deallocate orders_cursor

 

 

/*****************************************************************************

****************游标的应用,删除表内重复的纪录**************************************************************

******************************************************************************/

if object_id('user_info','u')is notnull

drop table user_info

go

 

create table user_info

(

   u_namevarchar(20),

   u_ageint,

   u_addressvarchar(30)

)

 

go

 

--插入数据

insert into user_info

 select 'tom',20,'xian'union  --union插入的过程中不可以插入重复的记录

 select 'jack',10,'wuhan'union

 select 'lilei',26,'beijing'union

 select 'lucy',20,'shanghai'union

 select'dennis',80,'american' union

 select'chalis',20,'tianjiang'

 

insert into user_info

 select 'tom',40,'american'union

 select 'jack',12,'shanghai'union

 select 'lilei',54,'beijing'union

 select 'lucy',54,'tianjiang'union

 select'dennis',60,'shanghai' union

 select'chalis',20,'tianjiang'

go

 

select distinct u_name,u_age,u_address fromuser_info

select * from user_info

/*

  操作:删除表内重复的纪录,让只留下单独的一条有效

*/

declare user_cursor cursorfor  --声明游标,得到表内所有的用户名

select count(*)as u_count, u_name fromuser_info group by u_name

 

--打开游标

open user_cursor

 

declare @name varchar(30),@countint

fetch next from user_cursor into@count,@name

while (@@fetch_status=0)

begin

   declarerecord_cursor cursor for --定义内部游标,根据用户名重新找到所有此用户名

                                   --的纪录

   selectu_name from user_info where u_name=@name

 

  open  record_cursor

   fetchnext from record_cursor

   while(@count>1 and @@fetch_status=0) --删除纪录留下最后的一条

  begin

     delete from user_info where current of record_cursor--删除当前记录的写法

                                            -- current of record_cursor

     set@count=@count-1            --纪录数减一

     fetch next from record_cursor  --获取下一条记录

  end

   closerecord_cursor             --循环结束,关闭内部游标

  deallocaterecord_cursor        --释放内部游标

   fetchnext from user_cursor into @count,@name

 

end

 

close user_cursor

deallocate user_cursor

 

select * from user_info