Mysql 存储过程

无参数存储过程

DROP PROCEDURE IF EXISTS  sta_base_update;
CREATE PROCEDURE sta_base_update()
BEGIN

DECLARE done INT DEFAULT 0;
DECLARE areaId CHAR(6);
.......

END ;

根据List 循环执行

DROP PROCEDURE IF EXISTS  sta_base_update;
CREATE PROCEDURE sta_base_update()
BEGIN

DECLARE done INT DEFAULT 0;
DECLARE areaId CHAR(6);
DECLARE areaParamId CHAR(6);

-- 游标
DECLARE list CURSOR FOR SELECT area_id FROM sta_area ;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

SET done = 0;
OPEN list;
REPEAT
FETCH NEXT FROM list INTO areaId;
IF NOT done THEN

SET areaParamId = IF(areaId='150000','15',IF(right(areaId, 2)='00',left(areaId, 4),areaId));
......

END IF;
UNTIL done END REPEAT;
CLOSE list;

END ;

创建定时器执行存储过程

-- 确认定时器是否开启，30分钟执行一次；
select @@event_scheduler;
SET GLOBAL event_scheduler=ON;

DROP EVENT IF EXISTS event_sta_update;
CREATE EVENT event_sta_update
 ON SCHEDULE  EVERY 30 MINUTE
ON COMPLETION PRESERVE 
DO
call sta_update();