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

最新推荐文章于 2021-03-26 10:56:44 发布

stillearn

最新推荐文章于 2021-03-26 10:56:44 发布

阅读量634

点赞数

分类专栏：中级进阶——数据库相关文章标签： mysql 存储过程游标

本文链接：https://blog.csdn.net/shizhengxing/article/details/50624379

版权

中级进阶——数据库相关专栏收录该内容

4 篇文章 0 订阅

订阅专栏

这里简单记录下本次业务解决过程中使用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 AS
SELECT  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,
    tjrq
FROM 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();