使用 mysql 遊标 批量更新資料

遊标由于安全性問題，感覺用到的地方還是比較少，這裡由于測試資料需要批量更新用到了，是以就mark一下。

本次使用遊标目的是：周遊資料表，同時更新DATETIME類型的字段為某範圍内随機時間。

首先是生成範圍内随機日期的sql：

SELECT CONCAT(FLOOR(1980 + (RAND() * 35)),'-',LPAD(FLOOR(1 + (RAND() * 12)),2,0),'-',LPAD(FLOOR(3 + (RAND() * 8)),2,0));
           

然後是生成随機時間的sql：

SELECT CONCAT(LPAD(FLOOR(0 + (RAND() * 23)),2,0),':',LPAD(FLOOR(0 + (RAND() * 59)),2,0),':',LPAD(FLOOR(0 + (RAND() * 59)),2,0));
           

将日期和時間拼起來，就可以指派了，下面是存儲過程：

DELIMITER $$

DROP PROCEDURE IF EXISTS `time_cursor`;
CREATE PROCEDURE `time_cursor` ()
BEGIN
	DECLARE tcomdate,tjoindate,tquitdate,tbirthdate,tstartdate,tenddate DATETIME;
	DECLARE tempno VARCHAR(10);
	DECLARE done INT DEFAULT 0;
	DECLARE cur CURSOR FOR SELECT empno FROM t_personsales;
	
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
	
	OPEN cur;
	FETCH cur INTO tempno;
	REPEAT
		IF NOT done THEN
			SELECT CONCAT(FLOOR(1990 + (RAND() * 25)),'-',LPAD(FLOOR(1 + (RAND() * 12)),2,0),'-',LPAD(FLOOR(3 + (RAND() * 8)),2,0),' ',LPAD(FLOOR(0 + (RAND() * 23)),2,0),':',LPAD(FLOOR(0 + (RAND() * 59)),2,0),':',LPAD(FLOOR(0 + (RAND() * 59)),2,0)) INTO tcomdate;
			SELECT CONCAT(FLOOR(2000 + (RAND() * 15)),'-',LPAD(FLOOR(1 + (RAND() * 12)),2,0),'-',LPAD(FLOOR(3 + (RAND() * 8)),2,0),' ',LPAD(FLOOR(0 + (RAND() * 23)),2,0),':',LPAD(FLOOR(0 + (RAND() * 59)),2,0),':',LPAD(FLOOR(0 + (RAND() * 59)),2,0)) INTO tjoindate;
			SELECT CONCAT(FLOOR(2010 + (RAND() * 6)),'-',LPAD(FLOOR(1 + (RAND() * 12)),2,0),'-',LPAD(FLOOR(3 + (RAND() * 8)),2,0),' ',LPAD(FLOOR(0 + (RAND() * 23)),2,0),':',LPAD(FLOOR(0 + (RAND() * 59)),2,0),':',LPAD(FLOOR(0 + (RAND() * 59)),2,0)) INTO tquitdate;
			SELECT CONCAT(FLOOR(1960 + (RAND() * 40)),'-',LPAD(FLOOR(1 + (RAND() * 12)),2,0),'-',LPAD(FLOOR(3 + (RAND() * 8)),2,0),' ',LPAD(FLOOR(0 + (RAND() * 23)),2,0),':',LPAD(FLOOR(0 + (RAND() * 59)),2,0),':',LPAD(FLOOR(0 + (RAND() * 59)),2,0)) INTO tbirthdate;
			SELECT CONCAT(FLOOR(1999 + (RAND() * 10)),'-',LPAD(FLOOR(1 + (RAND() * 12)),2,0),'-',LPAD(FLOOR(3 + (RAND() * 8)),2,0),' ',LPAD(FLOOR(0 + (RAND() * 23)),2,0),':',LPAD(FLOOR(0 + (RAND() * 59)),2,0),':',LPAD(FLOOR(0 + (RAND() * 59)),2,0)) INTO tstartdate;
			SELECT CONCAT(FLOOR(1999 + (RAND() * 15)),'-',LPAD(FLOOR(1 + (RAND() * 12)),2,0),'-',LPAD(FLOOR(3 + (RAND() * 8)),2,0),' ',LPAD(FLOOR(0 + (RAND() * 23)),2,0),':',LPAD(FLOOR(0 + (RAND() * 59)),2,0),':',LPAD(FLOOR(0 + (RAND() * 59)),2,0)) INTO tenddate;
			UPDATE t_personsales SET COMDATE = tcomdate,JOINDATE = tjoindate,QUITDATE = tquitdate,BIRTHDATE = tbirthdate,CHK_START_DATE = tstartdate, CHK_END_DATE = tenddate where empno = tempno;
		END IF;
	FETCH cur INTO tempno;
	UNTIL done = 1
	END REPEAT;
	CLOSE cur;
END
           

然後 call time_cursor(); 大功告成。