1:业务需求
最近有业务需求,存储过程内,实现二次循环的场景:首先对每个站点循环,然后对每个站点的业务进行二次循环。
也就是循环内嵌套循环。
2:存储过程实现嵌套循环
关于 mysql 存储过程内循环如何嵌套循环,也是在博主历经一番研究后,终于搞出来了,废话不多说,上干货。
博主是用的REPEAT +while实现的嵌套循环,说实话,mysql存储过程这个功能有待完善。
废话不多说,看代码:
CREATE DEFINER=`root`@`%` PROCEDURE `A`(dateTime varchar(100))
BEGIN
-- 参数 dateTime (格式 yyyy-MM)
DECLARE done BOOLEAN DEFAULT FALSE;
DECLARE edone BOOLEAN DEFAULT FALSE;
DECLARE v_deptId varchar(64);
DECLARE v_centerCode varchar(64);
DECLARE v_centerName varchar(64);
DECLARE v_dictLabel varchar(64);
DECLARE v_dictValue varchar(64);
DECLARE in_Num BIGINT(12);
DECLARE insideInfo CURSOR FOR
select dict_label,dict_value
from 表A a where dict_type='other_item' and status='0';
DECLARE dayInfo CURSOR FOR
select sd.dept_id,sd.center_code,bc.center_name
from 表B
where sd.center_code=bc.center_code and sd.status='0' and sd.del_flag='0' and sd.dept_level='3';
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=TRUE;
START TRANSACTION;
delete from 表C;
OPEN dayInfo;
REPEAT
FETCH dayInfo INTO v_deptId,v_centerCode,v_centerName;
IF !done THEN
-- 业务sql
INSERT INTO 表C
(one_type,two_type,dept_id,center_code,center_name, pro_name,month,year, create_by, create_time,pro_income, people_num)
(
select "1","q", v_deptId,v_centerCode,v_centerName,'基础业务', dateTime,left(dateTime,4),'admin',SYSDATE(),
ts.pro_income,ts.people_num
from
(select ifnull(sum(round(work_money,2)),0) as pro_income,
ifnull(count(DISTINCT bc_id),0) as people_num
from 表D
where 字段A=v_deptId and 字段B= v_centerCode
and DATE_FORMAT(字段C,'%Y-%m')=dateTime ) ts
);
-- 开启内循环:对外循环的每个对象进行内循环
-- 内循环结束
END IF;
UNTIL done END REPEAT;
CLOSE dayInfo;
COMMIT;
END
有需要demo 的可自行下载 。