задача 9

[iriyawin]

Задача 9
См. задачу 4. Напишите MySQL-процедуру ​ getAvgRate()​ , которая принимает id_валюты, диапазон дат и выводящих результат какой средний курс был в этом диапазоне дат.
Примечание: обязательно используйте работу с MySQL курсором и обработчиком события в MySQL.

[iriyawin]

mssql: Create proc getAvgRate (@curr_id int, @begDate datetime, @endDate datetime) as begin if (object_id('#tmp') is not null) drop table #tmp

create table #tmp (
   thedate datetime not null
   ,rate   money   not null
) 
insert into #tmp (thedate, rate)
select convert(varchar,DT1,104) as DT
  ,(select top 1 rate --select *
    from rates r1
    where (r1.date_rate = (select max(r2.date_rate) from rates r2 where (r2.date_rate <= DT1)))) as rate
from (
    select column_id, dateadd(d,column_id-1,convert(datetime,@begDate,120)) as DT1 --select top 100 *
    from sys.columns
    where (column_id > 0) and (column_id <= datediff(d,convert(datetime,@begDate,120),convert(datetime,@enddate,120))+1)
    group by column_id, dateadd(d,column_id-1,convert(datetime,@begDate,120))
) a 
order by a.column_id

declare @sum money, @cnt int, @dt datetime, @rate money
declare a cursor for select thedate, rate from #tmp
select @sum = 0, @cnt = 0
open a
fetch next from a into @dt, @rate
while (@@fetch_status = 0)
begin
    select @sum = @sum + @rate, @cnt = @cnt + 1
    fetch next from a into @dt, @rate
end
close a
deallocate a
if (@cnt = 0)
    select 0
else
    select @sum/@cnt
return

end

exec getAvgRate 1,'20130109','20130131'

[iriyawin]

на mysql пробовал - но с курсорами не получилось работающего решения. скелет того, что получилось create PROCEDURE getAvgRate(thecurr_id int, begdate date, enddate date) AS begin DECLARE thedate date DECLARE therate decimal(8,2) declare prevdate date

declare a CURSOR FOR SELECT date_rate, rate FROM rates Where (curr_id = thecurr_id) and (date_rate < enddate) order by date_rate

declare continue HANDLER for not FOUND_ROWS set done = true open a read_loop: LOOP fetch next from a into thedate, therate if done THEN Leave LOOP end IF if (thedate < begdate) THEN prevrate = therate else (thedate = begdate) THEN

endif end LOOP close a end