mysql存储过程时间_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();