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)
);

<pre class="prettyprint">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 ;
</pre>