共计 1551 个字符，预计需要花费 4 分钟才能阅读完成。
本文主要记录在 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();
