MySQL定时执行存储过程

时间:2016-05-07 14:15 来源:linux.it.net.cn 作者:IT

1,run-->cmd->cd C:\Program Files\MySQL\MySQL Server 5.5\bin

2, mysql -uXXXX -pXXXXXX

3, SHOW FULL PROCESSLIST\G

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;

(责任编辑：IT)