mysql 存储过程

1.创建表

DROP TABLE IF EXISTS `weekly_cycle`;
CREATE TABLE `weekly_cycle`  (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '序号',
  `start_date` date DEFAULT NULL COMMENT '填报开始日期',
  `end_date` date DEFAULT NULL COMMENT '填报停止日期',
  `cycle` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '周期名称',
  `city` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '地市公司名称',
  `mark` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '备注',
  `flag` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT '0' COMMENT '是否可见 1可见 0不可见',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 26 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of weekly_cycle
-- ----------------------------
INSERT INTO `weekly_cycle` VALUES (4, '2021-06-24', '2021-06-30', '2021年6月第四周', '西安', NULL, '1');
INSERT INTO `weekly_cycle` VALUES (6, '2021-06-24', '2021-06-30', '2021年6月第四周', '渭南', NULL, '1');
INSERT INTO `weekly_cycle` VALUES (7, '2021-06-24', '2021-06-30', '2021年6月第四周', '商洛', NULL, '1');
INSERT INTO `weekly_cycle` VALUES (8, '2021-06-24', '2021-06-30', '2021年6月第四周', '安康', NULL, '1');
INSERT INTO `weekly_cycle` VALUES (9, '2021-06-24', '2021-06-30', '2021年6月第四周', '铜川', NULL, '1');
INSERT INTO `weekly_cycle` VALUES (10, '2021-06-24', '2021-06-30', '2021年6月第四周', '宝鸡', NULL, '1');
INSERT INTO `weekly_cycle` VALUES (11, '2021-06-24', '2021-06-30', '2021年6月第四周', '榆林', NULL, '1');
INSERT INTO `weekly_cycle` VALUES (12, '2021-06-24', '2021-06-30', '2021年6月第四周', '延安', NULL, '1');
INSERT INTO `weekly_cycle` VALUES (13, '2021-06-24', '2021-06-30', '2021年6月第四周', '汉中', NULL, '1');
INSERT INTO `weekly_cycle` VALUES (14, '2021-06-24', '2021-06-30', '2021年6月第四周', '西咸', NULL, '1');
INSERT INTO `weekly_cycle` VALUES (15, '2021-06-24', '2021-06-30', '2021年6月第四周', '咸阳', NULL, '1');

 

DROP TABLE IF EXISTS `weekly_dict`;
CREATE TABLE `weekly_dict`  (
  `id` int(255) NOT NULL AUTO_INCREMENT COMMENT '编码',
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '名称',
  `value` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '值',
  `type` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '类型',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 51 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '标注释' ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of weekly_dict
-- ----------------------------

INSERT INTO `weekly_dict` VALUES (47, '第一周', '1', 'proc');
INSERT INTO `weekly_dict` VALUES (48, '第二周', '2', 'proc');
INSERT INTO `weekly_dict` VALUES (49, '第三周', '3', 'proc');
INSERT INTO `weekly_dict` VALUES (50, '第四周', '4', 'proc');

 

DROP TABLE IF EXISTS `weekly_target_amount`;
CREATE TABLE `weekly_target_amount`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `target` double DEFAULT NULL,
  `union_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `year` varchar(4) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 13 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of weekly_target_amount
-- ----------------------------
INSERT INTO `weekly_target_amount` VALUES (1, 7000, '西安', '2021');
INSERT INTO `weekly_target_amount` VALUES (2, 150, '咸阳', '2021');
INSERT INTO `weekly_target_amount` VALUES (3, 150, '宝鸡', '2021');
INSERT INTO `weekly_target_amount` VALUES (4, 180, '渭南', '2021');
INSERT INTO `weekly_target_amount` VALUES (5, 80, '汉中', '2021');
INSERT INTO `weekly_target_amount` VALUES (6, 120, '安康', '2021');
INSERT INTO `weekly_target_amount` VALUES (7, 120, '商洛', '2021');
INSERT INTO `weekly_target_amount` VALUES (8, 55, '铜川', '2021');
INSERT INTO `weekly_target_amount` VALUES (9, 55, '延安', '2021');
INSERT INTO `weekly_target_amount` VALUES (10, 30, '榆林', '2021');
INSERT INTO `weekly_target_amount` VALUES (11, 60, '西咸', '2021');

2.创建存储过程

CREATE DEFINER=`root`@`%` PROCEDURE `pro_test`(in count int(11))
begin
declare temp int default 0;
declare dict_value int ;
declare month_str int;

#初始化month
  select date_format( start_date, '%m' ) into  month_str from weekly_cycle
    where start_date= ( SELECT max( start_date ) FROM weekly_cycle ) 
    group by start_date;

#循环插入weekly_cycle
while temp < count DO

#计算dict_value初始值
SELECT
CASE
WHEN
    locate( '一', cycle ) THEN
    2 
    WHEN locate( '二', cycle ) THEN
    3 
    WHEN locate( '三', cycle ) THEN
    4 
    WHEN locate( '四', cycle ) THEN
    1 
    END AS cycle into dict_value
FROM
    weekly_cycle 
WHERE
    end_date = ( SELECT max( end_date ) FROM weekly_cycle ) 
GROUP BY
    cycle;

#计算月份
if dict_value = 1 then
  select (date_format( start_date, '%m' )+1) into  month_str from weekly_cycle
    where start_date= ( SELECT max( start_date ) FROM weekly_cycle ) 
    group by start_date;

end if;

#插入新的数据
insert into weekly_cycle(cycle,city,start_date,end_date) 
SELECT
    concat(
    date_format( cycle.new_start_date, '%Y' ),
    '年',
     month_str,
    '月',
    ( SELECT NAME FROM weekly_dict WHERE type = 'proc' AND VALUE = dict_value ) 
    ) AS cycle,
    city.union_name as city,
    cycle.new_start_date AS start_date,
    cycle.new_end_date AS end_date 
FROM
    ( SELECT DISTINCT union_name FROM weekly_target_amount ) AS city,
    (
SELECT
    date_add( new_weekly_cycle.end_date, INTERVAL 1 DAY ) AS new_start_date,
    date_add( new_weekly_cycle.end_date, INTERVAL 7 DAY ) AS new_end_date 
FROM
    ( SELECT max( start_date ) AS start_date, max( end_date ) AS end_date FROM weekly_cycle ) AS new_weekly_cycle 
    ) AS cycle;

/*

#参数重置循环
if (dict_value = 4) then 
    set dict_value = 1;
end if;
set dict_value = dict_value + 1; */
#重置循环
set temp = temp + 1;
end while;#结束while循环
end