In this stored procedure for an inventory system I want to process a packaging order for a product in kg according to the type of container and its capacity in kg. This finally stores the existence of the packaged product in the table "itemsenvasado".
I used two cursors because:
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 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;
-- Cursor para Procesar los lotes de producto granel
DECLARE cur_lotes_envasado CURSOR FOR
SELECT id, precio, cantidadenvasar
FROM controlstock.lotesenvasado
WHERE ordenID = ordenID_in
ORDER BY fecInsert;
-- Cursor para Procesar los lotes de envases
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; -- Asumiendo que 'fecInsert' indica la antigüedad del lote
-- Manejadores de Salida
DECLARE EXIT HANDLER FOR NOT FOUND
BEGIN
CLOSE cur_lotes_envasado;
CLOSE cur_envases;
END;
-- 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 y de los envases
OPEN cur_lotes_envasado;
read_loop: LOOP
FETCH cur_lotes_envasado INTO lote_id, lote_precio, lote_cantidad;
-- Terminar Bucle cuando no quedan KG
IF 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;
OPEN cur_envases;
envase_loop: LOOP
FETCH cur_envases INTO envase_lote_id, envase_cantidad;
IF 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
UPDATE controlstock.existencias
SET stock = envase_cantidad - envases_restantes AND status = 2
WHERE id = envase_lote_id;
SET envases_restantes = 0;
END IF;
END LOOP;
CLOSE cur_envases;
-- PASO 4: Calcular el precio unitario del producto envasado
SET precio_unitario = precio_por_kg * capacidad_envase + precio_envase;
-- PASO 5: 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
When I run it, it executes only the first cursor and then I get the error
ER_SP_CURSOR_NOT_OPEN: Cursor is not open
I have tried to move the cursors, but in MySQL Workbench I get the errors
DECLARE is not valid at this position, expecting END
When NOT FOUND is obtained over 1st cursor then EXIT handler is fired. After its statements (closing cursors) are executed then the flow EXITs (leaves) your stored procedure.
You must use CONTINUE handler which sets a variable but returns the process flow to the firing point, check the variable immediately after FETCH and leave the cycle if the variable is set.
Schematically:
CREATE PROCEDURE ..
DECLARE done INT DEFAULT FALSE;
DECLARE CURSOR cursor1 ...
DECLARE CURSOR cursor2 ...
DECLARE CONTINUE /* execute then return back */ HANDLER FOR NOT FOUND
BEGIN
SET done = TRUE;
END;
BEGIN
...
OPEN cursor1;
label1: LOOP
FETCH cursor1 INTO ... -- jump to continue handler when cursor is empty
IF done THEN -- check does 1st cursor is empty
LEAVE label1; -- if true then leave cycle but continue execution
END IF;
...
END LOOP label1;
CLOSE cursor1;
SET done = FALSE; -- reset variable value for 2nd cursor checking
...
OPEN cursor2;
label2: LOOP
FETCH cursor2 INTO ...
IF done THEN LEAVE label2; END IF;
...
END LOOP label2;
CLOSE cursor2;
...