笔记：存储过程-定时器

存储过程-定时器

存储过程：

  CREATE PROCEDURE sp_name ([proc_parameter[,...]]) 

           [characteristic ...] routine_body

       参数：[ IN | OUT | INOUT ] param_name type

定时器/事件：

       CREATEEVENT [IFNOT EXISTS] event_name

      　　 ON SCHEDULE schedule

      　　 [ONCOMPLETION [NOT] PRESERVE]

      　　 [ENABLE | DISABLE]

      　　 [COMMENT 'comment']

      　　 DO sql_statement;

       事件schedule取值：

       schedule:

　　 ATTIMESTAMP [+ INTERVAL INTERVAL]

　　 | EVERYINTERVAL [STARTS TIMESTAMP] [ENDS TIMESTAMP]

　　schedule中interval取值：

　　INTERVAL:

　　 quantity{YEAR | QUARTER | MONTH | DAY | HOUR | MINUTE |

　　 WEEK |SECOND | YEAR_MONTH | DAY_HOUR | DAY_MINUTE |

　　 DAY_SECOND| HOUR_MINUTE | HOUR_SECOND | MINUTE_SECOND}

 

存储过程例：

BEGIN

DECLARE D_COUNTINT DEFAULT-1;

DECLARE _STOPINT DEFAULT0;

DECLARE VAIDINT;

DECLARE UIDINT(11);

DECLARE UEMAILVARCHAR(100);

DECLARE UCELLVARCHAR(20);

DECLARE UVINVARCHAR(50);

DECLARE UCARVARCHAR(20);

DECLARE DMS_CAR_CODE_SUBVARCHAR(30);

DECLARE DMS_CAR_CODEVARCHAR(30);

DECLARE DMS_C_25VARCHAR(30);

DECLARE DMS_C_27VARCHAR(50);

DECLARE DMS_C_11VARCHAR(50);

DECLARE DMS_C_23VARCHAR(30);

DECLARE DMS_C_68VARCHAR(30);

DECLARE DMS_D_IDVARCHAR(50);

DECLARE DMS_DEALER_IDVARCHAR(20);

DECLARE DMS_PROVINCE_IDVARCHAR(20);

DECLARE DMS_CITY_IDVARCHAR(20);

DECLARE DMS_DEALER_NAMEVARCHAR(100);

DECLARE DMS_CAR_MODELVARCHAR(30);

DECLARE DMS_CARD_IDVARCHAR(60);

DECLARE NEW_CAROWNER_IDINT(11);

DECLARE CURCURSORFORSELECT A_ID,USER_ID,EMAIL,USER_CELL,VIN_CODE,CAR_CODEFROM E_APPDMS;

/**包含游标NOT FOUND，和执行异常，handler是作为事件句柄*/

DECLAREEXIT HANDLERFORSQLSTATE'02000'

    BEGIN

      SET _STOP=1;

      SELECT'游标正常结束!';

   END;

 

   DECLAREEXIT HANDLERFORSQLEXCEPTION

   BEGIN

       SET _STOP=1;

       SELECTCONCAT('SQLEXCEPTION-TABLE[E_APPDMS]-NEW_CAROWNER_ID:',NEW_CAROWNER_ID);

   END;

OPEN CUR;

FETCH CURINTO VAID,UID,UEMAIL,UCELL,UVIN,UCAR;

 

