2.4_3 MySQL 存储过程实战场景

GroupiesM

已于 2022-04-29 09:18:05 修改

阅读量3.9k

点赞数 1

分类专栏： # 2.4 Mysql 文章标签： mysql java 数据库

于 2021-03-17 18:31:35 首次发布

本文链接：https://blog.csdn.net/qq_43529621/article/details/114918180

版权

2.4 Mysql 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

前言

之前在Oracle练习过一次存储过程的使用 记一次Oracle存储过程练习 ，并没有在工作中试用过。这次是第一次在工作实战中使用存储过程，遇到了很多问题，属于面向百度开发。这次数据库类型换成了MySQL5.7，开发存储过程的环境为Navicat 12.1.10。最开始是没有使用存储过程的，使用的Java拼接处理（代码已经删掉，就不放了），200条测试数据执行了约一分钟，效率过低，采用存储大约0.07秒，执行效率是java处理的857倍。
据某位大佬告诉我，MySQL存储过程的功能不如Oralce强大，但也基本够用了，下面先简单介绍一下存储过程的需求场景（涉及敏感信息已脱敏处理）：

一、需求说明

按照UI原型设计表，上游按照表结构提供数据，分析各类型及数量，展现BI页面效果如下：

这里的苹果，香蕉，西瓜，葡萄的类型是动态表头（这里的苹果、香蕉可能是其他值，需要从提供的数据中获取），食品类，但生活用品类这两种类别是可以确定的

二、解决方案

2.1 设计表结构

由于是动态表头，所以不能够设计成固定的字段来体现数量

所以表结构要改造成这样：

简单介绍一下命名规则

YEAR_1：年，由于YEAR是关键字，加后缀方便使用SELECT YEAR_1，如果字段叫YEAR引用字段需要SELECT “YEAR”;
ORG：organization缩写，表示企业
YEAR_1 和 ORG基本上每个表都有，统一命名方便开发

WD01：维度01
DL01：度量01

需要上游提供数据的表的建表语句及模拟数据

zyqyxczg_a004 商品销量信息主表

-- ----------------------------
-- Table structure for zyqyxczg_a004
-- ----------------------------
DROP TABLE IF EXISTS `zyqyxczg_a004`;
CREATE TABLE `zyqyxczg_a004`  (
  `TB_TIME` date NULL DEFAULT NULL COMMENT '填报时间(YYYY-MM-DD)',
  `TB_ORDER` decimal(20, 0) NULL DEFAULT NULL COMMENT '填报数据指定排序序号',
  `YEAR_1` decimal(20, 0) NULL DEFAULT NULL COMMENT '年',
  `ORG` varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '企业简称',
  `WD01` varchar(1000) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '商品分类',
  `WD02` varchar(1000) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '商品名称',
  `DL01` decimal(20, 0) NULL DEFAULT NULL COMMENT '商品销量'
) ENGINE = MyISAM CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '商品销量信息主表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of zyqyxczg_a004
-- ----------------------------
INSERT INTO `zyqyxczg_a004` VALUES ('2020-03-04', 1, 2018, '淘宝', '食品', '苹果', 28);
INSERT INTO `zyqyxczg_a004` VALUES ('2020-03-04', 2, 2018, '淘宝', '食品类', '香蕉', 98);
INSERT INTO `zyqyxczg_a004` VALUES ('2020-03-04', 3, 2018, '淘宝', '生活用品类', '收纳柜', 12);
INSERT INTO `zyqyxczg_a004` VALUES ('2020-03-04', 4, 2018, '淘宝', '生活用品类', '地毯', 12);
INSERT INTO `zyqyxczg_a004` VALUES ('2020-03-04', 5, 2018, '拼多多', '食品', '苹果', 75);
INSERT INTO `zyqyxczg_a004` VALUES ('2020-03-04', 6, 2018, '拼多多', '食品', '西瓜', 13);
INSERT INTO `zyqyxczg_a004` VALUES ('2020-03-04', 7, 2018, '拼多多', '生活用品类', '收纳柜', 64);
INSERT INTO `zyqyxczg_a004` VALUES ('2020-03-04', 8, 2018, '拼多多', '生活用品类', '肥皂盒', 83);
INSERT INTO `zyqyxczg_a004` VALUES ('2020-03-04', 9, 2018, '京东', '食品', '香蕉', 79);
INSERT INTO `zyqyxczg_a004` VALUES ('2020-03-04', 10, 2018, '京东', '食品', '葡萄', 54);
INSERT INTO `zyqyxczg_a004` VALUES ('2020-03-04', 11, 2018, '京东', '生活用品类', '晾衣架', 69);
INSERT INTO `zyqyxczg_a004` VALUES ('2020-03-04', 12, 2018, '京东', '生活用品类', '地毯', 69);

zyqyxczg_a004 商品销量信息主表，通过存储过程处理数据后存放到以下四张表：

