简单的Mysql统计存储过程

所用到的表结构

被统计表

CREATE TABLE `sm_car_order` (
  `order_id` int(11) NOT NULL AUTO_INCREMENT,
  `org_code` int(11) NOT NULL COMMENT '机构ID',
  `order_create_time` datetime NOT NULL COMMENT '订单创建时间',
  `service_cost` decimal(8,2) NOT NULL COMMENT '服务成本',
  `class_id` int(11) DEFAULT NULL COMMENT '小类id'
) ENGINE=InnoDB AUTO_INCREMENT=19759 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='订单表';`

统计表

CREATE TABLE `sm_car_statistics_day` (
  `day_id` int(11) NOT NULL AUTO_INCREMENT,
  `day_org_code` int(11) DEFAULT '0' COMMENT '所属机构id',
  `day_total` int(10) DEFAULT '0' COMMENT '总订单数',
  `day_cost` decimal(10,2) DEFAULT '0.00' COMMENT '总服务成本',
  `day_class_id` int(11) DEFAULT '0' COMMENT '小类id',
  `day_time` int(11) unsigned DEFAULT '20200212' COMMENT '日期',
  PRIMARY KEY (`day_id`) USING BTREE,
  UNIQUE KEY `code_class_time` (`day_org_code`,`day_class_id`,`day_time`)
) ENGINE=InnoDB AUTO_INCREMENT=44059 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='日统计表';

异常消息表

CREATE TABLE `sm_car_procedure_error_log` (
  `error_code` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `error_msg` text COLLATE utf8_unicode_ci,
  `error_procedure_info` text COLLATE utf8_unicode_ci,
  `error_create_time` datetime DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

存储过程实现逻辑：实现了异常捕捉，异常终止，批量插入；

CREATE DEFINER=`root`@`localhost` PROCEDURE `handleOrderStaticsDay`(IN `var_date` int,OUT count_num INT)
BEGIN
-- 设置异常信息变量
	DECLARE ERROR_CODE CHAR(5) DEFAULT '000000';
	DECLARE ERROR_MSG TEXT;
	DECLARE ERROR_PROCEDURE_INFO TEXT;
	DECLARE done BOOLEAN DEFAULT 0;
	DECLARE var_total INT DEFAULT 0;
	DECLARE var_cost DOUBLE DEFAULT 0.00;
	DECLARE var_org_code INT DEFAULT 0;
	DECLARE var_class_id INT DEFAULT 	0;
	DECLARE var_insert_str LONGTEXT DEFAULT '';-- 批量更新字符串
	DECLARE var_update_insert_count INT DEFAULT 1; -- 计数初始化
	DECLARE var_update_insert_num INT DEFAULT 1000;
	DECLARE var_detele_idstr LONGTEXT DEFAULT '';-- 拼接id字符串
	-- 	声明游标
	DECLARE cur CURSOR FOR 
	-- 作用在哪个语句
	SELECT 
		count(*) as total,
		sum(service_cost) as cost,
		org_code,
		class_id
		FROM
		sm_car_order 
		WHERE order_create_time 
		LIKE CONCAT(left(var_date,4),'-',mid(var_date,5,2),'-',right(var_date,2),'%') 
		GROUP BY org_code,class_id;
		-- 设置结束标志
	-- 这条语句定义了一个continue handler
	-- 	他是在条件出现时被执行的代码，
	-- 	这里，他指出当SQLSTATE'02000'出现时，SET done=1。SQLSTATE '02000'是一个未找到条件，
	-- 	当REPEAT由于没有更多的行供循环而不能继续时，出现这个条件
		DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done =1;
	-- 声明异常处理
	DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
	BEGIN
		-- 捕获异常信息
		GET DIAGNOSTICS CONDITION 1
		ERROR_CODE = RETURNED_SQLSTATE,ERROR_MSG=MESSAGE_TEXT;
		SET ERROR_PROCEDURE_INFO=CONCAT('{"PROCEDURE_NAME":','"handleOrderStaticsDay"','"VAR_DATE":',var_date,'}');
	-- 把异常捕获，并写入错误日志表中
	insert into sm_car_procedure_error_log(error_code,error_msg,error_procedure_info,error_create_time)
	VALUES (ERROR_CODE,ERROR_MSG,ERROR_PROCEDURE_INFO,DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s'));
	SET done =1;
	END;


	
	-- 统计计数置为0
	SET count_num=0;
	-- 打开游标
	OPEN cur;
	-- 使用repeat循环语法
	REPEAT
	
-- 		批量读取数据到指定变量
		FETCH CUR INTO var_total,var_cost,var_org_code,var_class_id;
		 IF done<>1&&var_total<>0 then
				-- 	进行逻辑操v作
			-- 	INSERT INTO sm_car_statistics_day (day_org_code,day_class_id,day_cost,day_total,day_time) VALUES(var_org_code,var_class_id,var_cost,var_total,var_date);
			-- PREPARE语句准备好一条SQL语句，并分配给这条SQL语句一个名字供之后调用。准备好的SQL语句通过EXECUTE命令执行，通过DEALLOCATE PREPARE命令释放掉
		-- 判断批量提交的次数
			IF var_update_insert_count%var_update_insert_num = 0 THEN
		
				-- SET var_insert_str=SUBSTRING(var_insert_str,0,LENGTH(var_insert_str)-1);
				SET @var_insert_sql=CONCAT('REPLACE INTO sm_car_statistics_day(',
				'day_id,day_org_code,day_class_id,day_cost,day_total,day_time',
				') VALUES ',var_insert_str,'(0,0,0,0,0.00,0)');
				SELECT @var_insert_sql;
				PREPARE insert_value_stmt1 FROM @var_insert_sql;
				EXECUTE insert_value_stmt1;
				-- 删除id为‘0’的数据
				DELETE FROM sm_car_statistics_day WHERE day_org_code='0';
				-- 重置操作字符串
				SET var_insert_str='';
			END IF;
			SET var_insert_str=CONCAT(var_insert_str,'("",','\'',var_org_code,'\',\'',var_class_id,'\',\'',var_cost,'\',\'',var_total,'\',\'',var_date,'\')',',');
			-- 计数器+1
			SET var_update_insert_count=var_update_insert_count + 1;
		
		END IF;
			SET count_num=count_num+1;
		 IF done=1&&var_total<>0 THEN
			
		 	
				SET @var_insert_sql=CONCAT('REPLACE INTO sm_car_statistics_day(',
				'day_id,day_org_code,day_class_id,day_cost,day_total,day_time',
				') VALUES ',var_insert_str,'(0,0,0,0,0.00,0)');
				PREPARE insert_value_stmt1 FROM @var_insert_sql;
				EXECUTE insert_value_stmt1;
				-- 删除id为‘0’的数据
				DELETE FROM sm_car_statistics_day WHERE day_org_code='0';
					-- 循环结束条件

		 END IF;

	-- 循环结束条件
	UNTIL done
	END REPEAT;
	-- 关闭游标
	CLOSE cur;
	
END