Projecto: Exemplo de script de população de dimensões estáticas

13 dezembro 2016, 19:20 • Paulo Carreira

delimiter //
CREATE PROCEDURE load_date_dim()
BEGIN
   DECLARE v_full_date DATETIME;
   SET v_full_date = '2016-01-01 00:00:00';
   WHILE v_full_date < '2018-01-01 00:00:00' DO
       INSERT INTO bd1617_date_dimension(
          date_key,
          date_year,
       date_month_number,
       date_day
       ) VALUES (
           YEAR(v_full_date) * 10000 + MONTH(v_full_date)*100 + DAY(v_full_date),
           YEAR(v_full_date),
           MONTH(v_full_date),
           DAY(v_full_date)
       );
       SET v_full_date = DATE_ADD(v_full_date, INTERVAL 1 DAY);
   END WHILE;
END;
//