1.zyqyxczg_a004_b001_type 食品类型子表
2.zyqyxczg_a004_b001_result 食品类各商品销量子表
3.zyqyxczg_a004_b002_type 生活用品类型子表
4.zyqyxczg_a004_b002_result 生活用品类各商品销量子表

-- ----------------------------
-- 1.Table structure for zyqyxczg_a004_b001_type
-- ----------------------------
DROP TABLE IF EXISTS `zyqyxczg_a004_b001_type`;
CREATE TABLE `zyqyxczg_a004_b001_type`  (
  `type_order` int(11) NULL DEFAULT NULL COMMENT '类型排序',
  `type_list` varchar(96) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '食品名称'
) ENGINE = MyISAM CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '食品类型子表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- 2.Table structure for zyqyxczg_a004_b001_result
-- ----------------------------
DROP TABLE IF EXISTS `zyqyxczg_a004_b001_result`;
CREATE TABLE `zyqyxczg_a004_b001_result`  (
  `PK_RESULT_ORDER` int(11) NOT NULL AUTO_INCREMENT  COMMENT '主键-结果排序',
  `YEAR_1` varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '年',
  `ORG` varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '企业简称',
  `DL01` int(11) NULL DEFAULT NULL COMMENT '度量1',
  `DL02` int(11) NULL DEFAULT NULL COMMENT '度量2',
  `DL03` int(11) NULL DEFAULT NULL COMMENT '度量3',
  `DL04` int(11) NULL DEFAULT NULL COMMENT '度量4',
  `DL05` int(11) NULL DEFAULT NULL COMMENT '度量5',
  `DL06` int(11) NULL DEFAULT NULL COMMENT '度量6',
  `DL07` int(11) NULL DEFAULT NULL COMMENT '度量7',
  `DL08` int(11) NULL DEFAULT NULL COMMENT '度量8',
  `DL09` int(11) NULL DEFAULT NULL COMMENT '度量9',
  `DL10` int(11) NULL DEFAULT NULL COMMENT '度量10',
  `result_sum` int(11) NULL DEFAULT NULL COMMENT '总数',
  PRIMARY KEY (`pk_result_order`) USING BTREE
) ENGINE = MyISAM  CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '食品类各商品销量子表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- 3.Table structure for zyqyxczg_a004_b002_type
-- ----------------------------
DROP TABLE IF EXISTS `zyqyxczg_a004_b002_type`;
CREATE TABLE `zyqyxczg_a004_b002_type`  (
  `type_order` int(11) NULL DEFAULT NULL COMMENT '类型排序',
  `type_list` varchar(96) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '食品类型'
) ENGINE = MyISAM CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '生活用品类型子表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- 4.Table structure for zyqyxczg_a004_b002_result
-- ----------------------------
DROP TABLE IF EXISTS `zyqyxczg_a004_b002_result`;
CREATE TABLE `zyqyxczg_a004_b002_result`  (
  `PK_RESULT_ORDER` int(11) NOT NULL AUTO_INCREMENT  COMMENT '主键-结果排序',
  `YEAR_1` varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '年',
  `ORG` varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '企业简称',
  `DL01` int(11) NULL DEFAULT NULL COMMENT '度量1',
  `DL02` int(11) NULL DEFAULT NULL COMMENT '度量2',
  `DL03` int(11) NULL DEFAULT NULL COMMENT '度量3',
  `DL04` int(11) NULL DEFAULT NULL COMMENT '度量4',
  `DL05` int(11) NULL DEFAULT NULL COMMENT '度量5',
  `DL06` int(11) NULL DEFAULT NULL COMMENT '度量6',
  `DL07` int(11) NULL DEFAULT NULL COMMENT '度量7',
  `DL08` int(11) NULL DEFAULT NULL COMMENT '度量8',
  `DL09` int(11) NULL DEFAULT NULL COMMENT '度量9',
  `DL10` int(11) NULL DEFAULT NULL COMMENT '度量10',
  `result_sum` int(11) NULL DEFAULT NULL COMMENT '总数',
  PRIMARY KEY (`pk_result_order`) USING BTREE
) ENGINE = MyISAM  CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '生活用品类各商品销量子表' ROW_FORMAT = Dynamic;

2.2 整体思路介绍

Step1. 从原表到type表只需要按照以下sql逻辑，按顺序取出

select 
	WD02 as '食品名称'
from 
	cs_groupies_20210317.zyqyxczg_a004
where 
	wd01 ='食品类'
group by 
	WD02 
order by 
-- 保证和UI原型图一致，当出现UI原型图以外的类型时也不会报错
-- 但新类型会按mysql默认排序规则排到某个位置
	FIELD(WD02,'苹果','香蕉','西瓜','葡萄');

------------
苹果
香蕉
西瓜
葡萄

------------
-- 排序里面去掉葡萄测试一下效果
select 
	WD02 as '食品名称'
from 
	cs_groupies_20210317.zyqyxczg_a004
where 
	wd01 ='食品类'
group by 
	WD02 
order by 
	FIELD(WD02,'苹果','香蕉','西瓜');
------------
葡萄
苹果
香蕉
西瓜

