mysql存储过程多层游标循环嵌套的写法分享 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 ;
2024最新激活全家桶教程，稳定运行到2099年，请移步至置顶文章：https://sigusoft.com/99576.html
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请联系我们举报，一经查实，本站将立刻删除。 文章由激活谷谷主-小谷整理,转载请注明出处：https://sigusoft.com/62436.html
