MySQL定时任务的创建（存储过程）

首先定时任务一定是执行的存储过程

类似于：

CREATE DEFINER=`root`@`10.1.10.41` PROCEDURE `account_balance_snapshot_insert`()
BEGIN
DECLARE today VARCHAR (20);
DECLARE yesLogDate VARCHAR(20);
DECLARE accountId VARCHAR (40);
DECLARE productType VARCHAR (20);
DECLARE monthFare DOUBLE;
    DECLARE monthRealFare DOUBLE;
DECLARE leftFare400 DOUBLE;
DECLARE smsFare DOUBLE;
DECLARE telFare DOUBLE;
DECLARE faxFare DOUBLE;
DECLARE accountMonthFare DOUBLE;
DECLARE cursor_flag INT;
DECLARE mycur CURSOR FOR SELECT ACCOUNT_ID,MONTH_FARE,MONTH_REAL_FARE,LEFT_FARE_400,ACCOUNT_MONTH_FARE,SMS_FARE,TEL_FARE,FAX_FARE FROM c5_billing.`account_service_fare_snapshot_view` t;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET cursor_flag = 1 ;
SET cursor_flag = 0 ;
/*删除快照临时表的数据*/
DELETE FROM `c5_billing`.`tbl_fare_snapshot_log`;
SET productType = 'callCenter';
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d') INTO today;
SELECT DATE_FORMAT(NOW() - INTERVAL 1 DAY, '%Y-%m-%d') INTO yesLogDate;
/** 座席license、外呼、短信、传真、月租和400余额 **/
OPEN mycur;
loopflag : LOOP
FETCH mycur INTO accountId,monthFare,monthRealFare,leftFare400,accountMonthFare,smsFare,telFare,faxFare;
IF cursor_flag =1 THEN LEAVE loopflag;
END IF;
INSERT INTO c5_billing.tbl_fare_snapshot_log (PRODUCT_TYPE,LOG_TIME,ACCOUNT_ID,MONTH_FARE,MONTH_REAL_FARE,SMS_FARE,TEL_FARE,FAX_FARE,LEFT_FARE_400,ACCOUNT_MONTH_FARE) 
VALUES(productType,yesLogDate,accountId,monthFare,monthRealFare,smsFare,telFare,faxFare,LeftFare400,accountMonthFare);
COMMIT;
END LOOP loopflag ;
CLOSE mycur;

/** 座席license当日消费金额 **/
INSERT INTO `c5_billing`.tbl_fare_snapshot_log (PRODUCT_TYPE,LOG_TIME,ACCOUNT_ID,MONTH_FEE,MONTH_REAL_FEE)
SELECT productType,t.log_time,t.account_id,t.fare,t.real_fare FROM `c5_billing`.`c5_account_license_fare_log` t WHERE t.log_time=yesLogDate;
COMMIT;
/** 座席license当日补扣金额 **/
INSERT INTO `c5_billing`.tbl_fare_snapshot_log (PRODUCT_TYPE,LOG_TIME,ACCOUNT_ID,MONTH_FEE,MONTH_REAL_FEE)
SELECT 'callCenter',yesLogDate,t.account_id,t.LOG_FEE,t.license_real_fee FROM `c5_billing`.`tbl_log` t 
WHERE t.LOG_TIME>=yesLogDate AND t.LOG_TYPE='license';

/** 座席月租当日消费金额 **/
INSERT INTO `c5_billing`.tbl_fare_snapshot_log (PRODUCT_TYPE,LOG_TIME,ACCOUNT_ID,ACCOUNT_MONTH_FEE)
SELECT 'callCenter',yesLogDate,t.account_id,t.LOG_FEE FROM `c5_billing`.`tbl_log` t 
WHERE t.LOG_TIME>=yesLogDate AND t.LOG_TYPE='monthlyFees';

