MySQL定時執行存儲過程

 　　1,run-->cmd->cd C:Program FilesMySQLMySQL Server 5.5bin

　　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;