MySQL存储过程双游标报错ER_SP_CURSOR_NOT_OPEN求助

核心错误原因 #

1. ER_SP_CURSOR_NOT_OPEN 错误 #

你的EXIT HANDLER会在触发NOT FOUND时尝试关闭两个游标，但第一个游标cur_lotes_envasado在完成循环后已被手动关闭。当第二个游标循环触发NOT FOUND时，HANDLER尝试关闭已关闭的游标，导致报错。

2. DECLARE位置错误 #

MySQL语法强制要求所有DECLARE语句（变量、游标、HANDLER）必须放在BEGIN块的最顶部，不能在任何执行语句（如SET、SELECT INTO）之后声明，否则会触发DECLARE is not valid at this position错误。

修复后的完整存储过程 #

CREATE DEFINER=`soporte`@`localhost` PROCEDURE `insertarItemEnvasado`(
    IN ordenID_in           INT,                -- ID de la Orden
    IN lote_orden_in        VARCHAR(45),        -- Lote de la Orden de Envasado
    IN bod_origenID_in      INT,                -- ID Bodega de Origen
    IN bod_destinoID_in     INT,                -- ID Bodega de Destino
    IN productoID_in        INT,                -- ID Producto a Envasar
    IN cantidad_kg_envasar  DECIMAL(10,2),      -- Cantidad KG a Envasar
    IN envaseID_in          INT,                -- ID Envase
    IN capacidad_envase     DECIMAL(10,2),      -- Capacidad de Envase
    IN cantidad_envase      DECIMAL(10,2),      -- Cantidad de Envases
    IN codigo_final_in      VARCHAR(45),        -- Codigo del Producto Envasado Final
    IN userID_in            INT                 -- ID Usuario que hizo la Transaccion
)
BEGIN
    -- 所有DECLARE语句必须放在最顶部
    DECLARE total_kg_envasar    DECIMAL(10,2);
    DECLARE total_envases       INT;
    DECLARE kg_restante         DECIMAL(10,2);
    DECLARE envases_restantes   INT;
    DECLARE precio_unitario     DECIMAL(10,2);
    DECLARE precio_por_kg       DECIMAL(10,2);
    
    DECLARE lote_id INT;
    DECLARE lote_cantidad DECIMAL(10,2);
    DECLARE lote_precio DECIMAL(10,2);
    
    DECLARE envase_lote_id INT;
    DECLARE envase_cantidad INT;
    DECLARE precio_envase INT;
    
    -- 游标声明
    DECLARE cur_lotes_envasado CURSOR FOR
    SELECT id, precio, cantidadenvasar
    FROM controlstock.lotesenvasado
    WHERE ordenID = ordenID_in
    ORDER BY fecInsert;
    
    DECLARE cur_envases CURSOR FOR
    SELECT id, stock
    FROM controlstock.existencias
    WHERE productoID = envaseID_in AND bodegaID = bod_origenID_in AND status IN (1,2)
    ORDER BY fecInsert ASC;

    -- 为游标设置CONTINUE HANDLER，避免EXIT HANDLER提前终止存储过程
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET @not_found = 1;
    DECLARE @not_found INT;

    -- PASO 1: Verificar existencia total de lotes seleccionados y obtener precio por kg
    SELECT IFNULL(SUM(stock),0), AVG(precio)
    INTO total_kg_envasar, precio_por_kg
    FROM controlstock.lotesenvasado 
    WHERE ordenID = ordenID_in;

    -- Validar total KG para envasar
    IF total_kg_envasar < cantidad_kg_envasar THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'No hay suficientes KG. para envasar';
    END IF;

    -- PASO 2: Verificar Existencia de Envases y sacar precio unitario
    SELECT IFNULL(SUM(cantidad),0) 
    INTO total_envases
    FROM controlstock.productobodega 
    WHERE productoID = envaseID_in AND bodegaID = bod_origenID_in;
    
    SET precio_envase = (SELECT AVG(precio) FROM controlstock.existencias WHERE productoID = envaseID_in AND bodegaID = bod_origenID_in AND status IN (1,2));

    -- Validar total envases para envasar
    IF total_envases < cantidad_envase THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'No hay suficientes envases para envasar';
    END IF;

    -- Setear Kg Restantes
    SET kg_restante = cantidad_kg_envasar;
    
    -- Setear Envases Restantes
    SET envases_restantes = cantidad_envase;

    -- PASO 3: Descontar del producto granel
    SET @not_found = 0;
    OPEN cur_lotes_envasado;

    read_loop: LOOP
        FETCH cur_lotes_envasado INTO lote_id, lote_precio, lote_cantidad;
        
        -- 当无数据或KG已处理完时退出循环
        IF @not_found = 1 OR kg_restante <= 0 THEN
            LEAVE read_loop;
        END IF;

        IF lote_cantidad <= kg_restante THEN
            UPDATE controlstock.lotesenvasado
            SET stock = 0
            WHERE id = lote_id;
            SET kg_restante = kg_restante - lote_cantidad;
        ELSE
            UPDATE controlstock.lotesenvasado
            SET stock = lote_cantidad - kg_restante
            WHERE id = lote_id;
            SET kg_restante = 0;
        END IF;
    END LOOP;

    CLOSE cur_lotes_envasado;

    -- PASO 4: Descontar de los envases
    SET @not_found = 0;
    OPEN cur_envases;

    envase_loop: LOOP
        FETCH cur_envases INTO envase_lote_id, envase_cantidad;
        
        -- 当无数据或 envases 已处理完时退出循环
        IF @not_found = 1 OR envases_restantes <= 0 THEN
            LEAVE envase_loop;
        END IF;

        IF envase_cantidad <= envases_restantes THEN
            UPDATE controlstock.existencias
            SET stock = 0
            WHERE id = envase_lote_id;
            SET envases_restantes = envases_restantes - envase_cantidad;
        ELSE
            -- 修复语法错误：用逗号分隔多个赋值，而非AND
            UPDATE controlstock.existencias
            SET stock = envase_cantidad - envases_restantes, status = 2
            WHERE id = envase_lote_id;
            SET envases_restantes = 0;
        END IF;
    END LOOP;

    CLOSE cur_envases;

    -- PASO 5: Calcular el precio unitario del producto envasado
    SET precio_unitario = precio_por_kg * capacidad_envase + precio_envase;

    -- PASO 6: Añadir el producto envasado al inventario
    INSERT INTO controlstock.itemsenvasado (documentoID, productoID, bodegaID, lote, unidadID, precio, cantidad, stock, userID, status)
    VALUES (ordenID_in, productoID_in, bod_destinoID_in, lote_orden_in, 4, precio_unitario, cantidad_envase, cantidad_envase, userID_in, 1);

END

关键修复点 #

• 调整DECLARE顺序：确保所有变量、游标、HANDLER声明都在BEGIN块的最顶部，符合MySQL语法要求。
• 替换EXIT HANDLER为CONTINUE HANDLER：使用全局变量@not_found标记游标是否取完数据，避免EXIT HANDLER提前终止存储过程，同时避免尝试关闭已关闭的游标。
• 修复UPDATE语法错误：第二个游标循环中的UPDATE语句，将AND改为逗号，正确设置多个字段值。
• 循环退出条件优化：在每个游标循环中同时检查@not_found和剩余数量，确保循环能正常终止。

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