游标使用示例

    在论坛上帮一个网友解决问题，顺便练习了一下游标编程。做一下笔记，便于以后用到游标编程时查阅。

    --期望能更新股票的xxx值，xxx的计算规则是：

    --前一天的xxx*25/27+当天的closeprice*2/27；

    --每只股票的最早那条记录没有前一天的xxx，则取最早那天的closeprice代替；

    --每一条记录由stockcode、stockdate唯一确定。

create table kim(stockcode varchar(8),
  stockdate datetime,
  closeprice decimal(18, 3),
  xxx decimal(18, 3),
  primary key (stockcode, stockdate)
);
insert [kim]
select 'SH601318','20130701',34.3,NULL union all
select 'SH601318','20130703',33.81,NULL union all
select 'SH601318','20130702',34.22,NULL union all
select 'SZ002322','20130808',14.64,NULL union all
select 'SH601318','20130706',34.03,NULL union all
select 'SZ002322','20130809',14.16,NULL;

declare mycs cursor for
select stockcode, closeprice
from kim
order by stockcode, stockdate
for update of xxx;
open mycs;
declare @sk varchar(8), @prc decimal(18,3);
declare @sk_l varchar(8), @xxx_l decimal(18,3);
fetch next from mycs into @sk, @prc;
update kim set xxx=@prc where CURRENT of mycs;
set @sk_l=@sk;
set @xxx_l=@prc;
while @@fetch_status = 0
begin
  fetch next from mycs into @sk, @prc;
  if @@fetch_status <> 0 break;
  if @sk=@sk_l
  begin
    set @xxx_l=(@xxx_l*25 + @prc*2)/27;
    update kim set xxx=@xxx_l where CURRENT of mycs;
  end
  else
  begin
    set @xxx_l=@prc;
    update kim set xxx=@xxx_l where CURRENT of mycs;
  end;
  set @sk_l=@sk;
end;
close mycs;
deallocate mycs;
select * from kim;
drop table kim;
/*--------结果---------
SH601318	2013-07-01 00:00:00.000	34.300	34.300
SH601318	2013-07-02 00:00:00.000	34.220	34.294
SH601318	2013-07-03 00:00:00.000	33.810	34.258
SH601318	2013-07-06 00:00:00.000	34.030	34.241
SZ002322	2013-08-08 00:00:00.000	14.640	14.640
SZ002322	2013-08-09 00:00:00.000	14.160	14.604
-------------------------*/

    类似这种需要按顺序每行迭代更新的场景，经网友测试，在大数据量（超过50万记录）条件下，效率明显优于 CTE 递归。

    另外发现，在定义游标时，SELECT 后字段数量减少会明显提高效率。