Step2. 使用游标遍历取出的结果集，将序号和类型两个值，拼接到INSERT语句的两个参数，插入type表中

-- SQL模式如下所示（序号、商品名称从游标中遍历获取）
INSERT INTO zyqyxczg_a004_b001_type(type_order,type_list)VALUES(序号,商品名称);

Step3. 从原表和type表到result表，同样需要使用存储过程拼接并执行INSERT语句
这里INSERT语句比较复杂，我将其分为三部分【头部、身体、尾部】
- 头部：INSERT部分，这里year_1，org，result_sum是确定的，但是dlxx的数量是不确定的，是从type表遍历得来的；
- 身体：SELECT部分，同样dlxx的字段数量是不确定的，需要从type表遍历得来，这里使用了CASE WHEN进行列转行；
- 尾部：WHERE条件部分，由于year_1和org是联合主键，即同一年同一公司在此表中数据只会有一条，这里year_1和org是从原表遍历来的。

-- 【select和insert部分数据来源】
select type_list from zyqyxczg_a004_b001_type; 
---
苹果
香蕉
西瓜
葡萄

-- 【where部分数据来源】
select distinct 
	t1.year_1,
	t1.org  
from 
	zyqyxczg_a004 T1 
order by
	t1.year_1;
---
2018	京东
2018	拼多多
2018	淘宝


--拼接语句示例
INSERT INTO db_upload_excel.zyqyxczg_a004_b002_result ( year_1, org, dl01, dl02, dl03, dl04,result_sum ) 
SELECT
	year_1,
	org,
	IFNULL( sum( CASE wd02 WHEN '苹果' THEN dl01 END ), 0 ) AS dl01,
	IFNULL( sum( CASE wd02 WHEN '香蕉' THEN dl01 END ), 0 ) AS dl02,
	IFNULL( sum( CASE wd02 WHEN '西瓜' THEN dl01 END ), 0 ) AS dl03,
	IFNULL( sum( CASE wd02 WHEN '葡萄' THEN dl01 END ), 0 ) AS dl04,
	IFNULL( sum( dl01 ), 0 ) AS '总数' 
FROM
	cs_groupies_20210317.zyqyxczg_a004 
WHERE
	wd01 = '食品类' 
	AND year_1 = '2018' 
	AND org = '京东';

整体流程思路示意图：

下面介绍具体开发过程

2.3 存储过程开发

zyqyxczg_a004_b002*和 zyqyxczg_a004_b001* 脚本相似，此处就仅展示b001的脚本。

2.3.1 创建存储过程

再次介绍我是用版本是Navicat 12.1.10 不同版本界面可能会有不同

Step1.右击函数选择新建
Step2.选择过程，完成，存储过程名可以进去再定义
Step3.直接点击完成，调用存储过程需要输入、输出参数时可以在这里添加，我们这里不需要传参，都是查询后直接执行INSERT语句；
Step4.新建的存储过程就长这样
Step5.给出存储过程名 PROCEDURE ``(创建人DEFINER不熟悉的话不建议修改，就用CURRENT_USER)，后点击保存后即可看到新建的存储过程，下面开始编写脚本逻辑

2.3.2 zyqyxczg_a004_b001_type

Step1.存储过程内容

CREATE DEFINER=CURRENT_USER PROCEDURE `zyqyxczg_a004_b001_type`()
BEGIN
	#Routine body goes here...
	    /* 定义变量 */ 
    	DECLARE order_cursor int unsigned DEFAULT 1; -- 用于拼接insert语句
		DECLARE type_cursor varchar(100) character set 'utf8' DEFAULT null ;-- 商品类型，用于拼接insert语句
		DECLARE done int DEFAULT -1;  -- 用于控制循环是否结束： done=-1继续循环，done=1时退出循环
		
		/* 定义游标 */ 
		DECLARE myCursor CURSOR for 
		select 
			WD02 as '食品名称'
		from 
			cs_groupies_20210317.zyqyxczg_a004
		where 
			wd01 ='食品类'
		group by 
			WD02 
		order by 
			FIELD(WD02,'苹果','香蕉','西瓜','葡萄');
 
	/* 当游标到达尾部时，mysql自动设置done=1 */       
    DECLARE continue handler for not found set done=1;    
		
    /* 打开游标 */    
    OPEN myCursor;    
		
		/* 清空目标表 */ 
		truncate table cs_groupies_20210317.zyqyxczg_a004_b001_type;
		
		/* 循环开始 */    
		myLoop: LOOP
			/* 每次从结果集中获取一条数据，将结果集中的值赋值给变量type_cursor */   
			fetch myCursor into type_cursor;
		  -- 游标到达尾部,退出循环  
			if done = 1 then     
				leave myLoop;    
			end if;    
				
		/* do something */    
    		-- 循环插入问题类型到表中
			INSERT INTO cs_groupies_20210317.zyqyxczg_a004_b001_type(type_order,type_list)
			VALUES(order_cursor,type_cursor);
			
			-- 序号从1开始，每次循环+1
			set order_cursor = order_cursor + 1;
			
   		/* 循环结束 */    
		end loop myLoop;   
		
	/* 关闭游标，释放内存 */ 
	CLOSE myCursor;
	
