笔记：存储过程-定时器

存储过程-定时器

存储过程：

  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_COUNT INT DEFAULT-1;

DECLARE _STOP INT DEFAULT0;

DECLARE VAID INT;

DECLARE UID INT(11);

DECLARE UEMAIL VARCHAR(100);

DECLARE UCELL VARCHAR(20);

DECLARE UVIN VARCHAR(50);

DECLARE UCAR VARCHAR(20);

DECLARE DMS_CAR_CODE_SUB VARCHAR(30);

DECLARE DMS_CAR_CODE VARCHAR(30);

DECLARE DMS_C_25 VARCHAR(30);

DECLARE DMS_C_27 VARCHAR(50);

DECLARE DMS_C_11 VARCHAR(50);

DECLARE DMS_C_23 VARCHAR(30);

DECLARE DMS_C_68 VARCHAR(30);

DECLARE DMS_D_ID VARCHAR(50);

DECLARE DMS_DEALER_ID VARCHAR(20);

DECLARE DMS_PROVINCE_ID VARCHAR(20);

DECLARE DMS_CITY_ID VARCHAR(20);

DECLARE DMS_DEALER_NAME VARCHAR(100);

DECLARE DMS_CAR_MODEL VARCHAR(30);

DECLARE DMS_CARD_ID VARCHAR(60);

DECLARE NEW_CAROWNER_ID INT(11);

DECLARE CUR CURSORFORSELECT A_ID,USER_ID,EMAIL,USER_CELL,VIN_CODE,CAR_CODE FROM E_APPDMS;

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

DECLAREEXIT HANDLER FORSQLSTATE'02000'

    BEGIN

      SET _STOP =1;

      SELECT'游标正常结束!';

   END;

 

   DECLAREEXIT HANDLER FORSQLEXCEPTION

   BEGIN

       SET _STOP=1;

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

   END;

OPEN CUR;

FETCH CUR INTO VAID,UID,UEMAIL,UCELL,UVIN,UCAR;

 

WHILE _STOP <>1 DO

  SELECTCOUNT(1) INTO D_COUNT FROM E_DMS_SALESDATA WHERE C_7 = UVIN AND 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_NAME INTODMS_DEALER_ID,DMS_PROVINCE_ID,DMS_CITY_ID,DMS_DEALER_NAME

          FROM E_DMS_DEALER_REFUSE WHERE DEALER_ID = DMS_C_11;

     SELECT MODEL_ID INTO DMS_CAR_MODEL FROM E_DMS_CARMODEL_REFUSE WHERE 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_ID INTO NEW_CAROWNER_ID FROM E_CAROWNER WHERE 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_SALESDATA SET 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_APPDMS WHERE 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_APPDMS WHERE VIN_CODE = UVIN;

    END IF;

  END IF;

  FETCH CUR INTO VAID, UID,UEMAIL,UCELL,UVIN,UCAR;

ENDWHILE;

CLOSE CUR;

END;

 

callappdms_proc();

 

定时器、事件例：

create event appdms_checker

  ON SCHEDULE every 1 day  -- 每天执行

  starts CURRENT_TIMESTAMP+INTERVAL 12 HOUR  -- 当前时间12小时后开始执行

  do call 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;