CREATE OR REPLACE PROCEDURE ATUALIZA_ULTDTMOVCONS
AS
BEGIN
   FOR C1 IN (SELECT CDCLIENTE, CDCONSUMIDOR, MAX(DTMOVCLI) AS DTMOVCLI
             FROM MOVICLIE
             WHERE (CDCONSUMIDOR IS NOT NULL)
             GROUP BY CDCLIENTE, CDCONSUMIDOR) LOOP

      UPDATE CONSUMIDOR
      SET DTULTMOVCONS = C1.DTMOVCLI
      WHERE (CDCLIENTE    = C1.CDCLIENTE) AND
            (CDCONSUMIDOR = C1.CDCONSUMIDOR);
   END LOOP;

   FOR C2 IN (SELECT CDCLIENTE, MAX(DTMOVCLI) AS DTMOVCLI
              FROM MOVICLIE
              WHERE (CDCONSUMIDOR IS NULL)
              GROUP BY CDCLIENTE) LOOP

      UPDATE CLIENTE
      SET DTULTMOVCLIE = C2.DTMOVCLI
      WHERE (CDCLIENTE = C2.CDCLIENTE);
   END LOOP;
END;
/

COMMIT
/

BEGIN
ATUALIZA_ULTDTMOVCONS;
END;
/

COMMIT
/

DROP PROCEDURE ATUALIZA_ULTDTMOVCONS
/
