Procedure does not execute its loop and not the syntax error

1

The PROCEDURE below does not display a syntax error, but when running the LOOP it does not update the records correctly, only some are updated.

CREATE PROCEDURE 'SP_ATU_ITEM_NF' ()
BEGIN
  DECLARE v_nota_antiga INT default 0; 
  DECLARE v_itm INT default 0;
  DECLARE v_MAXIDNOTA INT default (SELECT MAX(idNota) FROM NFItem);
  DECLARE v_Codigo_Antigo VARCHAR(50);
  DECLARE v_Codigo VARCHAR(50);
  DECLARE v_idNota BIGINT default 0;
  DECLARE v_Item BIGINT default 0;
  DECLARE v_done bool default FALSE;
  DECLARE C CURSOR FOR 
  SELECT Codigo, idNota FROM NFItem ORDER BY Codigo,idNota;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = TRUE;
  OPEN C;

  LOOP_ATU_ITEM_NF:LOOP
      FETCH C INTO v_Codigo,v_idNota;
    IF v_nota_antiga <> v_idNota AND v_done = FALSE THEN
      SET v_nota_antiga = v_idNota;
      SET v_itm = 1;
      SET v_Codigo_Antigo = v_Codigo;
      FETCH C INTO v_Codigo,v_idNota;
      UPDATE NFItem SET Item = v_itm WHERE idNota = v_nota_antiga 
                                       AND Codigo = v_Codigo_Antigo;
    ELSE IF v_nota_antiga = v_idNota AND v_done = FALSE THEN
      SET v_Codigo_Antigo = v_Codigo;
      SET v_itm = v_itm +1;
      FETCH C INTO v_Codigo,v_idNota;
      UPDATE NFItem SET Item = v_itm WHERE idNota = v_nota_antiga 
                                       AND Codigo = v_Codigo_Antigo;
    ELSE 
      LEAVE LOOP_ATU_ITEM_NF;
    END IF;
        END IF;
  END LOOP LOOP_ATU_ITEM_NF;
  CLOSE C;
END
    
asked by anonymous 11.04.2018 / 19:36

0 answers