The stored procedure for generating time dimension in mysql (stored procedure example)

This paper mainly records the problem of generating time dimension in the process of BI and data analysis. It is also a basic example of mysql stored procedure
Including: basic syntax of stored procedure, variable definition, while loop, exception handling

The following stored procedure generates a date record of 3650 days before and after the current date
sql is as follows:

Create table:

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 'week',
  `weekofyear` int(2) NOT NULL COMMENT 'Week of the year',
  `day` int(2) NOT NULL COMMENT 'day',
  `dayofyear` int(3) NOT NULL COMMENT 'Day of the year',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Stored procedure:

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());
    -- Define exception handling  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';
    -- Exception handling completed
    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 ;

Call stored procedure

-- TRUNCATE table dim_date;
call getAllDate();

Tags: MySQL Stored Procedure

Posted on Mon, 02 Dec 2019 21:06:52 -0500 by psymonic