[MYSQL] Colocar o resultado de uma procedure em uma variável? [RESOLVIDO]

Assuntos Gerais
#1

Boa noite pessoal, estou desenvolvendo um projeto de um banco de dados(Só estudando mesmo, não é um hotel real) para um hotel, e preciso da ajuda de vocês…
A situação é a seguinte:
Eu preciso chamar uma procedure dentro de uma trigger, e colocar o valor dessa procedure em uma variável para poder dar um update em um campo da tabela…
A tabela em questão, é a tabela aluguel, que tem um campo com o nome de “despesas_totais”. Esse campo irá ser preenchido depois que o cliente der checkout no hotel, ou seja, quando o new.data_saida dele na tabela aluguel não for nulo. Assim que ele der checkout todas as despesas durante aquele período serão somadas e esta soma irá para o tal campo “despesas_totais”… Existe uma tabela de despesa que tem os campos id_despesa, descricao_despesa e valor_despesa, para poder identificar o valor de cada despesa que o hotel oferece separadamente. E existe uma tabela que é um produto de N:N da tabela aluguel para a tabela despesas, que tem como intuito gravar e identificar qual despesa foi consumida por qual cliente e qual o quarto e a data de entrada desse cliente no hotel…
Essa tabela N:N entre aluguel e despesas é denominada aluguel_despesas , e tem como chaves primárias o id_despesa (da tabela despesas), o id_cliente (da tabela aluguel), o o id_quarto (da tabela aluguel) e a data_entrada(da tabela aluguel, e que é de tipo datetime).
A procedure está funcionando corretamente, de acordo com os parâmetros que eu passo…
segue a procedure:

DROP PROCEDURE IF EXISTS Despesas_por_id_SP;
DELIMITER $$ 
// Aqui eu passarei como parâmetro o id do cliente, e a data em que ele entrou no hotel
CREATE PROCEDURE Despesas_por_id_SP (IN id_cliente INT, IN data_aluguel DATETIME)
BEGIN
SELECT sum(despesas.valor_despesa)
   FROM despesas,aluguel_despesas,aluguel
   WHERE aluguel.id_cliente = aluguel_despesas.id_cliente
   AND aluguel.data_entrada = aluguel_despesas.data_entrada
   AND aluguel.id_quarto = aluguel_despesas.id_quarto
   AND aluguel_despesas.id_despesa = despesas.id_despesa
//Passando os parâmetros
   AND (aluguel.id_cliente = id_cliente)
   AND (aluguel.data_entrada = data_aluguel);
END $$ 
DELIMITER ; 
COMMIT

Eu preciso passar o Resultado dessa procedure em uma variável para a seguinte trigger:

DELIMITER //
DROP TRIGGER IF EXISTS `hotel`.`Controle_Despesas_TR`;
CREATE TRIGGER `hotel`.`Controle_Despesas_TR` 
AFTER UPDATE ON hotel.alg_aluguel_tb
FOR EACH ROW
BEGIN 
   // Preciso passar o valor da procedure para essa variável:
   DECLARE Despesas_Cliente double; 
  // Estava tentando passar da seguinte forma (está dando erro...)
  CALL  Despesas_por_id_SP(aluguel.id_cliente, aluguel.data_entrada)
  INTO Despesas_Cliente;
   
   IF(new.data_saida IS NOT NULL) THEN
   UPDATE aluguel
   SET new.despesas_totais = Despesas_Cliente ;  
   END IF;
END;
//

Minha dúvida é especificamente neste trecho:

  CALL  Despesas_por_id_SP(aluguel.id_cliente, aluguel.data_entrada)
  INTO Despesas_Cliente;

Como é a sintaxe certa para chamar uma stored procedure dentro de uma trigger e armazenar o valor dela em uma variável declarada na própria trigger?

Desde já agradeço pela atenção.

Abçs;

#2

Olá João,

Veja se utilizando um parâmetro de entrada inout você não consegue retornar na sp o que deseja para usar na trigger.

Abraços.

Fabiano Abreu
Papo SQL | Um blog com tutoriais, dicas e truques sobre SQL

#3

