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)=0
and (actionby like '%wec%' or actionby like '%consortium%' )
and status='closed' and datediff(closedate,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-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;
，