/** 短信当日消费金额 **/
DELETE FROM `c5_bill_stat`.`tbl_sms_send`;
INSERT INTO `c5_bill_stat`.`tbl_sms_send` 
 (SELECT * FROM `c5_billing`.`c5_sms_send` t 
 WHERE t.ISDO='1' AND t.`SEND_TIME` >= yesLogDate AND t.`SEND_TIME` < today);
 COMMIT ;
INSERT INTO `c5_billing`.tbl_fare_snapshot_log (PRODUCT_TYPE,LOG_TIME,ACCOUNT_ID,SMS_FEE)
SELECT 'callCenter',yesLogDate,t.`ACCOUNT_ID`,IFNULL(SUM(t.SMS_FARE),0) 
FROM `c5_bill_stat`.`tbl_sms_send` t GROUP BY t.`ACCOUNT_ID`;

/** 传真当日消费金额 **/
DELETE FROM `c5_bill_stat`.`tbl_fax_send`;
INSERT INTO `c5_bill_stat`.`tbl_fax_send` 
 (SELECT * FROM `c5_billing`.`c5_fax_send` t 
 WHERE t.ISDO='1' AND t.`SEND_TIME` >= yesLogDate AND t.`SEND_TIME` < today);
 COMMIT ;
INSERT INTO `c5_billing`.tbl_fare_snapshot_log (PRODUCT_TYPE,LOG_TIME,ACCOUNT_ID,FAX_FEE)
SELECT 'callCenter',yesLogDate,t.`ACCOUNT_ID`,IFNULL(COUNT(t.`FAX_ID`)*fax.`all_price`,0) 
FROM `c5_bill_stat`.`tbl_fax_send` t LEFT JOIN `c5_billing`.`c5_account_service_fare` fax 
ON t.`ACCOUNT_ID`=fax.`ACCOUNT_ID` AND fax.`FARE_TYPE`='FAX' GROUP BY t.`ACCOUNT_ID`;

/** 外呼当日消费金额 **/
DELETE FROM `c5_call_record`.`c5_call_sheet_t`;
INSERT INTO `c5_call_record`.`c5_call_sheet_t` 
(SELECT * FROM `c5_call_record`.`c5_call_sheet` t 
 WHERE t.isdo = '1' AND t.`BEGIN_TIME` >= yesLogDate AND t.`BEGIN_TIME` < today);
 COMMIT ;
INSERT INTO `c5_billing`.tbl_fare_snapshot_log (PRODUCT_TYPE,LOG_TIME,ACCOUNT_ID,TEL_FEE)
SELECT 'callCenter',yesLogDate,t.`ACCOUNT_ID`,IFNULL(SUM(t.`CALL_FARE`),0) 
FROM `c5_call_record`.`c5_call_sheet_t` t GROUP BY t.`ACCOUNT_ID`;
COMMIT;

/** 400当日消费金额 **/
DELETE FROM `c5_call_record`.`c5_call_sheet_t`;
INSERT INTO `c5_call_record`.`c5_call_sheet_t` (
CALL_ID,ACCOUNT_ID,BEGIN_TIME,ISDO,call_fare
) 
SELECT call_sheet_id,account_id,begin_time,ISDO,CALL_FARE 
FROM `c5_call_record`.`c5_call_sheet_400` t WHERE ISDO='1' AND t.`begin_time` >= yesLogDate AND t.`begin_time` < today;
 COMMIT ;