Fala Fabiano, valeu pela sua dica…
Então, eu fiz as seguintes alterações:

DROP PROCEDURE IF EXISTS Despesas_por_id_SP;  
DELIMITER $$   
// Aqui eu passarei como parâmetro o id do cliente, e a data em que ele entrou no hotel  
CREATE PROCEDURE Despesas_por_id_SP (INOUT id_cliente INT, INOUT data_aluguel DATETIME)  
BEGIN  
SELECT sum(despesas.valor_despesa)  
   FROM despesas,aluguel_despesas,aluguel  
   WHERE aluguel.id_cliente = aluguel_despesas.id_cliente  
   AND aluguel.data_entrada = aluguel_despesas.data_entrada  
   AND aluguel.id_quarto = aluguel_despesas.id_quarto  
   AND aluguel_despesas.id_despesa = despesas.id_despesa  
//Passando os parâmetros  
   AND (aluguel.id_cliente = id_cliente)  
   AND (aluguel.data_entrada = data_aluguel);  
END $$   
DELIMITER ;   
COMMIT

aí, quando eu vou chamar a procedure da seguinte forma:

// Isso é pra trazer o resultado das despesas totais de um cliente que está consumindo uma despesa na tabela de despesas_aluguel
CALL Despesas_por_id_SP (9, "2013-04-08 11:49:49")

dá o seguinte erro:

SQL ERROR (1414) OUT or INOUT argument 1 for rotine hotel.Despesas_por_id_SP is not a variable or NEW pseudo-variable in BEFORE trigger.

O que pode ser o erro?
E no caso da procedure dar certo, a sintaxe correta para eu passar o valor do resultado dessa procedure para uma variável dentro da trigger é :

DECLARE Despesas_Cliente double;
CALL  Despesas_por_id_SP(aluguel.id_cliente, aluguel.data_entrada)  
INTO Despesas_Cliente;

?

#4

Pensei em uma lógica aqui…

defini a procedure com 3 parâmetros, sendo eles 2 de entrada e 1 de saída:

DROP PROCEDURE IF EXISTS Despesas_por_id_SP;    
DELIMITER $$     
// Aqui eu passarei como parâmetro o id do cliente, e a data em que ele entrou no hotel    
CREATE PROCEDURE Despesas_por_id_SP (IN id_cliente INT, IN data_aluguel DATETIME, OUT resultado_procedure DOUBLE)    
BEGIN 
SET resultado_procedure (   
SELECT sum(despesas.valor_despesa)    
   FROM despesas,aluguel_despesas,aluguel    
   WHERE aluguel.id_cliente = aluguel_despesas.id_cliente    
   AND aluguel.data_entrada = aluguel_despesas.data_entrada    
   AND aluguel.id_quarto = aluguel_despesas.id_quarto    
   AND aluguel_despesas.id_despesa = despesas.id_despesa    
//Passando os parâmetros    
   AND (aluguel.id_cliente = id_cliente)    
   AND (aluguel.data_entrada = data_aluguel) 
                                     );   
END $$     
DELIMITER ;     
COMMIT

e na trigger, estou tentando o seguinte:

//Minha dúvida está especificamente nesta parte... Como eu posso setar o valor da variável Despesas_Cliente com o mesmo valor do parâmetro de saída resultado_procedure definido na procedure? 
DECLARE Despesas_Cliente double;
IF(new.data_saida IS NOT NULL) THEN
CALL Calcula_Despesas_SP(new.id_cliente_tb, new.data_entrada)
SET Despesas_Cliente = resultado_procedure;
UPDATE aluguel
SET aluguel.valor_despesas = Despesas_Cliente ;   
END IF;
#5

Consegui colocar o resultado da procedure em uma variável da seguinte forma…

