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())、计算两个日期之间的天数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;