INSERT INTO `c5_billing`.tbl_fare_snapshot_log (PRODUCT_TYPE,LOG_TIME,ACCOUNT_ID,FEE_400)
SELECT 'callCenter',yesLogDate,t.`ACCOUNT_ID`,IFNULL(SUM(t.`CALL_FARE`),0) 
FROM `c5_call_record`.`c5_call_sheet_t` t GROUP BY t.`ACCOUNT_ID`;
COMMIT;   
     
     /** 400当日消费金额（按照服务号） **/
     DELETE FROM `c5_call_record`.`c5_call_sheet_400_service_num`;
     INSERT INTO `c5_call_record`.`c5_call_sheet_400_service_num` (
          CALLED_NO,ACCOUNT_ID,BEGIN_TIME,ISDO,call_fare
     ) 
     SELECT called_no,account_id,begin_time,ISDO,CALL_FARE           
     FROM `c5_call_record`.`c5_call_sheet_400` t WHERE ISDO='1' AND t.`begin_time` >= yesLogDate AND t.`begin_time` < today;
       COMMIT ;     
     INSERT INTO `c5_billing`.tbl_fare_snapshot_num_400_log (SERVICE_NUM,PRODUCT_TYPE,LOG_TIME,ACCOUNT_ID,FEE_400)
     SELECT t.`CALLED_NO` AS SERVICE_NUM, '400Fee',yesLogDate,t.`ACCOUNT_ID`,IFNULL(SUM(t.`CALL_FARE`),0) 
     FROM `c5_call_record`.`c5_call_sheet_400_service_num` t GROUP BY t.`CALLED_NO`;     
     COMMIT;  
     
/** 座席LICENSE当日缴费与赠送金额 **/
INSERT INTO `c5_billing`.tbl_fare_snapshot_log (PRODUCT_TYPE,LOG_TIME,ACCOUNT_ID,MONTH_PAYMENT,MONTH_PAYMENT_GIFT)
SELECT 'callCenter',yesLogDate,t.`ACCOUNT_ID`,IFNULL(SUM(t.`payment_fare`),0),IFNULL(SUM(t.`return_fare`),0) 
FROM `c5_billing`.`c5_account_fare_log` t WHERE t.`change_time`>=yesLogDate AND t.`change_time`<today 
AND t.`fare_type`='month' GROUP BY t.`ACCOUNT_ID`;
/** 短信当日缴费金额 **/
INSERT INTO `c5_billing`.tbl_fare_snapshot_log (PRODUCT_TYPE,LOG_TIME,ACCOUNT_ID,SMS_PAYMENT)
SELECT 'callCenter',yesLogDate,t.`ACCOUNT_ID`,IFNULL(SUM(t.`payment_fare`),0) 
FROM `c5_billing`.`c5_account_fare_log` t WHERE t.`change_time`>=yesLogDate AND t.`change_time`<today 
AND t.`fare_type`='SMS' GROUP BY t.`ACCOUNT_ID`;
/** 传真当日缴费金额 **/
INSERT INTO `c5_billing`.tbl_fare_snapshot_log (PRODUCT_TYPE,LOG_TIME,ACCOUNT_ID,FAX_PAYMENT)
SELECT 'callCenter',yesLogDate,t.`ACCOUNT_ID`,IFNULL(SUM(t.`payment_fare`),0) 
FROM `c5_billing`.`c5_account_fare_log` t WHERE t.`change_time`>=yesLogDate AND t.`change_time`<today 
AND t.`fare_type`='FAX' GROUP BY t.`ACCOUNT_ID`;
/** 通话当日缴费金额 **/
INSERT INTO `c5_billing`.tbl_fare_snapshot_log (PRODUCT_TYPE,LOG_TIME,ACCOUNT_ID,TEL_PAYMENT)
SELECT 'callCenter',yesLogDate,t.`ACCOUNT_ID`,IFNULL(SUM(t.`payment_fare`),0) 
FROM `c5_billing`.`c5_account_fare_log` t WHERE t.`change_time`>=yesLogDate AND t.`change_time`<today 
AND t.`fare_type`='TEL' GROUP BY t.`ACCOUNT_ID`;
/** 月租当日缴费金额 **/
INSERT INTO `c5_billing`.tbl_fare_snapshot_log (PRODUCT_TYPE,LOG_TIME,ACCOUNT_ID,ACCOUNT_MONTH_PAYMENT)
SELECT 'callCenter',yesLogDate,t.`ACCOUNT_ID`,IFNULL(SUM(t.`payment_fare`),0) 
FROM `c5_billing`.`c5_account_fare_log` t WHERE t.`change_time`>=yesLogDate AND t.`change_time`<today 
AND t.`fare_type`='monthRent' GROUP BY t.`ACCOUNT_ID`;
/** 400当日缴费金额 **/
INSERT INTO `c5_billing`.tbl_fare_snapshot_log (PRODUCT_TYPE,LOG_TIME,ACCOUNT_ID,PAYMENT_400)
SELECT 'callCenter',yesLogDate,t.`ACCOUNT_ID`,IFNULL(SUM(t.`payment_fare`),0) 
FROM `c5_billing`.`c5_account_fare_log` t WHERE t.`change_time`>=yesLogDate AND t.`change_time`<today 
AND t.`fare_type`='400' GROUP BY t.`ACCOUNT_ID`;

