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

Irma ·

更新时间:2024-05-16

· 1557 次阅读

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

最近有遇到一个需求

最终存储过程的写法如下

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;

循环无法正常退出问题

由于while中使用了select XX into XX from XXX的这种赋值语句在 select 的时候，没查到数据，导致出发handler将done修改为1，然后光标就会跳出循环。

或者多层循环嵌套时内循环出现将done修改为1的情况。

以上这两种情况，都是因为CONTINUE HANDLER把done更新造成的，如果遇到此类问题，需要在每次可能出现done被改变的地方处理完逻辑后增加set done=0;把变量值改一下，这样就可以继续执行了。

总结

这些仅为个人经验，希望能给大家一个参考，也希望大家多多支持软件开发网。

循环 mysql存储过程游标 Mysql 嵌套

1024 个赞