MariaDB存储过程调用报错[1329]，游标逻辑导致无返回结果求助

我调用以下MariaDB存储过程时出现错误：

CREATE PROCEDURE AGBBeta.TestReturnOptions(IN pOptionsID INT, IN pQty Int, IN pUserId INT,
    OUT outParam NVARCHAR(3))
BEGIN
    DECLARE CalcGlobalCHECK NVarchar(3);        
    DECLARE done INT DEFAULT FALSE;
    DECLARE iMaterialsId INTEGER;
    DECLARE iRangesID INTEGER;          
    DECLARE iSec1 INTEGER;
    DECLARE dSQM Decimal(9,2);
    DECLARE dSlabQty Decimal(9,2);
    DECLARE iMitreLog INTEGER;
    DECLARE dSlabOverPrice Decimal(9,2);
    DECLARE dPriceOver Decimal(9,2);
    DECLARE iOptionsID2 INTEGER;
    
    DECLARE materials_cursor CURSOR FOR 
            SELECT MaterialsID, RangesID, Sec1, SQM, SlabQty, MitreLog, SlabOverPrice, PriceOver, OptionsID2 FROM AGBBeta.Materials WHERE OptionsID = pOptionsID;   

    SET CalcGlobalCHECK = '000';
    INSERT INTO STORED_PROCEDURED_CHECK(SP_Name, pOptionsID, pQty, pUserId,ValueName,Value)
            VALUES ('TestReturnOptions', pOptionsID, pQty, pUserId,'CalcGlobalCHECK_1',CalcGlobalCHECK);
    
 OPEN materials_cursor;

        materials_cursor_loop: LOOP
            FETCH materials_cursor INTO iMaterialsId, iRangesID, iSec1, dSQM, dSlabQty, iMitreLog, dSlabOverPrice, dPriceOver, iOptionsID2;
        
            IF done THEN
                SET CalcGlobalCHECK = '002';
                INSERT INTO STORED_PROCEDURED_CHECK(SP_Name, pOptionsID, pQty, pUserId,ValueName,Value)
                VALUES ('TestReturnOptions', pOptionsID, pQty, pUserId,'CalcGlobalCHECK_4',CalcGlobalCHECK);
                LEAVE materials_cursor_loop;
            END IF; 
           
           INSERT INTO STORED_PROCEDURED_CHECK(SP_Name, pOptionsID, pQty, pUserId,ValueName,Value)
            VALUES ('TestReturnOptions', pOptionsID, pQty, pUserId,'CalcGlobalCHECK_2',CalcGlobalCHECK);
           
            SET CalcGlobalCHECK = '001';
        
        END LOOP materials_cursor_loop; 
    
        CLOSE materials_cursor;
    
    INSERT INTO STORED_PROCEDURED_CHECK(SP_Name, pOptionsID, pQty, pUserId,ValueName,Value)
            VALUES ('TestReturnOptions', pOptionsID, pQty, pUserId,'CalcGlobalCHECK_3',CalcGlobalCHECK);
    
        SET outParam =  CalcGlobalCHECK;
     
     SELECT outParam;

END

执行语句：

CALL AGBBeta.TestReturnOptions(26610, 1, 4, @a_message);

出现错误：

SQL Error [1329] [02000]: (conn=358) No data - zero rows fetched, selected, or processed

查看STORED_PROCEDURED_CHECK表，仅能看到CalcGlobalCHECK_1和CalcGlobalCHECK_2的记录，其余无显示。移除游标相关的OPEN到CLOSE逻辑后，存储过程可正常运行，但我需要基于游标返回的数据进行计算，请问该如何解决此问题？

问题原因 #

错误[1329]的核心原因是：你声明了done变量但没有绑定游标NOT FOUND的处理逻辑。当游标没有匹配数据，或者所有数据都fetch完成后，done变量不会自动变为TRUE，此时继续执行FETCH操作就会触发"No data"错误，直接中断存储过程执行，导致后续的日志插入和逻辑都无法完成。

解决方法 #

需要在声明游标之后，添加游标未找到数据的处理句柄，让done变量在游标无数据时自动置为TRUE，从而正常退出循环。

修改后的存储过程代码（重点标注新增行）：

CREATE PROCEDURE AGBBeta.TestReturnOptions(IN pOptionsID INT, IN pQty Int, IN pUserId INT,
    OUT outParam NVARCHAR(3))
BEGIN
    DECLARE CalcGlobalCHECK NVarchar(3);        
    DECLARE done INT DEFAULT FALSE;
    DECLARE iMaterialsId INTEGER;
    DECLARE iRangesID INTEGER;          
    DECLARE iSec1 INTEGER;
    DECLARE dSQM Decimal(9,2);
    DECLARE dSlabQty Decimal(9,2);
    DECLARE iMitreLog INTEGER;
    DECLARE dSlabOverPrice Decimal(9,2);
    DECLARE dPriceOver Decimal(9,2);
    DECLARE iOptionsID2 INTEGER;
    
    DECLARE materials_cursor CURSOR FOR 
            SELECT MaterialsID, RangesID, Sec1, SQM, SlabQty, MitreLog, SlabOverPrice, PriceOver, OptionsID2 FROM AGBBeta.Materials WHERE OptionsID = pOptionsID;   
    
    -- 新增：绑定NOT FOUND事件，自动设置done为TRUE
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    SET CalcGlobalCHECK = '000';
    INSERT INTO STORED_PROCEDURED_CHECK(SP_Name, pOptionsID, pQty, pUserId,ValueName,Value)
            VALUES ('TestReturnOptions', pOptionsID, pQty, pUserId,'CalcGlobalCHECK_1',CalcGlobalCHECK);
    
 OPEN materials_cursor;

        materials_cursor_loop: LOOP
            FETCH materials_cursor INTO iMaterialsId, iRangesID, iSec1, dSQM, dSlabQty, iMitreLog, dSlabOverPrice, dPriceOver, iOptionsID2;
        
            IF done THEN
                SET CalcGlobalCHECK = '002';
                INSERT INTO STORED_PROCEDURED_CHECK(SP_Name, pOptionsID, pQty, pUserId,ValueName,Value)
                VALUES ('TestReturnOptions', pOptionsID, pQty, pUserId,'CalcGlobalCHECK_4',CalcGlobalCHECK);
                LEAVE materials_cursor_loop;
            END IF; 
           
           INSERT INTO STORED_PROCEDURED_CHECK(SP_Name, pOptionsID, pQty, pUserId,ValueName,Value)
            VALUES ('TestReturnOptions', pOptionsID, pQty, pUserId,'CalcGlobalCHECK_2',CalcGlobalCHECK);
           
            SET CalcGlobalCHECK = '001';
        
        END LOOP materials_cursor_loop; 
    
        CLOSE materials_cursor;
    
    INSERT INTO STORED_PROCEDURED_CHECK(SP_Name, pOptionsID, pQty, pUserId,ValueName,Value)
            VALUES ('TestReturnOptions', pOptionsID, pQty, pUserId,'CalcGlobalCHECK_3',CalcGlobalCHECK);
    
        SET outParam =  CalcGlobalCHECK;
     
     SELECT outParam;

END

说明 #

添加DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;后，当游标执行FETCH没有获取到数据时，会自动触发这个句柄，将done设为TRUE。此时循环中的IF done THEN条件会生效，执行退出循环的逻辑，后续的日志插入、游标关闭和参数赋值代码都能正常执行，不会再触发[1329]错误。

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