INSERT INTO `c5_bill_stat`.`tbl_fare_snapshot_log_count` (PRODUCT_TYPE,LOG_TIME,ACCOUNT_ID,MONTH_FARE,MONTH_REAL_FARE,
SMS_FARE,TEL_FARE,FAX_FARE,LEFT_FARE_400,ACCOUNT_MONTH_FARE,MONTH_FEE,MONTH_REAL_FEE,SMS_FEE,TEL_FEE,FAX_FEE,FEE_400,
ACCOUNT_MONTH_FEE,MONTH_PAYMENT,MONTH_PAYMENT_GIFT,SMS_PAYMENT,TEL_PAYMENT,FAX_PAYMENT,PAYMENT_400,
ACCOUNT_MONTH_PAYMENT)
SELECT t.`PRODUCT_TYPE`,t.`LOG_TIME`,t.`ACCOUNT_ID`,SUM(t.`MONTH_FARE`),SUM(t.`MONTH_REAL_FARE`),SUM(t.`SMS_FARE`),SUM(t.`TEL_FARE`),
SUM(t.`FAX_FARE`),SUM(t.`LEFT_FARE_400`),SUM(t.`ACCOUNT_MONTH_FARE`),SUM(t.`MONTH_FEE`),SUM(t.`MONTH_REAL_FEE`),SUM(t.`SMS_FEE`),
SUM(t.`TEL_FEE`),SUM(t.`FAX_FEE`),SUM(t.`FEE_400`),SUM(t.`ACCOUNT_MONTH_FEE`),SUM(t.`MONTH_PAYMENT`),
SUM(t.`MONTH_PAYMENT_GIFT`),SUM(t.`SMS_PAYMENT`),SUM(t.`TEL_PAYMENT`),SUM(t.`FAX_PAYMENT`),SUM(t.`PAYMENT_400`),SUM(t.`ACCOUNT_MONTH_PAYMENT`)  
FROM `c5_billing`.`tbl_fare_snapshot_log` t GROUP BY t.LOG_TIME,t.ACCOUNT_ID;
 
    INSERT INTO `c5_bill_stat`.`tbl_fare_snapshot_num_400_log_count` (SERVICE_NUM,PRODUCT_TYPE,LOG_TIME,ACCOUNT_ID,FEE_400)
SELECT t.SERVICE_NUM,t.`PRODUCT_TYPE`,t.`LOG_TIME`,t.`ACCOUNT_ID`,SUM(t.`FEE_400`)
FROM `c5_billing`.`tbl_fare_snapshot_num_400_log` t GROUP BY t.LOG_TIME,t.SERVICE_NUM;

END;

又或者是如下之类的存储过程