DROP PROCEDURE IF EXISTS Despesas_por_id_SP;      
DELIMITER $$       
// Aqui eu passarei como parâmetro o id do cliente, e a data em que ele entrou no hotel      
CREATE PROCEDURE Despesas_por_id_SP (IN id_cliente INT, IN data_aluguel DATETIME, OUT resultado_procedure DOUBLE)      
BEGIN   
SELECT sum(despesas.valor_despesa)      
   FROM despesas,aluguel_despesas,aluguel      
   WHERE aluguel.id_cliente = aluguel_despesas.id_cliente      
   AND aluguel.data_entrada = aluguel_despesas.data_entrada      
   AND aluguel.id_quarto = aluguel_despesas.id_quarto      
   AND aluguel_despesas.id_despesa = despesas.id_despesa      
//Passando os parâmetros      
   AND (aluguel.id_cliente = id_cliente)      
   AND (aluguel.data_entrada = data_aluguel)   
   INTO resultado_procedure;     
END $$       
DELIMITER ;       
COMMIT

E na trigger eu coloquei assim:

DELIMITER //
DROP TRIGGER IF EXISTS `hotel`.`Controle_Aluguel_TR`;
CREATE TRIGGER `hotel`.`Controle_Aluguel_TR`
AFTER UPDATE ON hotel.aluguel
FOR EACH ROW
BEGIN

DECLARE Despesas_Cliente double;

IF(new.data_saida IS NOT NULL) THEN
CALL Calcula_Despesas_SP(new.id_cliente, new.data_entrada, @resultado);
SELECT @resultado INTO Despesas_Cliente;
END IF;
UPDATE alg_aluguel_tb
SET new.valor_despesas = Despesas_Cliente
WHERE alg_aluguel_tb.data_entrada = old.data_entrada;
END;
//

Na hora de criar tanto a trigger quanto a procedure dá certo. Porém na hora de executar, dá o seguinte erro:

"SQL Error (1442): Can’t update table ‘aluguel’ in stored function/trigger because it is already used by statement

which invoked this stored function/trigger."

Esse erro, pelo que eu li em outros fóruns, está relacionado ao fato do MYSQL não aceitar você dar um update na mesma tabela em que a trigger está sendo criada. No caso, eu não poderia dar um update no campo despesas_totais da tabela aluguel, pois eu estou criando a trigger como AFTER UPDATE na própria tabela aluguel. Eu fiz um teste criando uma nova tabela com o nome de teste e com os campos id_teste e valor e criei a trigger da seguinte forma:

DELIMITER //
DROP TRIGGER IF EXISTS `hotel`.`Controle_Aluguel_TR`;
CREATE TRIGGER `hotel`.`Controle_Aluguel_TR`
AFTER UPDATE ON hotel.aluguel
FOR EACH ROW
BEGIN

DECLARE Despesas_Cliente double;

IF(new.data_saida IS NOT NULL) THEN
CALL Calcula_Despesas_SP(new.id_cliente, new.data_entrada, @resultado);
SELECT @resultado INTO Despesas_Cliente;
END IF;
UPDATE teste
SET teste.valor = Despesas_Cliente;
END;
//

E dessa forma funcionou…
Será que eu terei que criar outra tabela para calcular essas despesas totais, ou vou ter que mudar o campo de tabela? Não faria sentido esse campo ficar em outra tabela sem ser a tabela aluguel…

#6

Deu certo aqui galera…
Eu só coloquei a trigger para ser executada antes que a ação ocorra, e tirei o “UPDATE aluguel”. O mysql joga esse erro, pois se ele não jogasse iria cair em um loop infinito(A trigger iria acontecer depois de um update na tabela, e o conteúdo da trigger iria dar um update na tabela, ou seja: quando a trigger fosse executada, ela seria ativada por ela mesma).

DELIMITER //  
DROP TRIGGER IF EXISTS `hotel`.`Controle_Aluguel_TR`;  
CREATE TRIGGER `hotel`.`Controle_Aluguel_TR`  
BEFORE UPDATE ON hotel.aluguel  
FOR EACH ROW  
BEGIN  
  
DECLARE Despesas_Cliente double;  
  
IF(new.data_saida IS NOT NULL) THEN  
CALL Calcula_Despesas_SP(new.id_cliente, new.data_entrada, @resultado);  
SELECT @resultado INTO Despesas_Cliente;  
END IF;    
SET teste.valor = Despesas_Cliente;  
END;  
//

Obrigado a todos.

#7