END

Step2.点击保存、运行即可执行存储过程
Step3.在看一下type表内已经有数据了（可以手动Truncate表再执行看看，确认一下）

2.3.3 zyqyxczg_a004_b001_result

Step1.存储过程内容

CREATE DEFINER=CURRENT_USER PROCEDURE `zyqyxczg_a004_b001_result`()
BEGIN
	# Routine body goes here...
    /* myCursorInsertSelect定义变量 */ 
		/* insert部分 */
		-- 【来自查询结果】用于拼接INSERT语句部分 DLXX的数字
		DECLARE insert_id_cursor_1 int unsigned DEFAULT 1; 
		-- 【来自拼接字符串0】上一个变量小于10时前面拼0
		DECLARE insert_id_cursor_2 varchar(200) character set 'utf8' DEFAULT null ; 
		-- 【来自拼接sql】拼接insert第二部分sql
		DECLARE insert_sql varchar(5000) character set 'utf8' DEFAULT 'insert into cs_groupies_20210317.zyqyxczg_a004_b001_result ( year_1,org' ;

		/* select部分 */
		-- 【来自查询结果】用于拼接select部分的食品名称
		DECLARE select_type_cursor varchar(4000) character set 'utf8' DEFAULT null ;
		-- 【来自拼接sql】拼接insert第一部分sql
		DECLARE select_sql varchar(5000) character set 'utf8' DEFAULT 'select \r\n  year_1,\r\n  org';
		
		
    /* myCursorWhere定义变量 */
	/* where部分 */
		-- 【来自查询结果】 用于拼接wherer部分的公司名称
	  DECLARE where_org_cursor varchar(4000) character set 'utf8' DEFAULT null ;
		-- 【来自查询结果】 用于拼接wherer部分的年
	  DECLARE where_year_cursor varchar(4000) character set 'utf8' DEFAULT null ;
		-- 【来自拼接sql】拼接where第二部分sql
		DECLARE where_sql varchar(5000) character set 'utf8' DEFAULT " \r\nwhere \r\n  wd01='食品类' \r\n  and year_1='";
		
		/* 执行sql语句 定义变量 */ 
		DECLARE all_sql_execute varchar(5000) character set 'utf8' DEFAULT null ;
		/* 控制循环 定义变量  -1：继续 ; 0:退出循环  */ 
		DECLARE done int DEFAULT -1; 
		
		/* 定义游标 */
		/* 游标1：select和insert部分 */
		DECLARE myCursorInsertSelect CURSOR for select type_list from zyqyxczg_a004_b001_type; 
		/* 游标2：where部分 */
		DECLARE myCursorWhere CURSOR for 
			select distinct 
				t1.year_1,
				t1.org  
			from 
				zyqyxczg_a004 T1 
			order by
				t1.year_1;
	
	/* 当游标到达尾部时，mysql自动设置done=1 */       
    DECLARE continue handler for not found set done=1;
		
    /* Insert和Select部分 打开游标 */    
    OPEN myCursorInsertSelect;    
		
		-- 先调用type存储过程，获取最新的食品名称列表；
		CALL zyqyxczg_a004_b001_type;
		-- 清空表
		truncate table cs_groupies_20210317.zyqyxczg_a004_b001_result;
		
		/* 循环开始 */    
		myLoop: LOOP    
			fetch myCursorInsertSelect into select_type_cursor;
		  /* 游标到达尾部,退出循环 */  
			if done = 1 then  
				/* 推出循环前在拼接上最后一个字段 ,result_sum */
				set insert_sql = concat(insert_sql,",result_sum ) ");
				set select_sql = concat(select_sql,",\r\n  IFNULL(sum(dl01),0) as '总数'	\r\nfrom \r\n  cs_groupies_20210317.zyqyxczg_a004");	
				-- 测试拼接结果
				 # select all_sql_execute; 
				leave myLoop;    
			end if;    
				
		/* do something */    
			/* 
			 * 如果insert_id_cursor_1<10 
			 * 例如insert_id_cursor_1=5,如则在前面拼接0, 即05 ;如果≥10,则直接取值
			 */
			if insert_id_cursor_1 < 10 THEN
				set insert_id_cursor_2 = concat('0',insert_id_cursor_1);
			else 
				set insert_id_cursor_2 = insert_id_cursor_1;
			end if;
			/* 循环拼接select_sql和insert_sql */
			-- 拼接sql查询部分
			set select_sql = concat(select_sql,",\r\n  IFNULL(sum(case wd02 when '",select_type_cursor,"' then dl01 end),0) as dl",insert_id_cursor_2);
			
			-- 拼接sqlinsert部分
			set insert_sql=concat(insert_sql,",dl",insert_id_cursor_2);
			
		-- 每次循环序号+1
			-- select all_sql_execute;
			set insert_id_cursor_1=insert_id_cursor_1+1;
    /* 循环结束 */    
		end loop myLoop;   
		
		CLOSE myCursorInsertSelect;
		
		
		/* Where条件部分 打开游标 */    
		OPEN myCursorWhere; 
			/* 此时done=1 重新赋值为-1 否则会直接跳出循环*/
			set done=-1;
			/* 循环拼接where条件中的year和org*/
			myLoop: LOOP    
				fetch myCursorWhere into where_year_cursor,where_org_cursor;
				-- 游标到达尾部,退出循环  
				if done = 1 then  
					-- 测试拼接结果
					select @sql; 
					leave myLoop;    
				end if;    
				
				/* do something */    
				-- 循环插入查询结果到表zyqyxczg_a004_b001_result中
				SET @sql=concat(insert_sql,
					"\r\n",
					select_sql,
					where_sql,
					where_year_cursor,
					"' and org='",
					where_org_cursor,
					"';");
				PREPARE all_sql_execute FROM @sql;
				EXECUTE all_sql_execute;
			/* 循环结束 */    
			end loop myLoop;   
		
		CLOSE myCursorWhere;	
			
