1. Creating a stored procedure with a cursor
SQL creation:
Create PROCEDURE daily_max_avg_online () begindeclare max_online int;declare avg_online int;declare time TIMESTAMP; Declare cur cursor FOR select MAX (Online_num), round (avg (online_num)), Date_format (Statistical_time, '%y-%m-%d ') from Real_time_online_statistics GROUP BY Date_format (Statistical_time, '%y-%m-%d '); open Cur;cur:loopfetch cur into max_ Online,avg_online,time;insert into Daily_max_avg_online_statistics (Id,max_online_num,avg_online_num,statistical_ Time) VALUES (UUID (), max_online,avg_online,time); End Loop Cur;close cur; END;
NAVICAT Create:
1. Click Functions in the database--new function
2. Click Finish to enter the interface to create the stored procedure
3. Save (Ctrl+s)--> enter stored procedure name--Complete the creation of the stored procedure
2. Create a timer call stored procedure
1. Click Events--New events--Enter the interface--fill in the stored procedures that need to be called periodically
2. Set the timer task--set here once a day, start time is 2014-12-04 15:08:00
3. The constant global event_scheduler must be on or 1 for the timer to work .--Check whether the timer is turned onSHOW VARIABLES like '%event_scheduler% '; --Open timer 0:off 1:onSET GLOBAL event_scheduler = 1; or SET GLOBAL event_scheduler = on; Over
Stored procedure for calling MySQL using timers--navicat client