mysql创建一个存储过程并创建定时任务

1、创建存储过程

SQL预览

DROP PROCEDURE IF EXISTS `sp_evaluation_point`;

CREATE DEFINER = `root`@`%` PROCEDURE `sp_evaluation_point`()
BEGIN
#Routine body goes here...
#定义参数
DECLARE
yesterday date;

DECLARE
_day VARCHAR (50);

DECLARE
point INT (11);

DECLARE
begin_date VARCHAR (50);

DECLARE
end_date VARCHAR (50);

#设置值
SET yesterday = CURDATE() - 1;

SET begin_date = CONCAT(yesterday, ' 00:00:00');

SET end_date = CONCAT(yesterday, ' 23:59:59');


#数据插入到数据库
INSERT INTO st_evaluation_point (
_day,
user_id,
head_img,
dimension_id,
class_id,
grade_id,
point
) SELECT
DATE_FORMAT(
table1.evaluate_time,
'%Y-%m-%d'
),
table1.evaluated_user_id,
table2.path,
table6.dimension_id,
table4.classroom_id,
table5.grade_number,
SUM(table1.cert)
FROM
web_stu_evaluate table1
LEFT JOIN web_sch_image table2 ON table2.obj_id = table1.evaluated_user_id
AND table2.obj_type = '03'
LEFT JOIN web_sch_student table3 ON table3.user_id = table1.evaluated_user_id
LEFT JOIN web_student_classroom table4 ON table4.student_id = table3.id
LEFT JOIN web_sch_classroom table5 ON table5.id = table4.classroom_id
LEFT JOIN web_sch_quota table6 ON table6.id = table1.quota_id
WHERE
table1.evaluate_time >= begin_date
AND table1.evaluate_time <= end_date
GROUP BY
table1.evaluated_user_id;

END;

2、创建每隔一天运行存储过程的定时任务

CREATE DEFINER=`root`@`%` 
EVENT `NewEvent`
ON SCHEDULE EVERY 1 DAY STARTS '2016-11-10 00:00:00'
ON COMPLETION PRESERVE
ENABLE
DO
call sp_evaluation_point();