WHILE _STOP<>1 DO

  SELECTCOUNT(1)INTO D_COUNT FROM E_DMS_SALESDATA WHERE C_7 = UVINAND IS_ACTIVITE =0AND IS_AUTH=0;

  IF D_COUNT>0THEN

   SELECTCOUNT(1)INTO D_COUNT FROM E_CAROWNER WHERE VIN_CODE = UVIN;

  

   IF (D_COUNT<=0)THEN

     SELECT CAR_CODE_SUB, CAR_CODE ,C_25, C_68, C_27, C_11,C_23,D_ID ,CARD_ID

          INTO DMS_CAR_CODE, DMS_CAR_CODE_SUB, DMS_C_25,DMS_C_68,DMS_C_27, DMS_C_11,DMS_C_23,DMS_D_ID,DMS_CARD_ID

          FROM E_DMS_SALESDATA

          WHERE C_7= UVIN AND IS_ACTIVITE=0AND IS_AUTH=0;

     SELECT DEALER_ID,PROVINCE_ID,CITY_ID,CN_NAMEINTODMS_DEALER_ID,DMS_PROVINCE_ID,DMS_CITY_ID,DMS_DEALER_NAME

          FROM E_DMS_DEALER_REFUSEWHERE DEALER_ID= DMS_C_11;

     SELECT MODEL_IDINTO DMS_CAR_MODELFROM E_DMS_CARMODEL_REFUSEWHERE COMMERCIAL_CODE= DMS_C_68;

     START TRANSACTION;

     INSERTINTOE_CAROWNER(VIN_CODE,CAR_CODE,CAR_MODEL,USER_NAME,IDENTITY_CARD,SUB_CAR_TYPE,CAR_TYPE

               ,DEALER_PROVINCE,DEALER_CITY,DEALER_ID,USER_ID,D_ID,AUTH_APPROVE_TIME,AUTH_TYPE,CAR_AUTH_STATUS)

                VALUES(UVIN,UCAR,DMS_CAR_MODEL,DMS_C_25,DMS_C_27,DMS_CAR_CODE_SUB,DMS_CAR_CODE,DMS_PROVINCE_ID

               ,DMS_CITY_ID,DMS_C_11,UID,DMS_D_ID,UNIX_TIMESTAMP(CURRENT_TIME()),2,1);

     SELECT CAROWNER_IDINTO NEW_CAROWNER_IDFROM E_CAROWNERWHERE VIN_CODE= UVIN;

    

     if (strcmp(DMS_C_23,' ')=0)then

        set DMS_C_23='0-0-0';

     end if;

    

     INSERTINTOE_CAROWNER_DETAIL(BUY_TIME,CAROWNER_ID,AUTH_APPLAY_TIME,CREATOR_ID,USER_CARD_ID,CAROWNER_NAME,USER_EMAIL

               ,USER_CELL,DEALER_NAME,CREATE_TIME,CARD_ACTIVATE_STATUS,CARD_SEND_STATUS,IS_FULL_INFO)

                VALUES (UNIX_TIMESTAMP(DMS_C_23),NEW_CAROWNER_ID,UNIX_TIMESTAMP(CURRENT_TIME()),UID,DMS_CARD_ID,

                DMS_C_25,UEMAIL,UCELL,DMS_DEALER_NAME,UNIX_TIMESTAMP(CURRENT_TIME()),1,0,0);

 

     UPDATE E_DMS_SALESDATASET IS_AUTH=1,AUTH_TIME=UNIX_TIMESTAMP(CURRENT_TIME()),IS_ACTIVITE=1,

                ACTIVATE_TIME=UNIX_TIMESTAMP(CURRENT_TIME())WHERE D_ID = DMS_D_ID;

     DELETEFROM E_APPDMSWHERE A_ID= VAID;

     COMMIT;

   END IF;

  ELSE

    SELECTCOUNT(1)INTO D_COUNT FROM E_DMS_SALESDATA WHERE C_7 = UVIN;

    IF (D_COUNT>0)THEN

       DELETEFROM E_APPDMSWHERE VIN_CODE= UVIN;

    END IF;

  END IF;

  FETCH CURINTO VAID, UID,UEMAIL,UCELL,UVIN,UCAR;

ENDWHILE;

CLOSE CUR;

END;

 

callappdms_proc();

 

定时器、事件例：

create event appdms_checker

  ON SCHEDULE every1 day  --每天执行

  startsCURRENT_TIMESTAMP+INTERVAL12 HOUR -- 当前时间12小时后开始执行

  docall appdms_proc();

 

我的例子

-- 存储过程定时器 定时新增用户

drop procedure if existscustomer_increment_proc;

create procedure customer_increment_proc(incrDate date)

begin

-- 上一天数量

declare preDaysCount int;

declare flag int;

select count(1) into preDaysCount fromfh_customer where create_date between date_format(date_add(incrDate, interval-1 day),'%Y-%m-%d 00:00:00') and date_format(date_add(incrDate, interval -1day),'%Y-%m-%d 23:59:59') and del_flag = '0';

-- 数量随机增减

set flag = 1;

if MOD(DAYOFYEAR(now()), FLOOR(rand() *31)) = 0 then set flag = -1; end if;

 

set preDaysCount = preDaysCount +FLOOR(rand() * 150);

-- 新增当日用户

update fh_customer set del_flag = '0',create_date = concat(date_format(incrDate,'%Y-%m-%d'),FLOOR(4+rand()*20),':',FLOOR(rand()*59),':',FLOOR(rand()*59))   where del_flag = '1' limit preDaysCount;

end;

 

 

-- callcustomer_increment_proc(str_to_date('2017-04-21 00:00:00','%Y-%m-%d%H:%i:%s'));

 

-- 定时器定时调用新增用户

 

create event customer_incr_task

on schedule every 1 day

starts current_timestamp + interval 11 hour

on completion preserve

do call customer_increment_proc(now());

 

注意：mysql默认不开启事件计划任务，需要开启：

 show variables like '%sche%';   --如果event_scheduler是OFF，需要执行下面语句开启

 set global event_scheduler = 1;