MySQL定时执行存储过程

2, mysql -uXXXX -pXXXXXX

3, SHOW FULL PROCESSLISTG

4,设置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 be 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 after the routine body will not 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(*)数量

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 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;

/*** 统计从当前日期向前推7天的committed closed情况

nCountOnTime 表示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 having the value or not

select * from daily_statistic;