Testar Data de Vencimento

[BetoGroo]

Olá!

Preciso de uma procedure que teste cada linha de uma table (mandado) e se o campo validade for menor que a data atual, o campo situação atualizará para 2.

depois de pesquisas, cheguei ao seguinte código, utilizando cursor:

DELIMITER $$

USE delpol $$

CREATE PROCEDURE datamp()
BEGIN

DECLARE done int default 0;
DECLARE vencimento date;

declare cursor_mp CURSOR FOR SELECT validade from mandado;

DECLARE continue handler for not found set done = 1;

open cursor_mp;

loop_mp: LOOP
    FETCH cursor_mp into vencimento;
    
    if done = 1 then
        leave loop_mp;
    end if;
    
    if vencimento < CURDATE() THEN
        update mandado set id_situacao = 2;
    END IF;
    
end LOOP loop_mp;

END $$

Porém, ele atualiza todas as linhas da tabela, quando que era par atualizar somente as com o prazo de validade vencido. Eis minha table mandado:

CREATE  TABLE IF NOT EXISTS `delpol`.`mandado` (
  `protocolo` INT(6) NOT NULL AUTO_INCREMENT ,
  `id_reu` INT NOT NULL ,
  `id_processo` INT NOT NULL ,
  `validade` DATE NULL ,
  `recebimento` DATE NULL ,
  `observacoes` MEDIUMTEXT NULL ,
  `id_situacao` INT NOT NULL ,
  PRIMARY KEY (`protocolo`, `id_reu`, `id_processo`) ,
  INDEX `fk_reu_processo_reu1` (`id_reu` ASC) ,
  INDEX `fk_reu_processo_processo1` (`id_processo` ASC) ,
  INDEX `fk_mandado_situacao1` (`id_situacao` ASC) ,
  CONSTRAINT `fk_reu_processo_reu1`
    FOREIGN KEY (`id_reu` )
    REFERENCES `delpol`.`reu` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_reu_processo_processo1`
    FOREIGN KEY (`id_processo` )
    REFERENCES `delpol`.`processo` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_mandado_situacao1`
    FOREIGN KEY (`id_situacao` )
    REFERENCES `delpol`.`situacao` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB

Desde já agradeço!

[Denis Courcy]

Oi, 'BetoGroo'

A linha 25 de sua procedure

update mandado set id_situacao = 2;

modifica o atributo id_situação de TODAS as linhas da tabela mandado. Você não precisa desta procedure para executar esta operação. A instrução:

UPDATE mandado
SET id_situacao = 2
WHERE validade < CURDATE();

é suficiente para executar o que você deseja. Porém, se seu desejo é usar uma procedure, então você deve corrigir sua procedure para que fique assim:

DELIMITER $$

USE delpol $$

CREATE PROCEDURE datamp()
BEGIN
   DECLARE done int default 0;
   DECLARE vencimento date;
   DECLARE m_protocolo integer;

   DECLARE cursor_mp CURSOR FOR SELECT protocolo, validade FROM mandado;
   DECLARE CONTINUE HANDLER FOR NOT FOUND SET = 1;

   OPEN cursor_mp;

loop_mp: LOOP
    FETCH cursor_mp INTO m_protocolo, vencimento;
    
    IF done = 1 THEN
        LEAVE loop_mp;
    END IF;
    
    IF vencimento < CURDATE() THEN
        UPDATE mandado SET id_situacao = 2 WHERE protocolo = m_protocolo;
    END IF;
    
END LOOP loop_mp;

END $$

Para lembrar a primeira instrução que passei é muito mais rápida que a storage procedure.

[BetoGroo]

Denis, muito obrigado pela ajuda!

Na verdade, eu usaria o cursor em um event, mas para fim de testes, comecei em uma procedure.

O problema é que eu realmente compliquei. Não precisava nem de cursor. A vantagem é que e aprendi a usar cursor, porém, neste caso, não é mesmo necessário.

Meu evento ficou assim:

DELIMITER $$
USE banco $$
CREATE EVENT EV_validadeMP
ON SCHEDULE EVERY 15 MINUTE
DO
    BEGIN
update mandado set id_situacao = 2
where validade < CURDATE() AND id_situacao = 1;
    END$$

Acha que executar este update a cada 15 minutos compromete o desempenho do banco?

Um Abraço, e muito obrigado!

[Denis Courcy]

Denis, muito obrigado pela ajuda!

Acha que executar este update a cada 15 minutos compromete o desempenho do banco?

Um Abraço, e muito obrigado!

Não muito. Dependerá do tamanho da tabela (qtd de registros).

Crie um índice por VALIDADE e SITUAÇÃO para otimização da busca e o impacto será menor ainda.