mysql存储过程语法和游标的语法_简述在MySQL中使用游标的原因

mysql存储过程语法和游标的语法_简述在MySQL中使用游标的原因mysql存储过程多层游标循环嵌套的写法分享DELIMITER $$USE `myDb`$$DROP PROCEDURE IF EXISTS `syncBomSopFromRouteSop`$$CREATE PROCEDURE `syncBomSopFromR

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/63528.html

(0)
上一篇 2024年 8月 28日
下一篇 2024年 8月 28日

相关推荐

关注微信