Mysql存储过程循环语句的使用案例

业务描述

使用Mysql存储过程去统计时间段内每个员工的奖励总和，并将统计的数据保存到员工奖励统计表。

业务数据

1.创建员工信息表–tb_employee

DROP TABLE IF EXISTS `tb_employee`;
CREATE TABLE `tb_employee`  (
`emp_id` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '员工编号',
`emp_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '员工姓名',
`email` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '邮箱',
`phone` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '联系电话',
`address` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '地址',
PRIMARY KEY (`emp_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

初始化员工信息表（tb_employee）数据：

INSERT INTO tb_employee (emp_id, emp_name, email, phone, address) VALUES (REPLACE(UUID(), '-', ''), '张三', '123@qq.com', '5758681', '华山');
INSERT INTO tb_employee (emp_id, emp_name, email, phone, address) VALUES (REPLACE(UUID(), '-', ''), '李四', '133@qq.com', '5758830', '衡山');
INSERT INTO tb_employee (emp_id, emp_name, email, phone, address) VALUES (REPLACE(UUID(), '-', ''), '王五', '122@qq.com', '5758112', '嵩山');
INSERT INTO tb_employee (emp_id, emp_name, email, phone, address) VALUES (REPLACE(UUID(), '-', ''), '朱六', '135@qq.com', '5758329', '泰山');

2.创建员工奖励信息表–tb_empreward

DROP TABLE IF EXISTS `tb_empreward`;
CREATE TABLE `tb_empreward`  (
`empreward_id` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '员工奖励编号',
`emp_id` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '员工编号',
`money` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '奖励金额',
`create_time` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '创建时间',
PRIMARY KEY (`empreward_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

初始化员工奖励信息表（tb_empreward）数据：

INSERT INTO tb_empreward (empreward_id, emp_id, money, create_time) VALUES (REPLACE(UUID(), '-', ''), 'bb9ef0b3eebd11e8b122204747de55dc', '100', NOW());
INSERT INTO tb_empreward (empreward_id, emp_id, money, create_time) VALUES (REPLACE(UUID(), '-', ''), 'bb9ef0b3eebd11e8b122204747de55dc', '120', NOW());
INSERT INTO tb_empreward (empreward_id, emp_id, money, create_time) VALUES (REPLACE(UUID(), '-', ''), 'bb9ef0b3eebd11e8b122204747de55dc', '150', NOW());
INSERT INTO tb_empreward (empreward_id, emp_id, money, create_time) VALUES (REPLACE(UUID(), '-', ''), 'bba62149eebd11e8b122204747de55dc', '155', NOW());
INSERT INTO tb_empreward (empreward_id, emp_id, money, create_time) VALUES (REPLACE(UUID(), '-', ''), 'bba62149eebd11e8b122204747de55dc', '20', NOW());
INSERT INTO tb_empreward (empreward_id, emp_id, money, create_time) VALUES (REPLACE(UUID(), '-', ''), 'bbb81893eebd11e8b122204747de55dc', '70', NOW());
INSERT INTO tb_empreward (empreward_id, emp_id, money, create_time) VALUES (REPLACE(UUID(), '-', ''), 'bbc25c03eebd11e8b122204747de55dc', '90', NOW());
INSERT INTO tb_empreward (empreward_id, emp_id, money, create_time) VALUES (REPLACE(UUID(), '-', ''), 'bbc25c03eebd11e8b122204747de55dc', '200', NOW());

3.创建员工奖励统计表–tb_emprewardcount

DROP TABLE IF EXISTS `tb_emprewardcount`;
CREATE TABLE `tb_emprewardcount`  (
`emprewardcount_id` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '员工统计编号',
`emp_id` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '员工编号',
`emp_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '员工姓名',
`subtotal` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '总计',
PRIMARY KEY (`emprewardcount_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

代码示例

CREATE DEFINER=`root`@`%` PROCEDURE `emp_count`(
IN in_start_time VARCHAR(50),
IN in_end_time VARCHAR(50),
OUT out_msg VARCHAR(50))


BEGIN
/*定义变量*/
DECLARE v_emp_id VARCHAR(50);
DECLARE v_subtotal VARCHAR(50);
DECLARE v_emp_name VARCHAR(50);
DECLARE done INT DEFAULT -1;/*用于控制循环是否结束*/
/*声明游标*/
DECLARE emp_id_cursor CURSOR FOR SELECT emp_id FROM tb_employee;
/*当游标到达尾部时,mysql自动设置done=1*/
DECLARE CONTINUE HANDLER FOR NOT found SET done=1;
/*打开游标*/
OPEN emp_id_cursor;

/*循环开始*/
myLoop: LOOP
/*移动游标并赋值*/
FETCH emp_id_cursor INTO v_emp_id;
/*游标到达尾部,退出循环*/
IF done = 1 THEN LEAVE myLoop;    
END IF;

SELECT SUM(a.money) INTO v_subtotal FROM tb_empreward a WHERE a.create_time BETWEEN in_start_time AND in_end_time AND a.emp_id=v_emp_id;

IF v_subtotal IS NOT NULL THEN
SET v_emp_name =(SELECT emp_name FROM tb_employee WHERE emp_id=v_emp_id);
INSERT INTO tb_emprewardcount (emprewardcount_id, emp_id,emp_name,subtotal) VALUES (REPLACE(UUID(), '-', ''), v_emp_id, v_emp_name, v_subtotal);
END IF;

/*循环结束*/
END LOOP myLoop;  
COMMIT;
SET out_msg='success';
/*关闭游标*/
CLOSE emp_id_cursor;
END