CREATE DEFINER=`root`@`10.1.10.41` PROCEDURE `agent_change_count`()
BEGIN
  DECLARE DATE VARCHAR (20) DEFAULT DATE_FORMAT(NOW() - INTERVAL 1 DAY, '%Y-%m-%d');
  DECLARE change_add VARCHAR (20) ;
  DECLARE change_delete VARCHAR (20) ;
  DECLARE product_ivr VARCHAR (10) ;
  DECLARE product_standard VARCHAR (10) ;
  DECLARE product_enterprise VARCHAR (10) ;
  DECLARE product_business VARCHAR (10) ;
  DECLARE product_monitor VARCHAR (10) ;
  DECLARE product_order VARCHAR (10) ;
  DECLARE product_ext VARCHAR (10) ;
  DECLARE product_outCall VARCHAR (10) ;
  DECLARE accoountId VARCHAR (40) ;
  DECLARE cursor_flag INT ;
  DECLARE account_cursor CURSOR FOR 
  SELECT 
   DISTINCT account_id 
  FROM
    c5_billing.`c5_agent_change_log` 
  WHERE change_time LIKE CONCAT(DATE, '%') ;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET cursor_flag = 1 ;
  SET change_add = 'add' ;
  SET change_delete = 'delete' ;
  SET product_ivr = '5' ;
  SET product_standard = '3' ;
  SET product_enterprise = '2' ;
  SET product_business = '6' ;
  SET product_monitor = '1' ;
  SET product_order = '7' ;
  SET product_ext = '8';
  SET product_outCall = '4' ;
  SET cursor_flag = 0 ;
  OPEN account_cursor;
  loopflag : LOOP


