Olá,
hoje trago aqui a criação e utilização de Stored Procedure no SQL.
De uma forma simples, o SQL é constituído por tabelas (reservatórios de dados), views (baseadas nas tabelas, são constituídas por filtros de forma a mostrar apenas alguns dados), procedimentos (stored procedures) e funções.
Os procedimentos e as funções são a parte mais criativa do SQL, é aqui que se programa em SQL. É código trabalhado que fica armazenado no servidor SQL e pode ser executado a qualquer altura, é algo que se faz uma vez e fica para sempre, pode ser reutilizado.
Uma função é algo que pode ser chamado na execução do código SQL e retorna sempre um valor (seja escalar ou conjunto de registos em formato tabela).
Existem funções internas do sistema, por exemplo a função year() que retorna o valor do ano para um campo do tipo data:
SELECT year(Campodata), descritivo from tblxxx
Outras há que permitem funcionar com campos numéricos, por exemplo max() que retorna o valor máximo do campo:
SELECT max(numero) from tblxxx
Em relação aos procedimentos (stored procedures), também existem alguns internos (sistema), iniciados por sp_xxx. Temos o caso do sp_send_dbmail que, ao ser executado, permite enviar email através do SQL:
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'teste',
@recipients = 'alguem@hotmail.com',
@body = 'Texto a escrever',
@subject = 'Assunto' ;
Note-se que previamente é preciso configurar o servidor para envio de email…
Repare-se que uma função é chamada dentro do pedido SQL, dentro do “select”; o stored procedure tem de ser executado fora desse pedido.
Por vezes (muitas vezes mesmo!) é necessário construir quer funções quer stored procedures para os mais variados efeitos. Ambos serão criados, guardados no servidor dentro da respectiva DB, e ambos poderão ser chamados/executados a qualquer altura.
Imaginemos, então, o seguinte cenário: uma DB “performance” onde estão armazenadas duas tabelas, “TblVeiculos” e “Abastecimentos“.
Fazendo a selecção de dados de ambas:
select * from performance.dbo.TblVeiculos
select * from performance.dbo.Abastecimentos
produzimos os seguintes dados:
A primeira tabela é composta por dados relativos a veículos, cada linha é única, cada linha refere-se a um único veículo (distinto dos outros).
Nesta minha pobre tabela tenho apenas quatro veículos, com identificador diferente, matrícula diferente e…bem, pode ter cores iguais!…
Na segunda tabela, existem dados de abastecimento de combustível para todos os veículos: em que kilometragem se efectuou, que tipo de combustível, preço a pagar, etc…
Imaginemos, então, que nos pedem para implementar um processo que nos mostre os custos (apenas de abastecimento de combustível) para os veículos de determinada cor…sabe-se lá porquê, podem estar a trabalhar num tipo de tinta redutora de combustível!
O pedido envolve duas vertentes, uma para juntar num só local os veículos de uma determinada cor, depois fazer o somatório de todas as despesas para cada um desses veículos.
A ideia será fazer a amostragem em dois quadros, talvez num interface ASP/PHP após clicar numa determinada cor; pode ser também para implementar mapas de Excel ou outros.
Este processo pode ser realizado através da construção de um Storage Procedure (SP) no SQL.
Vamos então criar um SP que produzirá duas tabelas, uma com a listagem de veículos da mesma cor (variável conforme input do utilizador/programa):
VeiculosMesmaCor
e outra com a listagem resumo dos custos para cada veículo dessa mesma cor:
ResumoVeiculoCustos
Para se perceber melhor, a imagem seguinte mostra já o resultado pretendido, para o caso da cor cinza:
Para a cor cinza a amostragem comporta os veículos 1,2 e 4.
Estes quadros podem ser facilmente exportados para Excel ou colocados num interface ASP ou PHP ou…
Vamos, agora, à implementação do Stored Procedure, chamei-lhe SPVeiculosConsumoCor:
O algoritmo para a criação dos primeiros resultados resume-se mais ou menos nisto:
- Verificar se existe a tabela
VeiculosMesmaCor.
- Caso positivo, elimina os dados da tabela
VeiculosMesmaCor.
- Insere nesta mesma tabela, para cada iteração, dados existentes na tabela
Abastecimentos.
- Os dados a inserir são apenas os que possuem cor do carro igual à especificada pelo utilizador.
O algoritmo para a criação dos resultados finais :
- Elimina dados existentes na tabela
ResumoVeiculoCustos.
- Insere nesta mesma tabela o somatório dos dados da tabela
VeiculosMesmaCor , agrupados por identificador de veículo.
Para executar o SP deveremos utilizar o seguinte comando :
exec dbo.SPVeiculosConsumoCor 'cinza';
(isto para o caso do cinza)
Após executar este SP, serão actualizados os dados das duas tabelas anteriores, VeiculosMesmaCor e ResumoVeiculoCustos.
Em resumo, o utilizador final apenas terá de efectuar os seguintes passos:
EXEC dbo.SPVeiculosConsumoCor 'cinza';
SELECT * from dbo.VeiculosMesmaCor
SELECT * from dbo.ResumoVeiculoCustos
e os resultados serão imediatos.
Espero que vos seja útil,
JG
