Seções iMasters
MySQL

Usando variáveis no MySQL

Olá gente. Espero que tenham gostado do
meu último artigo. Dessa vez vou falar sobre um recurso
menos conhecido. Vamos lá!

O MySQL possui um recurso que até alguns dias
atrás achava inútil. Entretanto salvou minha vida num relatório
que tive de gerar. Por isso resolvi escrever este artigo para
mostrar funcionamento das variáveis no MySQL.

Declarar variáveis no MySQL é simples, encontrar
um modo de usá-las é mais complicado.

Para declarar uma variável basta utilizar a sintaxe:

@variavel:=valor

Vamos testar esta sintaxe:

mysql> select @dt:=1;
+———+
| @dt:=1 |
+———+
| 1
+———+

Você deve ter feito a mesma coisa que eu quando
vi isso. “Onde eu vou usar este recurso?”

Bom, vou apresentar a solução onde utilizei este
recurso, e vocês poderão decidir se este recurso pode ser útil
no seu dia a dia ou não.

Utilizo aqui o MySQL 4.1.8, que possui o recurso
de subqueries, utilizado nesse relatório.

1ª Parte:

A primeira parte do relatório é a parte fácil.
Um select simples utilizado para buscar dados de uma tabela e
apresentá-los na tela.

SELECT
conta as Cod,
tit as Titulo,
dte as Emissão,
dtv as Vencimento,
vlr as Valor,
dtp as Pagamento
FROM contas
WHERE (dtp is null or dtp=’0000-00-00′) and cliente = 5896
ORDER BY dtv ASC

O select acima trás os dados referentes aos boletos
de cobrança do cliente 5896 que não foram pagos, conforme mostrado
abaixo.

+—–+———————+————–+—————+———-+—————+

| Cod | Titulo                    
| Emissão     | Vencimento | Valor    
| Pagamento |
+—–+———————+————–+—————+———-+—————+

| 151 | BOLETO 01/2005 | 2005-01-03 | 2005-01-10 | 680.00  
| 0000-00-00 |
| 1     | Boleto 02/2005    
| 2005-02-01 | 2005-02-05 | 1483.28 | NULL          
|
+—–+———————+————–+—————+———-+————–+

2ª Parte.

Até aqui tudo certo, e muito fácil. Entretanto
para concluirmos o relatório será necessário mostrar se o cliente
recebeu ou não o boleto. Após a geração do
boleto, é enviado um email para o cliente com o link de
onde o boleto está, ao visualizar o boleto o cliente aciona
um script que grava em uma tabela o dia, hora e data de vencimento
do boleto, bem como seu código de cliente. Assim na tabela emailslidos
temos os registros:

+——-+————————–+—————+——————+——+

| id        | lido                             
| dtv               |
ip                      |
cli    |
+——-+————————–+—————+——————+——+

| 00001 | 2005-03-01 11:43:01 | 2005-03-05 | 201.6.100.118 | 5942 |

+——-+————————–+—————+——————+——+

O nosso relatório deve apresentar, além dos registros
da primeira parte, uma coluna contendo a data e hora da visualização
do boleto caso este tenha sido visto.

À primeira vista, a utilização de um Join
seria o bastante, entretanto não consegui encontar um solução
plausível. Sendo assim, pensei em usar subqueries. Aí apareceu
outro problema.

Além de saber o código do cliente, seria necessário
saber a data de vencimento de cada boleto, para identificar a
data e hora de leitura do boleto certo. Veja o select abaixo.

SELECT
conta as Cod,
tit as Titulo,
dte as Emissão,
dtv as Vencimento,
vlr as Valor,
dtp as Pagamento ,

(select lido from emailslidos where cliente=5896
and dtv=????-??-?? order by DATE_FORMAT(lido,’%h:%i:%s %Y-%m-%d’)
desc limit 1) as lido

FROM contas
WHERE (dtp is null or dtp=’0000-00-00′) and cliente=5896
ORDER BY dtv ASC

Note que coloquei um subquery que equivale a um
campo (lido) da minha consulta principal. Se o único parâmetro
necessário fosse o codigo do cliente, um join teria resolvido
e no caso de subqueries, a nossa consulta acima estaria resolvida.
Entretanto, faz-se necessário a utilização do campo dtv (data
de vencimento) que vai justamente identificar o boleto. “E de
onde virá o valor do Campo data de vencimento?”.

Vamos separar a select:

SCT1 = SELECT conta as Cod,tit
as Titulo,dte as Emissão,dtv as Vencimento,vlr as Valor,dtp
as Pagamento FROM contas WHERE (dtp is null or dtp=’0000-00-00′)
and cliente=5896 ORDER BY dtv ASC

SCT2 = (select lido from
emailslidos where cliente=5896 and dtv=????-??-?? order by DATE_FORMAT(lido,’%h:%i:%s
%Y-%m-%d’) desc limit 1) as lido

Ou seja, SCT1 é a select principal, e SCT2 é a
subquery.

A resposta para a pergunta “E de onde virá o valor
do Campo data de vencimento?” está na SCT1. Ou seja, para cada
registro apresentado por SCT1, teremos de obter o dtv para usarmos
em SCT2.

Usando uma linguagem de programação seria possível
fazer uma consulta e depois outra, mais aí estaríamos deixando
de aproveirar os recursos do MySQL, sendo assim, vamos à solução
com variáveis.

Voltando ao nosso primeiro exemplo e modificando-o
um pouco:

