mysql存储过程 REPEAT 嵌套循环

嵌套循环"/>

mysql存储过程 REPEAT 嵌套循环

关于 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;REPEATFETCH dayInfo INTO v_deptId,v_centerCode,v_centerName;IF !done THEN--   业务sqlINSERT 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_numfrom 表Dwhere 字段A=v_deptId and 字段B= v_centerCodeand  DATE_FORMAT(字段C,'%Y-%m')=dateTime ) ts );-- 开启内循环：对外循环的每个对象进行内循环OPEN insideInfo;FETCH insideInfo INTO v_dictLabel,v_dictValue;
SET @j=1;WHILE @j<=in_Num DOINSERT INTO 表C (one_type,two_type,dept_id,center_code,center_name, pro_name,month,year, create_by, create_time,people_num, pro_income)(select "3",v_dictValue,v_deptId,v_centerCode,v_centerName,v_dictLabel,dateTime,left(dateTime,4),'admin',SYSDATE(),0,ts.pro_incomefrom (select ifnull(sum(round(bo.income_money,2)),0) as pro_incomefrom 表E where  bo.dept_id=v_deptId and bo.center_code= v_centerCode  and bo.item=v_dictValue and  DATE_FORMAT(check_time,'%Y-%m')=dateTime  ) ts );SET @j=@j+1;END while;		 --  结束循环CLOSE insideInfo;END IF;--  结束循环UNTIL done END REPEAT;CLOSE dayInfo;COMMIT;END