MySQL timed execution of stored procedures

1,RUN-->CMD->CD C:Program filesmysqlmysql Server 5.5bin

2, Mysql-uxxxx-pxxxxxx

3, show full PROCESSLISTG

4, set Sheduler

SET GLOBAL event_scheduler = on;

SET @ @global. Event_scheduler = on;

SET GLOBAL event_scheduler = 1;

SET @ @global. Event_scheduler = 1;

Similarly, any of these 4 statements can is used to turn off the Event Scheduler:

SET GLOBAL event_scheduler = off;

SET @ @global. Event_scheduler = off;

SET GLOBAL event_scheduler = 0;

SET @ @global. Event_scheduler = 0;

5,create procedure

-- --------------------------------------------------------------------------------

--Routine DDL

--note:comments before and the routine body won't be stored by the server

-- --------------------------------------------------------------------------------

DELIMITER $$

CREATE definer= ' root ' @ ' localhost ' PROCEDURE ' get_info_every_day ' ()

BEGIN

Declare Pintsumtotalaction int;

Declare Pintsumnoduedate int;

Declare pintsumclosed int;

Declare Pintsumforinfo int;

Declare Pintsumoverdue int;

Declare Pintsumtbdin1week int;

Declare Pintsumtbdafter1week int;

Declare PINTSUMPENDINGJPMO int;

Declare Pintsumeps int;

Declare PINTSUMWCI int;

Declare pintsumontimeclosed int;

Declare Pinttotal int; # #统计的时候所有的action Items

Declare strstatus varchar (40);

Declare dduedate datetime;

Declare dcloseddate datetime;

Declare Noverdue int;

Declare Ncountontime int; # #nIsOnTime COUNT (*) quantity

Declare Fetchseqok boolean; # # define the flag for loop judgement

/*

Declare my_cursor cursor FOR select B.status,b.duedate,b.closedate,datediff (now (), b.duedate) as overdue,

B.fk_actionitem from Actionitem A,actionitemdetail b where A.id_actionitem=b.fk_actionitem and a.finishdate=0

and status<> ' Forinfo ' and (Actionby like '%wec% ' or actionby-like '%consortium% ');

*/

Declare my_cursor cursor FOR select B.status,b.duedate,b.closedate,datediff (now (), b.duedate) as overdue

From Actionitem A,actionitemdetail b where A.id_actionitem=b.fk_actionitem and a.finishdate=0

and status<> ' Forinfo ' and (Actionby like '%wec% ' or actionby-like '%consortium% ');

Declare my_cursor2 cursor FOR select CAST (COUNT (*) as UNSIGNED) as Lnontimeclosedai from Actionitemdetail

Where DateDiff (now (), DueDate) <=7 and DateDiff (now (), DueDate) >=0

and (Actionby like '%wec% ' or Actionby like '%consortium% ')

and status= ' Closed ' and DateDiff (closedate,duedate) <0 order by DueDate;

Declare continue handler for not found set Fetchseqok = true;

Set pintsumtotalaction=0;

Set pintsumnoduedate=0;

Set pintsumclosed=0;

Set pintsumforinfo=0;

Set pintsumoverdue=0;

Set pintsumtbdin1week=0;

Set pintsumtbdafter1week=0;

Set pintsumpendingjpmo=0;

Set pintsumeps=0;

Set pintsumwci=0;

Set Fetchseqok = false;

/*

Declare continue handler for not FOUND set fetchseqok = true;

#define The continue handler for not found flag

Set Fetchseqok = false;

Open fetchseqcursor;

Fetchseqloop:loop

Fetch fetchseqcursor into _seqname, _value;

If Fetchseqok Then

Leave Fetchseqloop;

Else

Select _seqname, _value;

End If;

End Loop;

Close fetchseqcursor;

*/

Open my_cursor;

Fetchloop:loop

Fetch my_cursor into strstatus,dduedate,dcloseddate,noverdue;

If Fetchseqok Then

Leave Fetchloop;

Else

If LOWER (strstatus) = ' open ' Then

Case Noverdue

When IsNull (noverdue) then set pintsumnoduedate=pintsumnoduedate+1;

When noverdue>0 then set pintsumoverdue=pintsumoverdue+1;

When Noverdue<=0 and noverdue>-7 then set pintsumtbdin1week=pintsumtbdin1week+1;

else set pintsumtbdafter1week=pintsumtbdafter1week+1;

End case;

Else

Case LOWER (strstatus)

When ' closed ' then set pintsumclosed=pintsumclosed+1;

When ' Forinfo ' then set pintsumforinfo=pintsumforinfo+1;

When ' pending Jpmo ' then set pintsumpendingjpmo=pintsumpendingjpmo+1;

When ' escalated to the PCC for support ' then set pintsumeps=pintsumeps+1;

When ' Waiting for customer input ' then set pintsumwci=pintsumwci+1;

End case;

End If;

End If;

End LOOP;

Close my_cursor;

Set pinttotal=pintsumtbdafter1week+pintsumoverdue+pintsumtbdin1week+

pintsumnoduedate+pintsumpendingjpmo+pintsumeps+pintsumwci+pintsumclosed;

/*** statistics forward 7 days from the current date committed closed situation

Ncountontime represents count of on time closed number

*/

Set Fetchseqok = false;

Open My_cursor2;

My_loop:loop

Fetch My_cursor2 into ncountontime;

If Fetchseqok Then

Leave My_loop;

Else

Set Pintsumontimeclosed=ncountontime;

End If;

End Loop;

Close My_cursor2;

Insert into MyTest (testdate) value (now ());

Insert into daily_statistic (total,open,overdue,duewithin7days,ptp,noduedate,pendingjpmo,eps,wci,closed)

VALUES (Pinttotal,pintsumtbdafter1week,pintsumoverdue,pintsumtbdin1week,

Pintsumontimeclosed,pintsumnoduedate,

pintsumpendingjpmo,pintsumeps,pintsumwci,pintsumclosed);

/*

Insert into daily_statistic (total,open,overdue,duewithin7days,ptp,noduedate,pendingjpmo,eps,wci,closed) values

(Pinttotal,pintsumtbdafter1week,pintsumoverdue,pintsumtbdin1week,10,pintsumnoduedate,

pintsumpendingjpmo,pintsumeps,pintsumwci,pintsumclosed);

*/

End

6,create Event

Use CDDL;

DROP EVENT IF EXISTS e_statistics_daily;

CREATE EVENT e_statistics_daily

On SCHEDULE EVERY 1 day

Starts ' 2013-10-18 16:45:00 '

On completion Preserve

Do call Get_info_every_day ();

7, testing whether it is has the value or not

SELECT * from Daily_statistic;