Call one procedure inside another - MySql

0

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?

    
asked by anonymous 05.03.2018 / 14:50

0 answers