iMasters | Por uma internet mais criativa e dinâmica

Terça-feira, 15/03/2005 - 14:19 - Por Ricardo Luiz dos Santos
Seções relacionadas:

• MySQL

• Imprimir
• Indicar
• Digg it
• Del.icio.us
• Twitter
• 4 comentários

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!

Todos os artigos de Ricardo Luiz dos Santos

15/03/2005 - Usando variáveis no MySQL 22/02/2005 - Comando Show

4 comentários publicados

• 1. Desculpe

  Quarta-feira, 03/08/2005, por 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?
  

  Responder comentário

• 2. não testei

  Quarta-feira, 03/08/2005, por 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.

  Responder comentário

• 3. Mal Funcionamento

  Sábado, 21/04/2007, por 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

  Responder comentário

• 4. Perfeito - salvou minha vida

  Quinta-feira, 21/08/2008, por 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
  

  Responder comentário

Poste um comentário

(clique se não for cadastrado)

Login: Senha: Titulo: digite aqui seu comentário Site/blog: Enviar Receber notificação de novos comentários

Sobre o autor

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.

Indique para um amigo

Escreva aqui sua mensagem! ok

Tags relacionadas

apple lançamento design google youtube celular ti desenvolvimento 3g mercado linux crescimento web seguranca mobile brasil yahoo iphone windows photoshop microsoft internet gerência publicidade php tecnologia

TI SHOP Produtos iMasters

• Lançamento: CD-ROM Treinamento Aplicado de SQL - Lançamento! Treinamento Aplicado de SQL - Aprenda a trabalhar com SQL com bancos de dados Oracle e SQL Server. São mais de 100 tópicos explicados por Mauro Pichilliani, um articulistas mais lidos do iMasters. Aproveite! Apenas R$ 69,90 no TI SHOP.
• Lançamento: Livro iMasters "O Encontro de 2 Mundos"- Este livro conta com 56 crônicas de profissionais mais admirados e influentes do mercado brasileiro de Internet. Aproveite o preço especial para leitores do iMasters. Apenas R$ 40,00 e envio imediato!
• DVD Curso Completo de Photoshop - Do conceito à finalização Lançamento! Curso Completo de Photoshop, em DVD, com mais de 230 aulas dividas em 4 módulos: conceito, básico, avançado e finalização. Apenas R$ 69,50 no TI SHOP - Frete com 50% de desconto
• DVD Javascript Starter - Curso Completo Com mais de 9 horas de vídeo-aulas, é um curso completo sobre Javascript. Ideal para quem deseja aprender a linguagem. Apenas R$ 64,90 no TI SHOP - Frete com 50% de desconto!