记一次工作中解决实际问题使用的MySql存储过程

这里简单记录下本次业务解决过程中使用mysql的存储过程，从里面可以看到循环、分支控制，视图创建，变量定义及赋值，2个游标定义及使用相关的过程，存储过程定义及调用。

统计的基表：

CREATE TABLE `assets_hist` (  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '自增',  `purchase_id` bigint(20) DEFAULT NULL COMMENT '购买资产Id',  `skyroam_id` bigint(20) DEFAULT NULL COMMENT '用户名称',  `group_id` bigint(20) DEFAULT NULL COMMENT '客户集团',  `group_name` varchar(50) DEFAULT NULL COMMENT '集团名称',  `prod_id` bigint(20) DEFAULT NULL COMMENT '产品Id',  `prod_name` varchar(50) DEFAULT NULL COMMENT '产品名称',  `priority` int(11) DEFAULT NULL COMMENT '资产使用优先级',  `sku_profile` varchar(512) DEFAULT NULL COMMENT '销售品(SKU)',  `subs_id` bigint(20) DEFAULT NULL COMMENT '订单Id',  `subs_time` datetime DEFAULT NULL COMMENT '购买时间',  `subs_exptime` datetime DEFAULT NULL COMMENT '订单失效时间',  `type` int(11) DEFAULT NULL COMMENT '类型 0：购买1：激活2：退订',  `subs_count` int(11) DEFAULT NULL COMMENT '购买个数',  `effective_count` int(11) DEFAULT NULL COMMENT '有效个数',  `affect_count` int(11) DEFAULT NULL COMMENT '本次影响数量（激活标识为-1，退订标识为-N，购买标识为+N）',  `proc_code` varchar(20) DEFAULT NULL COMMENT '接口命令码',  `cons_order_id` bigint(20) DEFAULT NULL COMMENT '确认使用Id',  `cons_sn` varchar(32) DEFAULT NULL COMMENT '终端的sn',  `cons_mcc` int(11) DEFAULT NULL COMMENT '确认使用发生国家',  `effect_time` datetime DEFAULT NULL COMMENT '生效时间',  `expire_time` datetime DEFAULT NULL COMMENT '失效时间',  `data_usage` bigint(20) DEFAULT NULL COMMENT '本次确认使用的累积流量 byte',  `data_speed` bigint(20) DEFAULT NULL COMMENT '限速信息 Bit/s',  `create_date` datetime DEFAULT NULL COMMENT '创建时间',  `update_date` datetime DEFAULT NULL COMMENT '最后修改时间',  `version` int(11) DEFAULT NULL COMMENT '数据版本号',  `sku_id` int(11) DEFAULT NULL,  PRIMARY KEY (`id`),  KEY `skyroam_id` (`skyroam_id`),  KEY `group_id` (`group_id`),  KEY `purchase_id` (`purchase_id`),  KEY `effect_time` (`effect_time`),  KEY `prod_subs_id` (`subs_id`,`prod_id`),  KEY `index_create_date` (`create_date`),  KEY `index_prod_id` (`prod_id`)) ENGINE=InnoDB AUTO_INCREMENT=7095 DEFAULT CHARSET=utf8;

存储过程：

