MySQLストアド・プロシージャ--製造日時の偽データを一括作成(メモ)

select database();
DROP PROCEDURE IF EXISTS updateTitleTime;
DELIMITER $$
USE photographic $$
CREATE PROCEDURE updateTitleTime (IN sourceStartDate VARCHAR(20), IN sourceEndDate VARCHAR(20), IN targetStartDate VARCHAR(20), IN targetEndDate VARCHAR(20))
BEGIN
	DECLARE s_startDate DATETIME;
	DECLARE s_endDate DATETIME;
	DECLARE t_startDate DATETIME;
	DECLARE t_endDate DATETIME;
	DECLARE t_titleId VARCHAR(30);
	DECLARE t_sortId VARCHAR(30);
	DECLARE t_sortDetailId VARCHAR(30);
	DECLARE t_titleStartTime DATETIME;
	DECLARE t_replyTime DATETIME;
	DECLARE totalDays INT;
	DECLARE indexDay INT;
	DECLARE t_date VARCHAR(10);
	DECLARE t_time VARCHAR(8);
	DECLARE n_titleStartTime DATETIME;
	DECLARE title_done INT DEFAULT 0;
	DECLARE titleCursor CURSOR FOR select titleId,sortId,sortDetailId,titleStartTime from ebbbstitle where titleStartTime>=s_startDate and titleStartTime<=s_endDate;
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET title_done=1;
	SELECT str_to_date(sourceStartDate,'%Y-%m-%d %H:%i:%s') into s_startDate;
	SELECT str_to_date(sourceEndDate,'%Y-%m-%d %H:%i:%s') into s_endDate;
	SELECT str_to_date(targetStartDate,'%Y-%m-%d %H:%i:%s') into t_startDate;
	SELECT str_to_date(targetEndDate,'%Y-%m-%d %H:%i:%s') into t_endDate;
	select to_days(t_endDate)-to_days(t_startDate) into totalDays;
	OPEN titleCursor;
	REPEAT
        FETCH titleCursor INTO t_titleId,t_sortId,t_sortDetailId,t_titleStartTime;
        IF NOT title_done THEN
       		SELECT FLOOR(0 + RAND() * totalDays) into indexDay;
        	select DATE_FORMAT(DATE_ADD(t_startDate,INTERVAL indexDay DAY), '%Y-%m-%d') into t_date;
        	select DATE_FORMAT(t_titleStartTime,'%H:%i:%s') into t_time;
        	select STR_TO_DATE(CONCAT(t_date,' ',t_time), '%Y-%m-%d %H:%i:%s') into n_titleStartTime;
        	update ebbbstitle set titleStartTime=n_titleStartTime where titleId=t_titleId and sortId=t_sortId and sortDetailId=t_sortDetailId;
        	BEGIN
	        DECLARE r_replyNo VARCHAR(30);
	        DECLARE r_titleId VARCHAR(30);
	        DECLARE r_replyTime DATETIME;
	        DECLARE n_replyTime DATETIME;
	        DECLARE r_time VARCHAR(8);
	        DECLARE totalSeconds INT;
	        DECLARE indexSecond INT DEFAULT 1;
	        DECLARE reply_done INT DEFAULT 0;
        	DECLARE replyCursor CURSOR FOR select replyNo,titleId,replyTime from ebbbsreply where titleId=t_titleId;
			DECLARE CONTINUE HANDLER FOR NOT FOUND SET reply_done=1;
			SELECT TIMESTAMPDIFF(SECOND,n_titleStartTime,t_endDate) INTO totalSeconds;
        	OPEN replyCursor;
				REPEAT
					FETCH replyCursor INTO r_replyNo,r_titleId,r_replyTime;
					IF NOT reply_done THEN
						SELECT FLOOR(1 + RAND() * (totalSeconds-2)) into indexSecond;
						SELECT DATE_ADD(n_titleStartTime, INTERVAL indexSecond SECOND) INTO n_replyTime;
						update ebbbsreply set replyTime=n_replyTime where replyNo=r_replyNo and titleId=r_titleId;
					END IF;
				UNTIL reply_done END REPEAT;
			CLOSE replyCursor;
	        END;
	        BEGIN
		    DECLARE t_lastReplyTime DATETIME;
		    DECLARE reply_done INT DEFAULT 0;
			DECLARE replyCursor CURSOR FOR select replyTime from ebbbsreply where titleId=t_titleId order by replyTime desc limit 0,1;
			DECLARE CONTINUE HANDLER FOR NOT FOUND SET reply_done=1; 
			OPEN replyCursor;
				REPEAT
					FETCH replyCursor INTO t_lastReplyTime;
					IF NOT reply_done THEN
        				update ebbbstitle set lastReplyTime=t_lastReplyTime where titleId=t_titleId and sortId=t_sortId and sortDetailId=t_sortDetailId;
					END IF;
				UNTIL reply_done END REPEAT;
			CLOSE replyCursor;
		    END;
        END IF;
    UNTIL title_done END REPEAT;
	CLOSE titleCursor;
	COMMIT;
END $$
DELIMITER ;

このストレージ・プロシージャは、データベース初心者にとって次のような知識点に関連しています.
乱数(foolr()、rand()、計算2つの日付間の日数to_days(endDate)-to_days(startDate)、ストレージ時の値付け方法(select into)、カーソル遍歴クエリ結果、宣言変数DECLARE、日付関数(str_to_date、date_format、date_add、timestampdiff)
カーソルは次の文を繰り返します.

DECLARE CONTINUE HANDLER FOR NOT FOUND SET title_done=1;

カーソル変数を宣言するときのwhere条件の値は、先に値を割り当てる必要はありませんが、カーソルを開く前に値を割り当てる必要があります.

DECLARE titleCursor CURSOR FOR select titleId,sortId,sortDetailId,titleStartTime from ebbbstitle where titleStartTime>=s_startDate and titleStartTime<=s_endDate;