END

Step2.点击保存、运行即可执行存储过程
Step3. 点击结果1，可以看到在存储过程里打印到控制台的最后一条查询语句（用于调试拼接结果）
Step4.将结果复制出来，并执行Select部分语句，这个查询结果就是存储过程执行的最后一条INSERT数据
Step5.在看一下type表内已经有数据了（可以手动Truncate表再执行看看，确认一下）

2.3.4 一键执行多个存储过程

存储过程内容

CREATE DEFINER=CURRENT_USER PROCEDURE `dynamic_refresh`()
BEGIN
	#Routine body goes here...
		-- 刷新所有动态表头的数据(result里面已经调用了type)
		CALL db_upload_excel.zyqyxczg_a004_b001_result;
		CALL db_upload_excel.zyqyxczg_a004_b002_result;
		
END

2.3.5 定时执行存储过程

定时从2021-03-17 开始每天0点执行一次，获取新的结果

Step1.右击事件，选择新建事件

Step2.在定义中输入执行的sql语句

CALL cs_groupies_20210317.dynamic_refresh

Step3.在计划中选择开始时间和执行频率
Step4.注释可以不填，点击保存，输入定时任务名称
Step5.在事件里可以看到刚才创建的任务

3 Java代码

    public JsonData select04(Map<String, String[]> params) {
        params.forEach((key, values) -> logger.debug("参数:{},值{}", key, values));
        JsonData result = new JsonData();
        String tableNameSql = "SELECT type_list AS \"tableName\" FROM zyqyxczg_a004_b001_type";
        List<Map<String, String>> tableNameDatas = dbUtils.excuteSelect(tableNameSql, index);
        List<String> tableNameList = tableNameDatas.stream().map(data -> data.get("tableName")).collect(Collectors.toList());
        result.setExtendData(commontService.appendSql(params, tableNameList, "zyqyxczg_a004_b001_result"));
        tableNameList.add(0, "序号");
        tableNameList.add(1, "企业名称");
        tableNameList.add("总数");
        result.setOtherList(commontService.assemblyTableNameData(tableNameList));
        return result;
    }

4 表结构及数据导出

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for zyqyxczg_a004
-- ----------------------------
DROP TABLE IF EXISTS `zyqyxczg_a004`;
CREATE TABLE `zyqyxczg_a004`  (
  `TB_TIME` date NULL DEFAULT NULL COMMENT '填报时间(YYYY-MM-DD)',
  `TB_ORDER` decimal(20, 0) NULL DEFAULT NULL COMMENT '填报数据指定排序序号',
  `YEAR_1` decimal(20, 0) NULL DEFAULT NULL COMMENT '年',
  `ORG` varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '企业简称',
  `WD01` varchar(1000) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '商品分类',
  `WD02` varchar(1000) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '商品名称',
  `DL01` decimal(20, 0) NULL DEFAULT NULL COMMENT '商品销量'
) ENGINE = MyISAM CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '商品销量信息表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of zyqyxczg_a004
-- ----------------------------
INSERT INTO `zyqyxczg_a004` VALUES ('2020-03-04', 1, 2018, '淘宝', '食品类', '苹果', 28);
INSERT INTO `zyqyxczg_a004` VALUES ('2020-03-04', 2, 2018, '淘宝', '食品类', '香蕉', 98);
INSERT INTO `zyqyxczg_a004` VALUES ('2020-03-04', 3, 2018, '淘宝', '生活用品类', '收纳柜', 12);
INSERT INTO `zyqyxczg_a004` VALUES ('2020-03-04', 4, 2018, '淘宝', '生活用品类', '地毯', 12);
INSERT INTO `zyqyxczg_a004` VALUES ('2020-03-04', 5, 2018, '拼多多', '食品类', '苹果', 75);
INSERT INTO `zyqyxczg_a004` VALUES ('2020-03-04', 6, 2018, '拼多多', '食品类', '西瓜', 13);
INSERT INTO `zyqyxczg_a004` VALUES ('2020-03-04', 7, 2018, '拼多多', '生活用品类', '收纳柜', 64);
INSERT INTO `zyqyxczg_a004` VALUES ('2020-03-04', 8, 2018, '拼多多', '生活用品类', '肥皂盒', 83);
INSERT INTO `zyqyxczg_a004` VALUES ('2020-03-04', 9, 2018, '京东', '食品类', '香蕉', 79);
INSERT INTO `zyqyxczg_a004` VALUES ('2020-03-04', 10, 2018, '京东', '食品类', '葡萄', 54);
INSERT INTO `zyqyxczg_a004` VALUES ('2020-03-04', 11, 2018, '京东', '生活用品类', '晾衣架', 69);
INSERT INTO `zyqyxczg_a004` VALUES ('2020-03-04', 12, 2018, '京东', '生活用品类', '地毯', 69);

