I have to retrieve some values before running my cursor, as the MySql syntax does not allow to make select before the cursor declaration, I thought of dribbling, doing the select in a first procedure and then calling the second that will iterate over a cursor :
DROP PROCEDURE IF EXISTS finaliza_volumes;
DELIMITER |
CREATE PROCEDURE finaliza_volumes (IN id_area INT, IN id_conf INT, IN rec_id INT)
BEGIN
DECLARE id_interno INT;
DECLARE v_finished INTEGER DEFAULT 0;
DEClARE conf_cursor CURSOR FOR
SELECT origem_id FROM conferencia_item WHERE conferencia_id = id_conf;
DECLARE CONTINUE HANDLER
FOR NOT FOUND SET v_finished = 1;
OPEN conf_cursor;
conf_get: LOOP
FETCH conf_cursor INTO id_interno;
IF v_finished = 1 THEN
LEAVE conf_get;
END IF;
UPDATE volume
SET contido_em_id = NULL, area_id = id_area, expedicao_id = NULL, recebimento = rec_id
WHERE id = id_interno;
END LOOP conf_get;
CLOSE conf_cursor;
END |
DELIMITER ;
DROP PROCEDURE IF EXISTS finaliza_recebimento;
DELIMITER |
CREATE PROCEDURE finaliza_recebimento (IN rec_id INT)
BEGIN
DECLARE id_conf INT;
DECLARE id_area INT;
SELECT conferencia_id, area_id INTO id_conf, id_area FROM recebimento WHERE id = rec_id;
CALL finaliza_volumes(id_area, id_conf, rec_id);
UPDATE recebimento
SET estado = 'FINALIZADO'
WHERE id = rec_id;
END |
DELIMITER ;
Does not return any errors and only returns the:
Query OK, 1 row affected (0.02 sec)
But for some reason the update inside the cursor never occurs, and I'm sure that select of the cursor must have values, and this "1 row affected" is the update on receipt. >
Can someone help me, know how to do this procedure call inside another, or some trick to have these values before the cursor declaration?