简单的Mysql统计存储过程

简单的实现了单表的日统计过程，涉及存储过程中的异常捕获，异常分析。目的是熟悉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