-- ----------------------------
-- Table structure for zyqyxczg_a004_b001_result
-- ----------------------------
DROP TABLE IF EXISTS `zyqyxczg_a004_b001_result`;
CREATE TABLE `zyqyxczg_a004_b001_result`  (
  `PK_RESULT_ORDER` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键-结果排序',
  `YEAR_1` varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '年',
  `ORG` varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '企业简称',
  `DL01` int(11) NULL DEFAULT NULL COMMENT '度量1',
  `DL02` int(11) NULL DEFAULT NULL COMMENT '度量2',
  `DL03` int(11) NULL DEFAULT NULL COMMENT '度量3',
  `DL04` int(11) NULL DEFAULT NULL COMMENT '度量4',
  `DL05` int(11) NULL DEFAULT NULL COMMENT '度量5',
  `DL06` int(11) NULL DEFAULT NULL COMMENT '度量6',
  `DL07` int(11) NULL DEFAULT NULL COMMENT '度量7',
  `DL08` int(11) NULL DEFAULT NULL COMMENT '度量8',
  `DL09` int(11) NULL DEFAULT NULL COMMENT '度量9',
  `DL10` int(11) NULL DEFAULT NULL COMMENT '度量10',
  `result_sum` int(11) NULL DEFAULT NULL COMMENT '总数',
  PRIMARY KEY (`PK_RESULT_ORDER`) USING BTREE
) ENGINE = MyISAM AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '食品类各商品销量子表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of zyqyxczg_a004_b001_result
-- ----------------------------
INSERT INTO `zyqyxczg_a004_b001_result` VALUES (1, '2018', '京东', 0, 79, 0, 54, NULL, NULL, NULL, NULL, NULL, NULL, 133);
INSERT INTO `zyqyxczg_a004_b001_result` VALUES (2, '2018', '拼多多', 75, 0, 13, 0, NULL, NULL, NULL, NULL, NULL, NULL, 88);
INSERT INTO `zyqyxczg_a004_b001_result` VALUES (3, '2018', '淘宝', 28, 98, 0, 0, NULL, NULL, NULL, NULL, NULL, NULL, 126);

-- ----------------------------
-- Table structure for zyqyxczg_a004_b001_type
-- ----------------------------
DROP TABLE IF EXISTS `zyqyxczg_a004_b001_type`;
CREATE TABLE `zyqyxczg_a004_b001_type`  (
  `type_order` int(11) NULL DEFAULT NULL COMMENT '类型排序',
  `type_list` varchar(96) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '食品名称'
) ENGINE = MyISAM CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '食品类型子表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of zyqyxczg_a004_b001_type
-- ----------------------------
INSERT INTO `zyqyxczg_a004_b001_type` VALUES (1, '苹果');
INSERT INTO `zyqyxczg_a004_b001_type` VALUES (2, '香蕉');
INSERT INTO `zyqyxczg_a004_b001_type` VALUES (3, '西瓜');
INSERT INTO `zyqyxczg_a004_b001_type` VALUES (4, '葡萄');

