使用游标对查询结果循环

前段时间给了我一个任务,写一个存储过程.表里面有每个工作日的期货价格数据,有最高价,最低价,平均价,现在需要根据年份查询出历史数据,另外需要计算出周最高价,周最低价和周平均价,我在存储过程里面使用了游标,感觉用在存储过程里面,性能也不错,速度很快.

 

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[GetProductPriceFromYear]

       @ProductID int,

       @Date Datetime

AS

BEGIN

       create table #temp1

(

       id int IDENTITY(1,1) NOT NULL primary key,

       AddDate varchar(20),

       [datename] varchar(20),

       week varchar(20),

       ProductName varchar(20),

       ProductID varchar(20),

       LPrice float(20),

       HPrice float(20),

       APrice float(20),

       WeekLPrice float(20),

       WeekHPrice float(20),

       WeekAPrice float(20),

       WeekDayCount int

)

 

declare @tempDate varchar(20)

declare @AddDate varchar(20)

declare @datenam varchar(20)

declare @week varchar(20)

declare @ProductName varchar(20)

declare @Product varchar(20)

declare @LPrice varchar(20)

declare @HPrice varchar(20)

declare @APrice varchar(20)

Declare   CurPrice   Cursor   For

              select distinct AddDate=CONVERT(char(10),a.AddDate,20),datename(dw,a.AddDate) as [datename],

datepart(ww,a.AddDate) as week, b.ProductName,a.ProductID,a.LPrice,a.HPrice,a.APrice

from dbo.SmsPrice as a join dbo.SmsProduct as b

on a.ProductID = b.ProductId where a.ProductID = @ProductID AND Datediff(yy,a.AddDate,@Date)=0 order by AddDate desc

Open   CurPrice    

Fetch  from CurPrice into @AddDate,@datenam,@week,@ProductName,@Product,@LPrice,@HPrice,@APrice

insert into #temp1(AddDate,[datename],week,ProductName,ProductID,LPrice,HPrice,APrice)

                     values(@AddDate,@datenam,@week,@ProductName,@Product,@LPrice,@HPrice,@APrice)

 

While   @@FETCH_STATUS=0  

    BEGIN

             

              if(@tempDate!=@AddDate)

                     insert into #temp1(AddDate,[datename],week,ProductName,ProductID,LPrice,HPrice,APrice)

                     values(@AddDate,@datenam,@week,@ProductName,@Product,@LPrice,@HPrice,@APrice)

              set @tempDate = @AddDate

              Fetch  from CurPrice into @AddDate,@datenam,@week,@ProductName,@Product,@LPrice,@HPrice,@APrice

       end

  Close   CurPrice

  Deallocate   CurPrice

 

--insert into #temp1(AddDate,[datename],week,ProductName,ProductID,LPrice,HPrice,APrice)

--select distinct AddDate=CONVERT(char(10),a.AddDate,20),datename(dw,a.AddDate) as [datename],

--datepart(ww,a.AddDate) as week, b.ProductName,a.ProductID,a.LPrice,a.HPrice,a.APrice

--from dbo.Price as a join dbo.SmsProduct as b

--on a.ProductID = b.ProductId where a.ProductID = @ProductID AND Datediff(yy,a.AddDate,@Date)=0 order by AddDate desc

declare @weekid varchar(20)

declare @tempWeek2 varchar(20)

declare @WeekLPrice float(20)

declare @WeekHPrice float(20)

declare @WeekAPrice float(20)

 

Declare   Cur   Cursor   For

              select distinct week from #temp1

Open   Cur    

  Fetch   Cur   Into   @weekid

While   @@FETCH_STATUS=0  

        BEGIN

                     select @tempWeek2=week,@WeekLPrice=avg(CONVERT(float,LPrice)),@WeekHPrice=avg(CONVERT(float,HPrice)),

                            @WeekAPrice=avg(CONVERT(float,APrice)) from #temp1 where week =@weekid group by week

                     update #temp1 set WeekLPrice = @WeekLPrice,WeekHPrice=@WeekHPrice,WeekAPrice=@WeekAPrice,

                            WeekDayCount=(select count(*) from #temp1 where week = @weekid) where week = @weekid

                     Fetch   Cur   Into   @weekid

              end

  Close   Cur  

  Deallocate   cur

select * from #temp1

END