DELIMITER $$-- USE `assets`$$-- SELECT LAST_INSERT_ID() from `assets_hist`-- 计算统计日期 -1为昨天  CREATE OR REPLACE VIEW v_yesterday AS SELECT CURDATE() + INTERVAL -1 DAY  AS tjrq FROM DUAL $$-- 计算各group的开始和结束日期CREATE OR REPLACE VIEW v_sync_group ASSELECT  group_id,group_name,IFNULL(time_zone, '-8') time_zone,    DATE_ADD(tjrq,INTERVAL IFNULL(b.time_zone, '-8') HOUR) tjstart,    DATE_ADD( DATE_ADD(tjrq, INTERVAL 1 DAY),INTERVAL IFNULL(b.time_zone, '-8') HOUR) tjend,    tjrqFROM sync_group b,v_yesterday a $$-- 插入本次任务基础数据DELETE FROM sync_user_product_day WHERE DATE=(SELECT tjrq FROM v_yesterday) $$INSERT INTO sync_user_product_day(skyroam_id,product_id,group_id,DATE)     SELECT a.skyroam_id,a.prod_id,a.group_id,b.tjrq     FROM (SELECT DISTINCT skyroam_id,prod_id,group_id FROM assets_hist) a    INNER JOIN v_sync_group b ON a.group_id=b.group_id $$-- 任务辅助表DROP TABLE IF EXISTS day_task_assist1 $$CREATE  TABLE day_task_assist1 AS SELECT id,skyroam_id,group_id,prod_id,subs_id,TYPE,subs_count,effective_count,affect_count,cons_sn,create_date FROM assets_hist WHERE 1=2 $$DROP TABLE IF EXISTS day_task_assist2 $$CREATE  TABLE day_task_assist2 AS SELECT * FROM `sync_user_product_day` WHERE 1=2 $$-- 执行过程处理今天有数据的用户DROP PROCEDURE IF EXISTS `sp_stat_day`$$CREATE PROCEDURE sp_stat_day()BEGIN    DECLARE v_skyroam_id,v_skyroam_id_bak BIGINT(20);    DECLARE v_group_id,v_group_id_bak BIGINT(20);    DECLARE v_prod_id,v_prod_id_bak BIGINT(20);    DECLARE v_time_begin,v_time_end DATETIME;    DECLARE v_date DATE;    DECLARE v_null_sn_cnt,v_notnull_sn_count INT DEFAULT 0;    DECLARE v_minid_nullsn INT DEFAULT 0;    DECLARE v_first_notnull_sn VARCHAR(32) DEFAULT NULL;        DECLARE v_subs_count INT DEFAULT 0;    DECLARE v_effective_count INT DEFAULT 0;    DECLARE v_affect_count INT DEFAULT 0;    DECLARE v_sn VARCHAR(32) DEFAULT NULL;          DECLARE is_over INT DEFAULT 0;    DECLARE cur_skyroam_day CURSOR FOR         SELECT skyroam_id,group_id,product_id FROM sync_user_product_day WHERE DATE=(SELECT tjrq FROM v_yesterday);    DECLARE cur_upd_day CURSOR FOR         SELECT skyroam_id,product_id,group_id,purchase,remain_amount,cons_count,sn FROM day_task_assist2 ORDER BY skyroam_id,product_id,group_id;    DECLARE CONTINUE HANDLER FOR NOT FOUND SET is_over = 1;    OPEN cur_skyroam_day;    FETCH cur_skyroam_day INTO v_skyroam_id,v_group_id,v_prod_id;    outer1:WHILE is_over=0 DO        -- 保存任务的时间范围        SELECT tjstart,tjend INTO v_time_begin,v_time_end FROM v_sync_group WHERE group_id=v_group_id;        SELECT tjrq INTO v_date FROM v_yesterday;        -- 转移数据        INSERT INTO day_task_assist1 SELECT id,skyroam_id,group_id,prod_id,subs_id,TYPE,subs_count,effective_count,affect_count,cons_sn,create_date FROM assets_hist a        WHERE skyroam_id=v_skyroam_id AND group_id=v_group_id AND prod_id=v_prod_id AND create_date >=v_time_begin AND create_date<v_time_end;        -- 处理sn为空的        SELECT COUNT(1) INTO v_null_sn_cnt FROM day_task_assist1 WHERE cons_sn IS NULL;        SELECT COUNT(1) INTO v_notnull_sn_count FROM day_task_assist1 WHERE cons_sn IS NOT NULL;        -- 用户今天没数据则取前天数据        IF v_null_sn_cnt+v_notnull_sn_count=0 THEN            SELECT remain_amount,sn INTO v_effective_count,v_sn FROM sync_user_product_day WHERE skyroam_id=v_skyroam_id AND product_id=v_prod_id AND group_id=v_group_id AND DATE=(v_date + INTERVAL -1 DAY);            UPDATE sync_user_product_day SET purchase=0,remain_amount=v_effective_count,cons_count=0,sn=v_sn WHERE skyroam_id=v_skyroam_id AND product_id=v_prod_id AND group_id=v_group_id AND DATE=v_date;            FETCH cur_skyroam_day INTO v_skyroam_id,v_group_id,v_prod_id;            ITERATE outer1;        END IF;        WHILE v_null_sn_cnt>0 AND v_notnull_sn_count>0 DO            SELECT MIN(id) INTO v_minid_nullsn FROM day_task_assist1 WHERE cons_sn IS NULL;            SELECT cons_sn INTO v_first_notnull_sn FROM day_task_assist1 WHERE cons_sn IS NOT NULL AND id>v_minid_nullsn LIMIT 1;            UPDATE day_task_assist1 SET cons_sn=v_first_notnull_sn WHERE id = v_minid_nullsn;            SELECT COUNT(1) INTO v_null_sn_cnt FROM day_task_assist1 WHERE cons_sn IS NULL;            -- SELECT COUNT(1) INTO v_notnull_sn_count FROM day_task_assist1 WHERE cons_sn IS NOT NULL;            -- set v_null_sn_cnt=0;        END WHILE;        -- 初步统计        INSERT INTO day_task_assist2(skyroam_id,product_id,group_id,sn,purchase,cons_count,remain_amount,DATE)            SELECT m.skyroam_id,m.prod_id,m.group_id,m.sn,m.rechargeAmount,m.usageAmount,n.effective_cout,v_date FROM             (SELECT skyroam_id,prod_id,group_id,cons_sn sn,                SUM((CASE TYPE WHEN 0 THEN affect_count ELSE 0 END)) + SUM((CASE TYPE WHEN 2 THEN affect_count ELSE 0 END))  rechargeAmount,                SUM((CASE TYPE WHEN 1 THEN -1*affect_count ELSE 0 END))  usageAmount                FROM day_task_assist1 a GROUP BY cons_sn,skyroam_id,prod_id,group_id            )m LEFT JOIN             (SELECT skyroam_id,group_id,prod_id,cons_sn sn,effective_cout FROM                (SELECT skyroam_id,group_id,prod_id,SUM(effective_count) effective_cout                 FROM assets_hist                 WHERE id IN (SELECT MAX(id) FROM assets_hist WHERE prod_id=v_prod_id AND group_id=v_group_id AND skyroam_id=v_skyroam_id GROUP BY subs_id)                )xx                LEFT JOIN (SELECT cons_sn FROM assets_hist WHERE id=(SELECT MAX(id) FROM assets_hist WHERE prod_id=v_prod_id AND group_id=v_group_id AND skyroam_id=v_skyroam_id)) yy ON 1=1            ) n ON m.skyroam_id=n.skyroam_id AND m.sn=n.sn;                 -- 初始化        TRUNCATE TABLE day_task_assist1;        FETCH cur_skyroam_day INTO v_skyroam_id,v_group_id,v_prod_id;    END WHILE;    CLOSE cur_skyroam_day;    -- 更新    SET is_over=0;    OPEN cur_upd_day;    FETCH cur_upd_day INTO v_skyroam_id,v_group_id,v_prod_id,v_subs_count,v_effective_count,v_affect_count,v_sn;    WHILE is_over=0 DO        IF v_skyroam_id=v_skyroam_id_bak AND v_prod_id=v_prod_id_bak AND v_group_id=v_group_id_bak THEN            INSERT INTO sync_user_product_day(skyroam_id,product_id,group_id,purchase,remain_amount,cons_count,sn,DATE)VALUES(v_skyroam_id,v_prod_id,v_group_id,v_subs_count,v_effective_count,v_affect_count,v_sn,v_date);        ELSE            UPDATE sync_user_product_day SET purchase=v_subs_count,remain_amount=v_effective_count,cons_count=v_affect_count,sn=v_sn WHERE skyroam_id=v_skyroam_id AND product_id=v_prod_id AND group_id=v_group_id AND DATE=v_date;        END IF;        SET v_skyroam_id_bak=v_skyroam_id,v_group_id_bak=v_group_id,v_prod_id_bak=v_prod_id;        FETCH cur_upd_day INTO v_skyroam_id,v_group_id,v_prod_id,v_subs_count,v_effective_count,v_affect_count,v_sn;    END WHILE;    CLOSE cur_upd_day;END $$DELIMITER ; CALL sp_stat_day();