-- ----------------------------
-- Table structure for zyqyxczg_a004_b002_result
-- ----------------------------
DROP TABLE IF EXISTS `zyqyxczg_a004_b002_result`;
CREATE TABLE `zyqyxczg_a004_b002_result`  (
  `PK_RESULT_ORDER` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键-结果排序',
  `YEAR_1` varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '年',
  `ORG` varchar(500) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '企业简称',
  `DL01` int(11) NULL DEFAULT NULL COMMENT '度量1',
  `DL02` int(11) NULL DEFAULT NULL COMMENT '度量2',
  `DL03` int(11) NULL DEFAULT NULL COMMENT '度量3',
  `DL04` int(11) NULL DEFAULT NULL COMMENT '度量4',
  `DL05` int(11) NULL DEFAULT NULL COMMENT '度量5',
  `DL06` int(11) NULL DEFAULT NULL COMMENT '度量6',
  `DL07` int(11) NULL DEFAULT NULL COMMENT '度量7',
  `DL08` int(11) NULL DEFAULT NULL COMMENT '度量8',
  `DL09` int(11) NULL DEFAULT NULL COMMENT '度量9',
  `DL10` int(11) NULL DEFAULT NULL COMMENT '度量10',
  `result_sum` int(11) NULL DEFAULT NULL COMMENT '总数',
  PRIMARY KEY (`PK_RESULT_ORDER`) USING BTREE
) ENGINE = MyISAM AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '生活用品类各商品销量子表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for zyqyxczg_a004_b002_type
-- ----------------------------
DROP TABLE IF EXISTS `zyqyxczg_a004_b002_type`;
CREATE TABLE `zyqyxczg_a004_b002_type`  (
  `type_order` int(11) NULL DEFAULT NULL COMMENT '类型排序',
  `type_list` varchar(96) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '食品类型'
) ENGINE = MyISAM CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '生活用品类型子表' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Procedure structure for dynamic_refresh
-- ----------------------------
DROP PROCEDURE IF EXISTS `dynamic_refresh`;
delimiter ;;
CREATE PROCEDURE `dynamic_refresh`()
BEGIN
	#Routine body goes here...
		-- 刷新所有动态表头的数据(result里面已经调用了type)
		CALL db_upload_excel.zyqyxczg_a004_b001_result;
		CALL db_upload_excel.zyqyxczg_a004_b002_result;
		
END
;;
delimiter ;

-- ----------------------------
-- Procedure structure for zyqyxczg_a004_b001_result
-- ----------------------------
DROP PROCEDURE IF EXISTS `zyqyxczg_a004_b001_result`;
delimiter ;;
CREATE PROCEDURE `zyqyxczg_a004_b001_result`()
BEGIN
	# Routine body goes here...
    /* myCursorInsertSelect定义变量 */ 
		/* insert部分 */
			-- 【来自查询结果】用于拼接INSERT语句部分 DLXX的数字
		DECLARE insert_id_cursor_1 int unsigned DEFAULT 1; 
			-- 【来自拼接字符串0】上一个变量小于10时前面拼0
		DECLARE insert_id_cursor_2 varchar(200) character set 'utf8' DEFAULT null ; 
			-- 【来自拼接sql】拼接insert第二部分sql
		DECLARE insert_sql varchar(5000) character set 'utf8' DEFAULT 'insert into cs_groupies_20210317.zyqyxczg_a004_b001_result ( year_1,org' ;

		/* select部分 */
			-- 【来自查询结果】用于拼接select部分的食品名称
		DECLARE select_type_cursor varchar(4000) character set 'utf8' DEFAULT null ;
			-- 【来自拼接sql】拼接insert第一部分sql
		DECLARE select_sql varchar(5000) character set 'utf8' DEFAULT 'select \r\n  year_1,\r\n  org';
		
		
    /* myCursorWhere定义变量 */
		/* where部分 */
			-- 【来自查询结果】 用于拼接wherer部分的公司名称
	  DECLARE where_org_cursor varchar(4000) character set 'utf8' DEFAULT null ;
			-- 【来自查询结果】 用于拼接wherer部分的年
	  DECLARE where_year_cursor varchar(4000) character set 'utf8' DEFAULT null ;
			-- 【来自拼接sql】拼接where第二部分sql
		DECLARE where_sql varchar(5000) character set 'utf8' DEFAULT " \r\nwhere \r\n  wd01='食品类' \r\n  and year_1='";
		
		/* 执行sql语句 定义变量 */ 
		DECLARE all_sql_execute varchar(5000) character set 'utf8' DEFAULT null ;
		/* 控制循环 定义变量  -1：继续 ; 0:退出循环  */ 
		DECLARE done int DEFAULT -1; 
		
		/* 定义游标 */
		/* 游标1：select和insert部分 */
		DECLARE myCursorInsertSelect CURSOR for select type_list from zyqyxczg_a004_b001_type; 
		/* 游标2：where部分 */
		DECLARE myCursorWhere CURSOR for 
			select distinct 
				t1.year_1,
				t1.org  
			from 
				zyqyxczg_a004 T1 
			order by
				t1.year_1;
	
		/* 当游标到达尾部时，mysql自动设置done=1 */       
    DECLARE continue handler for not found set done=1;
		
    /* Insert和Select部分 打开游标 */    
    OPEN myCursorInsertSelect;    
		
		-- 动态获取最新问题类型
		CALL zyqyxczg_a004_b001_type;
		-- 清空表
		truncate table cs_groupies_20210317.zyqyxczg_a004_b001_result;
		
		/* 循环开始 */    
		myLoop: LOOP    
			fetch myCursorInsertSelect into select_type_cursor;
		  /* 游标到达尾部,退出循环 */  
			if done = 1 then  
				/* 推出循环前在拼接上最后一个字段 ,result_sum */
				set insert_sql = concat(insert_sql,",result_sum ) ");
				set select_sql = concat(select_sql,",\r\n  IFNULL(sum(dl01),0) as '总数'	\r\nfrom \r\n  cs_groupies_20210317.zyqyxczg_a004");	
				-- 测试拼接结果
				 # select all_sql_execute; 
				leave myLoop;    
			end if;    
				
		/* do something */    
			/* 
			 * 如果insert_id_cursor_1<10 
			 * 例如insert_id_cursor_1=5,如则在前面拼接0, 即05 ;如果≥10,则直接取值
			 */
			if insert_id_cursor_1 < 10 THEN
				set insert_id_cursor_2 = concat('0',insert_id_cursor_1);
			else 
				set insert_id_cursor_2 = insert_id_cursor_1;
			end if;
			/* 循环拼接select_sql和insert_sql */
			-- 拼接sql查询部分
			set select_sql = concat(select_sql,",\r\n  IFNULL(sum(case wd02 when '",select_type_cursor,"' then dl01 end),0) as dl",insert_id_cursor_2);
			
			-- 拼接sqlinsert部分
			set insert_sql=concat(insert_sql,",dl",insert_id_cursor_2);
			
		-- 每次循环序号+1
			-- select all_sql_execute;
			set insert_id_cursor_1=insert_id_cursor_1+1;
    /* 循环结束 */    
		end loop myLoop;   
		
		CLOSE myCursorInsertSelect;
		
		
		/* Where条件部分 打开游标 */    
		OPEN myCursorWhere; 
			/* 此时done=1 重新赋值为-1 否则会直接跳出循环*/
			set done=-1;
			/* 循环拼接where条件中的year和org*/
			myLoop: LOOP    
				fetch myCursorWhere into where_year_cursor,where_org_cursor;
				-- 游标到达尾部,退出循环  
				if done = 1 then  
					-- 测试拼接结果
					select @sql; 
					leave myLoop;    
				end if;    
				
				/* do something */    
				-- 循环插入查询结果到表zyqyxczg_a004_b001_result中
				SET @sql=concat(insert_sql,"\r\n",select_sql,where_sql,where_year_cursor,"' and org='",where_org_cursor,"';");
				PREPARE all_sql_execute FROM @sql;
				EXECUTE all_sql_execute;
			/* 循环结束 */    
			end loop myLoop;   
		
		CLOSE myCursorWhere;	
			