mysql> select @dt:=1,@dt+1;

+———+———+
| @dt:=1 | @dt+1  |
+———+———+
| 1 _____| 2 ____ |
+———+———+

Note que setamos a variável dt como 1 em um campo
e utilizamos ela no outro campo com sucesso sem que fosse necessário
fazer duas consultas, uma para setar e outra para usar a variável.
O mesmo princípio pode ser utilizado no nosso relatório.

SELECT
conta as Cod,
tit as Titulo,
dte as Emissão,

@dt:=dtv as Vencimento,
vlr as Valor,
dtp as Pagamento ,

(select lido from emailslidos where cliente=5896
and
dtv=@dt
order by DATE_FORMAT(lido,\’%h:%i:%s %Y-%m-%d\’) desc limit 1)
as lido

FROM contas
WHERE (dtp is null or dtp=\’0000-00-00\’) and cliente=5896
ORDER BY dtv ASC

Com isso, é possível matar as duas consultas necessárias
em uma linguagem de programação em uma única consulta com processamento
total em banco.

Até próxima!

Ricardo Luiz dos Santos

é Programador Linux/PHP/MySQL desde 2001, Graduado em Processamento de Dados pela FATEC e Pós-graduado em Redes de Computadores e Comunicação de dados pela UEL. Profissional certificado Conectiva Linux.

Email

Leia os últimos artigos publicados por Ricardo Luiz dos Santos

Comente também

9 Comentários

andre

Bom dia, sou iniciante em desen. de sistemas, trablho com mysql e php.
Lhe pergunto: um INNER JOIN nao resolveria o problema sem criar variaveis? se nao, Porque?

Bráulio Machado Campos

Não testei a utilização do inner, o esquema com a váriável foi o primeiro que testei e funcionou, achei interessante e resolvi passar a solução adiante em forma de artigo, mas se um inner resolvesse tbém poderia ser usado.

Marcos C Anzolin

Veja bem eu usei este recurço pra realizar calculos ele funcina bem quando vc só tem uma unica linha de resposta do banco quando se tem mais de uma e usá o resultado da 1º e mais nada faça um teste com mais de um resulado.

segue me teste aqui.

select
cl.id_compra,
date_format(cl.datahrcompra, ‘%d/%m/%Y %H:%i:%s’) as dthr,
sum(quantidade) as itens,
@vlt:=sum(vlunitario * quantidade) as valortotal,
@dsc:=sum(desconto) as descp,
if(@dsc >= 1,@vlt – (@vlt * (@dsc / 100)) ,@vlt) as re
from
frt_pedido_cliente cl inner join frt_pedidoresumido pr using(id_compra)
group by pr.id_compra

Fábio Paiva

Cara, me cadastrei no imaster só para te dar parabéns, fino demais esse script, salvou minha vida…
olha o que eu fiz:

select dm.nome_motivo,
@codigo:=dm.cod_motivo,
count(d.cod_objetivo_denuncia) as quantidade,
(select count(d.cod_objetivo_denuncia) as excluidos
from tab_denuncia d
inner join tab_denuncia_acompanhamento da on (da.cod_denuncia=d.cod_denuncia)
where da.status_denuncia=6/*excluidas*/ and d.cod_objetivo_denuncia = @codigo
group by dm.cod_motivo
) as excluidos
from tab_denuncia_motivos dm
left join tab_denuncia d on (dm.cod_motivo = d.cod_objetivo_denuncia)
left join tab_denuncia_acompanhamento da on (da.cod_denuncia=d.cod_denuncia)
group by dm.cod_motivo

Wagner Demetrio Nascimento

Pessoal eu vi o ricardo mostrando um post com a utilização de variáveis segue aqui mais uma.

No caso de uma subquery na clausula select, se voce precisar mostrar este valor duas vezes o melhor é que seja com variável para evitar repetir a sql novamente e ter um processamento desnecessário

Ex:

SELECT @INSCRITOS:=(SELECT COUNT(*)
FROM ALUNO_AVALIACAO
WHERE PROFESSOR_ID = 6
AND DISCIPLINA_ID = 925) AS INSCRITOS
// Aqui eu preciso usar novamente a mesma sql para um cálculo.
,@INSCRITOS * 10 AS TOTAL
,RESULTADO_AVALIACAO_ID, PERGUNTA_ID, DISCIPLINA_ID, ALUNO_ID, PROFESSOR_ID, VALOR
FROM RESULTADO_AVALIACAO R;

Espero ter ajudado

Att.

Joao Silverio

Bando de Amadores… Utilizar Data de Vencimento como ID do boleto… E se for emitido o cliente tiver vários boletos vencendo no mesmo dia otário… Volta pra escola… Tu é burro pra caralho…

    Pedro

    João, parabéns, você tem uma didática muito boa! Vou começar a utilizar a mesma técnica com você para corrigir essa frase ridícula que você escreveu:
    “E se for emitido o cliente tiver vários boletos vencendo no mesmo dia otário” – João, cacete!, quando quiser escrever alguma coisa que tenha sentido, use VIRGULAS! O que você quis dizer com essa merda de frase? Você ‘emite um cliente’? O que é um ‘dia otário’? PORRA! Pegue tua lancheira e volte para a pré-escola, sua anta.

bonus

Esse post ajudou-me bastante.
Não estava conseguindo resolver um problema de uma consulta e não conhecia as variáveis no MySQL.
Parabéns!

Qual a sua opinião?