MySQL のストアドプロシージャで日付ディメンションテーブルをつくる

--
-- 日付ディメンションテーブル
--
drop table if exists dates;
 
create table dates (
 id    integer primary key auto_increment,
 date  date,
 year  integer,
 month integer,
 day   integer,
 week  integer,
 index date(date),
 index year(year),
 index month(month),
 index day(day),
 index week(day)
);

drop procedure if exists update_dates_table;
 
delimiter //
create procedure update_dates_table()
begin
 declare currentdate date default '2008-12-01';
 truncate dates;
 while currentdate <= curdate() do
   insert into dates(date, year, month, day, week)
   values(
     currentdate,
     year(currentdate),
     month(currentdate),
     day(currentdate),
     week(currentdate, 0)
   );
   set currentdate = date_add(currentdate, interval 1 day);
 end while;
end
//
delimiter ;