mysql存储过程及定时任务

最新推荐文章于 2025-07-05 14:23:41 发布

weixin_34205076

最新推荐文章于 2025-07-05 14:23:41 发布

阅读量120

点赞数

CC 4.0 BY-SA版权

文章标签：数据库 python

原文链接：https://my.oschina.net/Clarences/blog/2050212

本文介绍了一个使用MySQL存储过程进行日统计的例子。该存储过程通过游标遍历用户表，并统计每位用户的各项指标，如操作记录数、新增客户数、维护客户数等，最后将统计数据插入到日统计表中。

2019独角兽企业重金招聘Python工程师标准>>>

一、创建存储过程

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;  -- 日发展线下用户统计数
	DECLARE	policyCount,policySum,policyExtract INT DEFAULT 0;-- 个人业绩统计(保单、金额、提成)
	DECLARE	BpolicyCount,BpolicySum,BpolicyExtract INT DEFAULT 0;-- B级业绩统计(保单、金额、提成)
	DECLARE	CpolicyCount,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;

转载于:https://my.oschina.net/Clarences/blog/2050212