mysql存储过程多层游标循环嵌套的写法

短命女 2024-04-08 09:31 75阅读 0赞

最近有遇到一个需求：将有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