欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

MySQL定时执行存储过程

程序员文章站 2022-06-03 08:50:01
...

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_

  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;