END
;;
delimiter ;

-- ----------------------------
-- Procedure structure for zyqyxczg_a004_b001_type
-- ----------------------------
DROP PROCEDURE IF EXISTS `zyqyxczg_a004_b001_type`;
delimiter ;;
CREATE PROCEDURE `zyqyxczg_a004_b001_type`()
BEGIN
	#Routine body goes here...
	    /* 定义变量 */ 
    	DECLARE order_cursor int unsigned DEFAULT 1; -- 用于拼接insert语句
		DECLARE type_cursor varchar(100) character set 'utf8' DEFAULT null ;-- 商品类型，用于拼接insert语句
		DECLARE done int DEFAULT -1;  -- 用于控制循环是否结束： done=-1继续循环，done=1时退出循环
		
		/* 定义游标 */ 
		DECLARE myCursor CURSOR for 
		select 
			WD02 as '食品名称'
		from 
			cs_groupies_20210317.zyqyxczg_a004
		where 
			wd01 ='食品类'
		group by 
			WD02 
		order by 
			FIELD(WD02,'苹果','香蕉','西瓜','葡萄');
 
	/* 当游标到达尾部时，mysql自动设置done=1 */       
    DECLARE continue handler for not found set done=1;    
		
    /* 打开游标 */    
    OPEN myCursor;    
		
		/* 清空目标表 */ 
		truncate table cs_groupies_20210317.zyqyxczg_a004_b001_type;
		
		/* 循环开始 */    
		myLoop: LOOP
			/* 每次从结果集中获取一条数据，将结果集中的值赋值给变量type_cursor */   
			fetch myCursor into type_cursor;
		  -- 游标到达尾部,退出循环  
			if done = 1 then     
				leave myLoop;    
			end if;    
				
		/* do something */    
    		-- 循环插入问题类型到表中
			INSERT INTO cs_groupies_20210317.zyqyxczg_a004_b001_type(type_order,type_list)
			VALUES(order_cursor,type_cursor);
			
			-- 序号从1开始，每次循环+1
			set order_cursor = order_cursor + 1;
			
   		/* 循环结束 */    
		end loop myLoop;   
		
	/* 关闭游标，释放内存 */ 
	CLOSE myCursor;
	
END
;;
delimiter ;

-- ----------------------------
-- Event structure for 每天0点刷新动态表头
-- ----------------------------
DROP EVENT IF EXISTS `每天0点刷新动态表头`;
delimiter ;;
CREATE EVENT `每天0点刷新动态表头`
ON SCHEDULE
EVERY '1' DAY STARTS '2021-03-17 00:00:00'
DO CALL cs_groupies_20210317.dynamic_refresh
;;
delimiter ;

SET FOREIGN_KEY_CHECKS = 1;