mysql存储过程双层循环 mysql存储过程循环写法

最近有遇到一个需求：将有3级主从关联的表数据同步到另外一个有3级主从关联的表中，同步数据的表是第3级子表，而关联关系在第1、2级主表中，由于考虑到到内网数据库可能没法直接访问的限制，所以没有用代码来实现，而是直接用存储过程来实现

最终存储过程的写法如下：

DELIMITER $$

USE `myDb`$$

DROP PROCEDURE IF EXISTS `syncBomSopFromRouteSop`$$

CREATE PROCEDURE `syncBomSopFromRouteSop`(IN tenantId BIGINT(11))
BEGIN
	#工艺路线ID
	DECLARE crId BIGINT(11);
	#生产bomID
	DECLARE pbrId BIGINT(11);
	#工序档案ID
	DECLARE processId VARCHAR(50);
	#生产bom工序Id
	DECLARE bomProcessId BIGINT(11);
	#生产bom数量
	DECLARE productionBomNum INT(5);
	#生产bom工序数量
	DECLARE productionBomProcessNum INT(5);
	
	DECLARE i INT(4) DEFAULT 1;
	#DECLARE j INT(4) DEFAULT 1;
	
	
	#定义生产bom游标
	DECLARE productionBomCursor CURSOR FOR (
		SELECT cr_id,pbr_id FROM jgmes_modeling_production_bom_route WHERE tenant_id=tenantId AND delete_flag=0 AND IFNULL(cr_id,'')<>'' ORDER BY cr_id,pbr_id /*测试1条数据AND pbr_id=86673*/ 
	);
	#定义生产bom工序游标
	DECLARE bomProcessCursor CURSOR FOR (
		SELECT pbp_id bomProcessId,p.p_id processId
		FROM jgmes_modeling_production_bom_process pbp 
		LEFT JOIN jgmes_modeling_process p ON p.p_id=pbp.p_id
		LEFT JOIN jgmes_modeling_production_bom_route pbr ON pbr.pbr_id=pbp.pbr_id
		WHERE pbp.tenant_id=tenantId AND pbp.delete_flag=0 AND 
		pbr.tenant_id=tenantId AND pbr.delete_flag=0 AND pbr.pbr_id=pbrId /*测试1条生产bom */
	);
	#定义生产bom的sop游标
	/*DECLARE bomSopCursor CURSOR FOR (
		
	);*/
	#解决没有查到数据报：“DECLARE CONTINUE HANDLER FOR NOT FOUND SET @IS_FOUND=0;”的异常的处理方法
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET @IS_FOUND=0;
	
	SELECT COUNT(*) INTO productionBomNum FROM jgmes_modeling_production_bom_route WHERE tenant_id=tenantId AND delete_flag=0 AND IFNULL(cr_id,'')<>''/*测试1条数据 AND pbr_id=86673*/;
	
	
	SET @creationDate=NOW();
	
	TRUNCATE TABLE a;
	

	##打开生产bom游标
	OPEN productionBomCursor;
		/* 生产bom循环开始 */
		 FETCH productionBomCursor INTO crId,pbrId;
		 WHILE i<=productionBomNum DO
			
		    SELECT COUNT(*) INTO productionBomProcessNum FROM jgmes_modeling_production_bom_process WHERE tenant_id=tenantId AND delete_flag=0 AND pbr_id=pbrId;
		    
		    #第2层循环
		    #打开生产bom工序游标
		    OPEN bomProcessCursor;
			FETCH bomProcessCursor INTO bomProcessId,processId;
			SET @j=1;  
			WHILE @j<=productionBomProcessNum DO
			INSERT INTO a (seqNum, remark)VALUES(CONCAT(i,'_',@j,'_',bomProcessId), 'i-j-bomProcessId');
				#INSERT INTO a (seqNum, remark)VALUES(CONCAT(crId,'_',processId), 'crProcessId');
				#删除生产bom工序下面的sop表记录
				UPDATE jgmes_modeling_production_sop SET delete_flag=1 WHERE tenant_id=tenantId AND delete_flag=0 AND pbp_id=bomProcessId;
				#将生产bom工序对应工艺路线工序下面的sop文件复制插入
				INSERT INTO jgmes_modeling_production_sop (
				  pbr_id,
				  pbp_id,
				  sn,
				  file_name,
				  file_path,
				  file_type,
				  file_length,
				  file_version_num,
				  tenant_id,
				  creation_date,
				  created_by,
				  last_update_date,
				  last_updated_by,
				  last_update_login,
				  delete_flag,
				  version_num
				)
				SELECT
				  pbrId,
				  bomProcessId,
				  sn,
				  file_name,
				  file_path,
				  file_type,
				  file_length,
				  file_version_num,
				  sop.tenant_id,
				  @creationDate,
				  -1,
				  sop.last_update_date,
				  sop.last_updated_by,
				  sop.last_update_login,
				  sop.delete_flag,
				  sop.version_num
				FROM
				  jgmes_modeling_crafts_sop  sop
				LEFT JOIN jgmes_modeling_crafts_process cp ON cp.cp_id=sop.cp_id
				LEFT JOIN jgmes_modeling_process p ON p.p_id=cp.p_id
				WHERE sop.tenant_id=tenantId AND sop.delete_flag=0 AND cp.cr_id=crId AND cp.p_id=processId;
				#游标下移
				FETCH bomProcessCursor INTO bomProcessId,processId;
				SET @j=@j+1;
			END WHILE;
		    CLOSE bomProcessCursor;
		    
		    #游标下移
		    FETCH productionBomCursor INTO crId,pbrId;
		    SET i=i+1;
		 END WHILE;
		/*生产bom循环结束*/
	##关闭游标
	CLOSE productionBomCursor;	
	#返回新同步的数据
	SELECT * FROM jgmes_modeling_production_sop WHERE tenant_id=tenantId AND delete_flag=0 AND creation_date=@creationDate AND created_by=-1;

END$$

DELIMITER ;

刚开始写完执行时，报了一个“DECLARE CONTINUE HANDLER FOR NOT FOUND SET @IS_FOUND=0;”的错误，后面加了如下语句就正常了：

DECLARE CONTINUE HANDLER FOR NOT FOUND SET @IS_FOUND=0;

由于存储过程调试不太方便，其中a表是用来调试用的临时表，调试完成后可以删除

CREATE TABLE `a` (
  `id` bigint(11) NOT NULL AUTO_INCREMENT,
  `seqNum` varchar(50) DEFAULT NULL,
  `remark` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10722 DEFAULT CHARSET=utf8mb4