FETCH account_cursor INTO accoountId ;
IF cursor_flag =1 THEN LEAVE loopflag;
END IF;
SELECT accoountId;
SELECT cursor_flag;
    INSERT INTO c5_bill_stat.`tbl_agent_change_count` 
    (SELECT 
      * 
    FROM
      (
        (SELECT 
          NULL) id,
        (SELECT 
          accoountId) account_id,
        (SELECT 
          DATE) DATE,
        (SELECT 
          IFNULL(SUM(t.change_num), 0) 
        FROM
          c5_billing.c5_agent_change_log t 
        WHERE t.change_time LIKE CONCAT(DATE, '%') 
          AND t.change_type = change_add 
          AND t.account_id = accoountId) add_number_total,
        (SELECT 
          IFNULL(SUM(t.change_num), 0) 
        FROM
          c5_billing.c5_agent_change_log t 
        WHERE t.change_time LIKE CONCAT(DATE, '%') 
          AND t.change_type = change_delete 
          AND t.account_id = accoountId) delete_number_total,
        (SELECT 
          IFNULL(SUM(t.change_num), 0) 
        FROM
          c5_billing.c5_agent_change_log t 
        WHERE t.change_time LIKE CONCAT(DATE, '%') 
          AND t.change_type = change_add 
          AND t.change_product = product_ivr 
          AND t.account_id = accoountId) add_ivr,
        (SELECT 
          IFNULL(SUM(t.change_num), 0) 
        FROM
          c5_billing.c5_agent_change_log t 
        WHERE t.change_time LIKE CONCAT(DATE, '%') 
          AND t.change_type = change_delete 
          AND t.change_product = product_ivr 
          AND t.account_id = accoountId) delete_ivr,
        (SELECT 
          IFNULL(SUM(t.change_num), 0) 
        FROM
          c5_billing.c5_agent_change_log t 
        WHERE t.change_time LIKE CONCAT(DATE, '%') 
          AND t.change_type = change_add 
          AND t.change_product = product_standard 
          AND t.account_id = accoountId) add_standard,
        (SELECT 
          IFNULL(SUM(t.change_num), 0) 
        FROM
          c5_billing.c5_agent_change_log t 
        WHERE t.change_time LIKE CONCAT(DATE, '%') 
          AND t.change_type = change_delete 
          AND t.change_product = product_standard 
          AND t.account_id = accoountId) delete_standard,
        (SELECT 
          IFNULL(SUM(t.change_num), 0) 
        FROM
          c5_billing.c5_agent_change_log t 
        WHERE t.change_time LIKE CONCAT(DATE, '%') 
          AND t.change_type = change_add 
          AND t.change_product = product_enterprise 
          AND t.account_id = accoountId) add_enterprise,
        (SELECT 
          IFNULL(SUM(t.change_num), 0) 
        FROM
          c5_billing.c5_agent_change_log t 
        WHERE t.change_time LIKE CONCAT(DATE, '%') 
          AND t.change_type = change_delete 
          AND t.change_product = product_enterprise 
          AND t.account_id = accoountId) delete_enterprise,
        (SELECT 
          IFNULL(SUM(t.change_num), 0) 
        FROM
          c5_billing.c5_agent_change_log t 
        WHERE t.change_time LIKE CONCAT(DATE, '%') 
          AND t.change_type = change_add 
          AND t.change_product = product_monitor 
          AND t.account_id = accoountId) add_monitor,
        (SELECT 
          IFNULL(SUM(t.change_num), 0) 
        FROM
          c5_billing.c5_agent_change_log t 
        WHERE t.change_time LIKE CONCAT(DATE, '%') 
          AND t.change_type = change_delete 
          AND t.change_product = product_monitor 
          AND t.account_id = accoountId) delete_monitor,
        (SELECT 
          IFNULL(SUM(t.change_num), 0) 
        FROM
          c5_billing.c5_agent_change_log t 
        WHERE t.change_time LIKE CONCAT(DATE, '%') 
          AND t.change_type = change_add 
          AND t.change_product = product_business 
          AND t.account_id = accoountId) add_business,
        (SELECT 
          IFNULL(SUM(t.change_num), 0) 
        FROM
          c5_billing.c5_agent_change_log t 
        WHERE t.change_time LIKE CONCAT(DATE, '%') 
          AND t.change_type = change_delete 
          AND t.change_product = product_business 
          AND t.account_id = accoountId) delete_business,
(SELECT 
          IFNULL(SUM(t.change_num), 0) 
        FROM
          c5_billing.c5_agent_change_log t 
        WHERE t.change_time LIKE CONCAT(DATE, '%') 
          AND t.change_type = change_add 
          AND t.change_product = product_outCall 
          AND t.account_id = accoountId) add_outcall,
        (SELECT 
          IFNULL(SUM(t.change_num), 0) 
        FROM
          c5_billing.c5_agent_change_log t 
        WHERE t.change_time LIKE CONCAT(DATE, '%') 
          AND t.change_type = change_delete 
          AND t.change_product = product_outCall 
          AND t.account_id = accoountId) delete_outcall,
(SELECT 
          IFNULL(SUM(t.change_num), 0) 
        FROM
          c5_billing.c5_agent_change_log t 
        WHERE t.change_time LIKE CONCAT(DATE, '%') 
          AND t.change_type = change_add 
          AND t.change_product = product_order 
          AND t.account_id = accoountId) add_order,
        (SELECT 
          IFNULL(SUM(t.change_num), 0) 
        FROM
          c5_billing.c5_agent_change_log t 
        WHERE t.change_time LIKE CONCAT(DATE, '%') 
          AND t.change_type = change_delete 
          AND t.change_product = product_order 
          AND t.account_id = accoountId) delete_order,
        (SELECT 
          COUNT(DISTINCT t.account_id) 
        FROM
          c5_billing.c5_agent_change_log t 
        WHERE t.change_time LIKE CONCAT(DATE, '%') 
          AND t.account_id = accoountId) change_account,
        (SELECT 
          IFNULL(SUM(t.change_num), 0) 
        FROM
          c5_billing.c5_agent_change_log t 
        WHERE t.change_time LIKE CONCAT(DATE, '%') 
          AND t.change_type = change_add 
          AND t.change_product = product_ext 
          AND t.account_id = accoountId) add_ext,
        (SELECT 
          IFNULL(SUM(t.change_num), 0) 
        FROM
          c5_billing.c5_agent_change_log t 
        WHERE t.change_time LIKE CONCAT(DATE, '%') 
          AND t.change_type = change_delete 
          AND t.change_product = product_ext 
          AND t.account_id = accoountId) delete_ext
      )) ;
  END LOOP loopflag ;
  CLOSE account_cursor ;
  COMMIT ;
  
END;