内容:
- 存储过程格式
- 定时调用存储过程
详情:
1、存储过程格式
BEGIN
DECLARE b int DEFAULT 0;
DECLARE nowA INT;
DECLARE nowB VARCHAR(20) CHARSET utf8 DEFAULT '';
DECLARE user_cursor CURSOR FOR select ...;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET b=1;
OPEN user_cursor;
FETCH user_cursor into nowA,nowB;
WHILE b<>1 do
IF ... THEN
ELSE
END IF;
FETCH user_cursor into nowA,nowB;
END WHILE;
CLOSE user_cursor;
END
2、定时调用存储过程
create event if not exists e_eventA
on schedule at '2017-12-15 01:10:00'+ INTERVAL 1 DAY
on COMPLETION PRESERVE
do CALL ProName()
#查看event_scheduler是否打开
show VARIABLES like 'event_scheduler';
#如果在关闭状态,需要打开它
set GLOBAL event_scheduler=1;
#启动某事件任务
alert event e_eventA on COMPLETION PRESERVE ENABLE;
#关闭某事件任务
alert event e_eventA on COMPLETION PRESERVE DISABLE;
#删除某事件
drop EVENT e_eventA