Created
February 10, 2016 09:24
-
-
Save yukihirai0505/5d887487ae26e2d633ce to your computer and use it in GitHub Desktop.
ストアドプロシージャで指定した日付から現在日付までのカラムをもったテーブルを作成する。
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
USE `[データベース名]`; | |
DROP PROCEDURE IF EXISTS `[ストアドプロシージャの名前]`; | |
DELIMITER $$ | |
USE `[データベース名]`$$ | |
CREATE DEFINER =`root`@`localhost` PROCEDURE `[ストアドプロシージャの名前]`(IN targetDate DATE) | |
BEGIN | |
/************************ | |
テンポラリーテーブルをDROP | |
************************/ | |
DROP TEMPORARY TABLE IF EXISTS tmp_calendar; | |
/************************ | |
指定日から現在の日付までのカラムをもつテーブルを作成する | |
************************/ | |
SET @fromDate = targetDate; | |
SET @toDate = NOW(); | |
SET @createTmp = "CREATE TEMPORARY TABLE tmp_calendar (items varchar(20)"; | |
WHILE @toDate >= @fromDate DO | |
SET @createTmp = concat(@createTmp, ",c", REPLACE(@fromDate, '-', '_'), " DATE"); | |
SET @fromDate = date_add(@fromDate, INTERVAL 1 DAY); | |
END WHILE; | |
SET @createTmp = concat(@createTmp, ")"); | |
PREPARE stmt FROM @createTmp; | |
EXECUTE stmt; | |
DEALLOCATE PREPARE stmt; | |
SELECT * | |
FROM tmp_calendar; | |
END$$ | |
DELIMITER ; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment