DROP PROCEDURE IF EXISTS proc_init_townsman_vacancy_rand;--如果存在此存储过程则删掉
DELIMITER $$
CREATE DEFINER=`root`@`%` PROCEDURE `proc_init_townsman_vacancy_rand`()
BEGIN
DECLARE d INT DEFAULT 1;
DECLARE daysum DECIMAL(18,2) DEFAULT 5000;
DECLARE daytime date DEFAULT DATE_FORMAT(DATE_SUB(NOW(), INTERVAL d MONTH), '%Y-%m-1');
WHILE d <=65 DO
set daytime = DATE_FORMAT(DATE_SUB(NOW(), INTERVAL d Month), '%Y-%m-01');
INSERT INTO `townsman_vacancy`(`VACANCY _COUNT`,`ALL_COUNT`,`COUNTY_CODE`,`STREET_CODE`,`DATA_DATE`)
SELECT FLOOR(daysum*(RAND()*0.1)) AS VANCANY,FLOOR(daysum*( 1.2 - RAND()*0.4)) AS ALL_COUNT,COUNTY_CODE,STREET_CODE,daytime FROM STREET;
set d = d + 1 ;
END WHILE;
END$$
DELIMITER ;
CALL proc_init_townsman_vacancy_rand();
这里有几个函数:FLOOR() , RAND() 。
关键词: DELIMITER
语法:DROP PROCEDURE IF EXISTS