MySQL 8嵌套游标存储过程报Error Code 1329问题求助

问题描述 #

我正在开发名为build_roster的存储过程，接收逗号分隔的合同ID字符串参数idList。过程中声明变量varId，通过外层游标curseLoop赋值；内层游标secondCurse的WHERE子句需用该varId筛选数据。执行存储过程（调用语句：CALL build_roster("14,15");）时，触发错误Error Code: 1329. No data - zero rows fetched, selected, or processed，未获取到预期的var1至var6组成的结果集。原存储过程代码如下：

CREATE PROCEDURE `build_roster`(
    IN idList VARCHAR(255) -- comma separated string of contract ids
    )
    BEGIN
    DECLARE done INT;
    DECLARE doneAgain INT;
    
    -- variable for the first select statement to retrieve the billing id
    
    DECLARE varId INT DEFAULT 0;
    
    -- variable set that will build the roster table based on the billing id 
    
    DECLARE var1 VARCHAR(25);
    DECLARE var2 VARCHAR(25);
    DECLARE var3 DATETIME;
    DECLARE var4 DATETIME;
    DECLARE var5 DATETIME;
    DECLARE var6 INT;
    
    -- buffer for outside loop
    DECLARE curse CURSOR FOR 
        SELECT id from billing_file WHERE FIND_IN_SET(contract_id, contract_id_list);
    
    -- buffer for interior loop
    DECLARE secondCurse CURSOR FOR
        Select c.col1, c.col2, p.colA, p.colB, p.colC, cl.col from tableC c
        INNER JOIN tableP p ON c.col1 = p.colA
        INNER JOIN tableCL cl ON c.col2 = cl.col2A
        where c.miscCol = varId -- this is where I'm having an issue I think
        GROUP BY c.col1, c.col2, p.colA, p.colB
        ORDER BY p.colB ASC;
    
    -- set up the temporary table to house all the data that we find through the loops
    DROP TEMPORARY TABLE IF EXISTS results;
    CREATE TEMPORARY TABLE IF NOT EXISTS results (
        var1 VARCHAR(25) NOT NULL,
      var2 VARCHAR(25) NOT NULL,
      var3 DATETIME NOT NULL,
      var4 DATETIME,
      var5 DATETIME,
      var6 TINYINT(1) DEFAULT 0
    );
    
    -- begin parsing the data
    OPEN curse;

    curseLoop : LOOP
        FETCH curse INTO varId;
        OPEN secondCurse;
        secondCurseLoop : LOOP
            FETCH secondCurse INTO var1, var2, var3, var4, var5, var6;
            INSERT INTO results VALUES (var1, var2, var3, var4, var5, var6);
        END LOOP secondCurseLoop;
        CLOSE secondCurse;
    END LOOP curseLoop;
    CLOSE curse;

    -- run a quick select to verify data is pulled, this is the stopping point for this procedure      build until I verify it all works
    SELECT * FROM results;
END

错误原因分析 #

1. 缺少游标终止处理逻辑：两个游标都未定义NOT FOUND处理程序，当游标取完所有数据后继续执行FETCH操作，直接触发1329错误。
2. 外层游标参数引用错误：外层游标curse的WHERE子句中使用了不存在的contract_id_list变量，实际应引用传入的参数idList，导致无法正确筛选billing_file的数据。
3. 静态游标无法动态更新条件：MySQL的静态游标在定义时就会解析SQL语句，内层游标secondCurse定义时varId的值为默认的0，后续循环中更新varId不会改变游标对应的筛选条件，导致每次内层查询都用0作为过滤值，无法获取目标数据。

修复方案及代码 #

针对上述问题，我们用动态SQL替代内层游标（解决静态游标无法动态更新条件的问题），添加游标终止处理程序，修正参数引用错误，最终修复后的代码如下：

CREATE PROCEDURE `build_roster`(
    IN idList VARCHAR(255) -- 逗号分隔的合同ID字符串
)
BEGIN
    DECLARE done INT DEFAULT 0;
    
    -- 存储billing id的变量
    DECLARE varId INT DEFAULT 0;
    
    -- 外层游标：根据传入的合同ID列表获取billing id
    DECLARE curse CURSOR FOR 
        SELECT id FROM billing_file WHERE FIND_IN_SET(contract_id, idList);
    
    -- 定义外层游标的终止处理程序
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    
    -- 创建临时表存储结果
    DROP TEMPORARY TABLE IF EXISTS results;
    CREATE TEMPORARY TABLE IF NOT EXISTS results (
        var1 VARCHAR(25) NOT NULL,
        var2 VARCHAR(25) NOT NULL,
        var3 DATETIME NOT NULL,
        var4 DATETIME,
        var5 DATETIME,
        var6 TINYINT(1) DEFAULT 0
    );
    
    -- 开始外层循环
    OPEN curse;
    curseLoop : LOOP
        FETCH curse INTO varId;
        -- 当没有数据时退出外层循环
        IF done = 1 THEN
            LEAVE curseLoop;
        END IF;
        
        -- 用动态SQL拼接查询，实现varId的动态筛选
        SET @sql = CONCAT(
            'INSERT INTO results (var1, var2, var3, var4, var5, var6) ',
            'SELECT c.col1, c.col2, p.colA, p.colB, p.colC, cl.col FROM tableC c ',
            'INNER JOIN tableP p ON c.col1 = p.colA ',
            'INNER JOIN tableCL cl ON c.col2 = cl.col2A ',
            'WHERE c.miscCol = ', varId, ' ',
            'GROUP BY c.col1, c.col2, p.colA, p.colB ',
            'ORDER BY p.colB ASC'
        );
        
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END LOOP curseLoop;
    CLOSE curse;
    
    -- 返回最终结果集
    SELECT * FROM results;
END

修复说明 #

• 移除了嵌套游标，改用动态SQL实现varId的动态筛选，避免了静态游标无法更新条件的问题。
• 添加了NOT FOUND处理程序，确保游标取完数据后能正确退出循环，不再触发1329错误。
• 修正了外层游标中的参数引用错误，现在能正确根据传入的idList筛选billing_file的数据。

内容的提问来源于stack exchange，提问作者Mark Hill