1
The PROCEDURE below does not present syntax error, but when running LOOP it does not update the records correctly, only a few 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