游標語法
declare c1 cursor for?
select title from titles
--定義一個游標c1，確定游標對應的列是titles表的title列，游標可以對應多個列
declare @bname varchar(50)
--聲明變量
open c1
--初始化，開始使用游標
fetch next from c1
into @bname
--初始化游標c1到i=0，即起始位置，把游標對應列的值放入變量中，用作輸出
while @@FETCH_STATUS=0
--判斷游標狀態，是否到最后一行
begin
?print 'the name of the book is '+@bname
--對每行做數據做相應操作
fetch next from c1
into @bname
--完成操作后，游標后移，再次把游標對應行的該列值放入變量，重復直到游標到最后一列
end
close c1 --關閉游標
deallocate c1 --銷毀游標
practice
first：多個變量，聚合函數輸出，返回多個字符串
declare b cursor for
select title_id from titleauthor
declare @titleid1 varchar(50),@sum1 int,@sum2 int,@sum3 int
open b
fetch next from b
into @titleid1
while @@FETCH_STATUS=0
begin
set @sum1=(select count(*) from sales where title_id=@titleid1 and ord_date between '1992-1-1' and '1992-12-30')
set @sum2=(select count(*) from sales where title_id=@titleid1 and ord_date between '1993-1-1' and '1993-12-30')
set @sum3=(select count(*) from sales where title_id=@titleid1 and ord_date between '1994-1-1' and '1994-12-30')
?print @titleid1+' '+cast(@sum1 as varchar(10))+' '+cast(@sum2 as varchar(10))+' '+cast(@sum3 as varchar(10))
fetch next from b
into @titleid1?
end
close b
deallocate b
second：多個變量，返回表
declare c cursor for
select title_id from titleauthor
declare @titleid2 varchar(50),@asum1 int,@asum2 int,@asum3 int
create table #temptable(
titleid varchar(100),
count92 int,
count93 int,
count94 int)
open c
fetch next from c
into @titleid2
while @@FETCH_STATUS=0
begin
set @asum1=(select count(*) from sales where title_id=@titleid2 and ord_date between '1992-1-1' and '1992-12-30')
set @asum2=(select count(*) from sales where title_id=@titleid2 and ord_date between '1993-1-1' and '1993-12-30')
set @asum3=(select count(*) from sales where title_id=@titleid2 and ord_date between '1994-1-1' and '1994-12-30')
insert into #temptable values(@titleid2,@asum1,@asum2,@asum3)
fetch next from c into @titleid2
end
close c
deallocate c
select * from #temptable
drop table #temptable
