mysql存储过程定时任务_mysql存储过程及定时任务

一、创建存储过程

DROP PROCEDURE IF EXISTS `daystatisticsProcedure`;

CREATE DEFINER = `root`@`localhost` PROCEDURE `daystatisticsProcedure`()

BEGIN

-- 变量

DECLARE done INT DEFAULT 0;

-- 统计参数变量

DECLARE userId VARCHAR(32);

DECLARE userRealName VARCHAR(50);

DECLARE BuserID VARCHAR(32);

DECLARE operation INT DEFAULT 0; -- 操作记录

DECLARE newCustomers INT DEFAULT 0; -- 新增客户记录

DECLARE maintainCustomers,maintainRecord INT DEFAULT 0; -- 日维护客户数/日维护记录数

DECLARE childCount INT DEFAULT 0; -- 日发展线下用户统计数

DECLAREpolicyCount,policySum,policyExtract INT DEFAULT 0;-- 个人业绩统计(保单、金额、提成)

DECLAREBpolicyCount,BpolicySum,BpolicyExtract INT DEFAULT 0;-- B级业绩统计(保单、金额、提成)

DECLARECpolicyCount,CpolicySum,CpolicyExtract INT DEFAULT 0;-- B级业绩统计(保单、金额、提成)

-- 获取用户并赋值给游标

DECLARE mycursor CURSOR FOR SELECT dcUserID,dcUserRealName FROM tb_user WHERE dnStatus = 0;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;

-- 打开游标

OPEN mycursor;

-- 循环执行

REPEAT

-- 声明赋值

FETCH mycursor INTO userid,userRealName;

IF NOT done THEN

-- 查询用户操作记录

SELECT COUNT(dcUserID) INTO operation FROM tb_logtrack

WHERE DATE_FORMAT(dtAddtime,'%Y%m%d')=DATE_ADD(CURDATE(), INTERVAL -1 DAY) AND dcUserID = userId;

-- 查询用户新增客户

SELECT COUNT(dcUserID) INTO newCustomers FROM tb_customer

WHERE DATE_FORMAT(dtAddTime,'%Y%m%d')=DATE_ADD(CURDATE(), INTERVAL -1 DAY) AND dcUserID = userId;

-- 根据用户ID查询日维护客户数/日维护记录数

SELECT COUNT(DISTINCT(dcCustomer)),COUNT(dcUserID) INTO maintainCustomers,maintainRecord FROM tb_customertraceinfo

WHERE DATE_FORMAT(dtAddTime,'%Y%m%d')=DATE_ADD(CURDATE(), INTERVAL -1 DAY) AND dcUserID = userId ;

-- 根据用户ID查询日发展线下用户统计数

SELECT COUNT(dcUserIDParent) INTO childCount FROM tb_userrelationown

WHERE DATE_FORMAT(dtAddTime,'%Y%m%d')=DATE_ADD(CURDATE(), INTERVAL -1 DAY) AND dcUserIDParent = userId;

-- 个人业绩统计(保单、金额、提成)

SELECT COUNT(dcUserID),SUM(ddPriceSum),SUM(ddPriceT) INTO policyCount,policySum,policyExtract FROM tb_policymsgt

WHERE DATE_FORMAT(dtAddTime,'%Y%m%d')=DATE_ADD(CURDATE(), INTERVAL -1 DAY) AND dcUserID = userId;

-- B级业务统计(保单、金额、提成)

SELECT COUNT(dcUserID),SUM(ddPriceSum),SUM(ddPriceT) INTO BpolicyCount,BpolicySum,BpolicyExtract FROM tb_policymsgt

WHERE DATE_FORMAT(dtAddTime,'%Y%m%d')=DATE_ADD(CURDATE(), INTERVAL -1 DAY) AND

dcUserID in(SELECT dcUserID FROM tb_userrelationown WHERE dcUserIDParent = userId);

-- C级业务统计(保单、金额、提成)

SELECT COUNT(dcUserID),SUM(ddPriceSum),SUM(ddPriceT) INTO CpolicyCount,CpolicySum,CpolicyExtract FROM tb_policymsgt

WHERE DATE_FORMAT(dtAddTime,'%Y%m%d')=DATE_ADD(CURDATE(), INTERVAL -1 DAY)

AND dcUserID in(SELECT dcUserID FROM tb_userrelationown WHERE dcUserIDParent

in(SELECT dcUserID FROM tb_userrelationown WHERE dcUserIDParent = userId));

-- 添加日统计记录

INSERT INTO tb_dailystatistics VALUES(REPLACE(uuid(),'-',''),userId,userRealName,NULL,NULL,NULL,NULL,

YEAR(DATE_ADD(CURDATE(), INTERVAL -1 DAY)),MONTH(DATE_ADD(CURDATE(), INTERVAL -1 DAY)),DAY(DATE_ADD(CURDATE(), INTERVAL -1 DAY)),NOW(),'0',operation,

newCustomers,maintainCustomers,maintainRecord,childCount,policyCount,policySum,policyExtract,

BpolicyCount,BpolicySum,BpolicyExtract,CpolicyCount,CpolicySum,CpolicyExtract);

END IF;

-- 循环结束

UNTIL done END REPEAT;

-- 关闭游标

CLOSE mycursor;

-- 打印结果

END;

二、创建定时任务事件

三、开启定时任务

-- 关闭定时任务

ALTER EVENT 《事件名称》 DISABLE;

-- 开启定时任务

ALTER EVENT 《事件名称》 ENABLE;