mysql中生成时间维度的存储过程（存储过程示例）

本文主要记录在BI和数据分析过程中碰到的生成时间维度的问题，另外也是一个mysql的存储过程基础示例包含：存储过程基本语法、变量定义、while循环、异常处理以下存储过程生成了以当前日期为基准前后3650天的日期记录sql如下：创建表：CREATE TABLE dim_date ( id int(8) NOT NULL DEFAULT ‘0’, key date NOT NULL DEFAULT ‘0000-00-00’, year int(4) NOT NULL, quarter int(1) NOT NULL, month int(2) NOT NULL, week int(1) NOT NULL COMMENT ‘星期’, weekofyear int(2) NOT NULL COMMENT ‘一年中的第几周’, day int(2) NOT NULL COMMENT ‘日’, dayofyear int(3) NOT NULL COMMENT ‘一年总的第几天’, PRIMARY KEY (id)) ENGINE=InnoDB DEFAULT CHARSET=utf8;存储过程：delimiter //DROP PROCEDURE IF EXISTS getAllDate; CREATE PROCEDURE getAllDate()BEGIN DECLARE count int default 0; DECLARE startDay DATE DEFAULT date(now()); DECLARE endDay DATE DEFAULT DATE(NOW()); – 定义异常处理方式 http://www.cnblogs.com/cookiehu/p/4994278.html DECLARE out_status VARCHAR(200) DEFAULT ‘OK’; DECLARE CONTINUE HANDLER FOR 1062 SET out_status=‘Duplicate Entry’; – 异常处理方式完毕 WHILE count<3650 DO INSERT INTO dim_date(id, key, year, quarter, month, week, weekofyear, day, dayofyear) VALUES (cast(DATE_FORMAT(startDay,’%Y%m%d’) as UNSIGNED), startDay, YEAR(startDay), QUARTER(startDay), MONTH(startDay), WEEKDAY(startDay)+1, week(startDay,1), DAY(startDay), DAYOFYEAR(startDay)); set count = count +1; set startDay = DATE_ADD(DATE(now()),INTERVAL count DAY); SET endDay = DATE_SUB(DATE(NOW()),INTERVAL count DAY); INSERT INTO dim_date(id, key, year, quarter, month, week, weekofyear, day, dayofyear) VALUES (cast(DATE_FORMAT(endDay,’%Y%m%d’) as UNSIGNED), endDay, YEAR(endDay), QUARTER(endDay), MONTH(endDay), WEEKDAY(endDay)+1, week(endDay,1), DAY(endDay), DAYOFYEAR(endDay)); END WHILE;END//delimiter ;调用存储过程– TRUNCATE table dim_date;call getAllDate();