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

mysql存储过程循环中使用游标方法及遇到的问题

Oracle开发9年，频繁使用存储过程。现转到mysql，使用存储过程还是不太习惯，语法差别比较大，而且没有深入去了解过mysql的存储过程语法。

现在公司有部分数据需要通过存储过程处理，只能硬着头皮上了。

循环

与oracle 用法基本一样，使用loop或者while

首先定义一个游标

DECLARE rs CURSOR FOR
SELECT
       contract_id
   FROM
       data_sale_contract_delay
   WHERE FLAG IS NULL or FLAG= \’\’;

在游标定义后面要定义一个如果游标移动到最后一行数据后再次移动后处理的语句.

DECLARE CONTINUE HANDLER FOR SQLSTATE \’02000\’ SET done = 1 ;

这个语句的含义是在fetch 不到内容时,将变量done 赋值为1.用来做跳出循环判断用.

此处与Oracle不同,Oracle会更简单,直接exit when v_cur%notfound;就可以跳出循环.v_cur是游标的名字.

接下来打开游标,遍历:

OPEN rs;
FETCH NEXT FROM rs INTO v_contract_id;
WHILE (done<>1) DO
… — 处理逻辑省略
FETCH NEXT FROM rs INTO v_contract_id;– 处理完成后直接再从游标读取下一条数据,如果已是最后一条数据,再fetch时会出发上面的 CONTINUE HANDLER,将变量done 值改成1
END WHILE;
CLOSE rs;