SQL Server游标

SQL Server游标

• 概念
  游标是SQL的一种数据访问机制。
  可以将游标简单的看成是查询的结果集的一个指针，
  可以根据需要在结果集上面来回滚动，浏览需要的数据。
• 实例一
  –创建一个游标来获取数据，从而根据消费金额来修改消费的等级
  declare cur_cust_level Cursor
  for select id, ConsumeAmount from Customers
  –打开游标
  open cur_cust_level
  –浏览数据，取数据ID, ConsumeAcount
  –取数据
  declare @id int
  declare @Cacount int
  fetch next from cur_cust_level into @id, @Cacount
  –循环往下
  while (@@FETCH_STATUS=0)
  begin
  –print @id+@Cacount
  –修改消费等级
  if (@Cacount <500)
  update Customers set ConsumeLeve='低消费 'where id=@id
  else if (@Cacount< 1000)
  update Customers set ConsumeLeve=‘中消费’ where id=@id
  else
  update Customers set ConsumeLeve=‘高消费’ where id=@id
  fetch next from cur_cust_level into @id,@Cacount
  end
  –关闭游标
  close cur_cust_level
  –释放游标
  deallocate cur_cust_level
• 实例二
  –做统计报表
  –日报表营业额
  –统计每天的消费金额
  –根据年月日进行分组，
  –将该查询结果，汇总一条条的插入到统计表中间
  –声明游标
  declare cur_statis Cursor
  for
  select year(ConsumeTIme) yy,MONTH(ConsumeTime) mm,DAY(ConsumeTime) dd,sum(ConsumeAmount) yye from Customers
  group by year(ConsumeTIme),MONTH(ConsumeTime),DAY(ConsumeTime)

–打开游标.
open cur_statis
–浏览数据并获取
declare @yy varchar (50)
declare @mm varchar (50)
declare @dd varchar (50)
declare @yye int
fetch next from cur_statis into @yy, @mm,@dd, @yye
while (@@FETCH_STATUS=0)
begin
insert into statis values(@yy, @mm,@dd, @yye)
fetch next from cur_statis into @yy, @mm,@dd, @yye
end
–关闭游标
close cur_statis
–释放游标
deallocate cur_statis