1 Banco de Dados SQL Stored Procedures Elaini Simoni Angelotti.

Slide 1 1 Banco de Dados SQL Stored Procedures Elaini Simoni Angelotti Slide 2 2 Stored Prodecure (sp) São semelhantes a procedimentos e subrotinas em outras linguagens: –Podem ser chamadas por outra sp ou por aplicativos desenvolvidos em PHP, ASP, Delphi, VB, etc –Podem receber e retornar parâmetros –Podem conter instruções de controle e laços Uma stored procedure (sp) é uma coleção de comandos nomeada que é salva no BD. Slide 3 3 Uma vez criada, uma sp pode ser executada sempre que necessário, simplesmente chamando a sp. Vantagens de utilizar sp: –Facilitar a manutenção e a alteração das aplicações –Ocultar a complexidade de acesso ao BD –Poder receber parâmetros de entrada e retornar resultados –Reduzir o tráfego de rede gerado pela aplicação –Facilitar e centralizar o gerenciamento de permissões –Melhorar a velocidade de execução Slide 4 4 Para escrever uma sp pode-se utilizar instruções de comparações (if), loops (while), operadores, variáveis, etc Para o início e fim de um bloco usa-se BEGIN e END Exemplo: Escrever uma sp que imprima Hello world CREATE PROCEDURE sp_Teste AS BEGIN SELECT o famoso Hello World END Para Executar a sp: EXEC sp_Teste Slide 5 5 Pode-se declarar uma variável em uma sp para armazenar valores. Usa-se o comando DECLARE para declarar variáveis. CREATE PROCEDURE sp_Teste1 AS DECLARE @msg varchar (100) BEGIN SELECT @msg = o famoso Hello World PRINT @msg END Para Executar a sp: EXEC sp_Teste1 Slide 6 6 Sp com Parâmetros de Entrada Permite a criação de sp mais flexíveis Os parâmetros são sempre declarados como @ e devem ter o tipo de dados definido. –Exemplo 1: Crie uma sp para encontrar todas as pessoas que emprestaram uma fita em um determinado ano (para o BD Locadora). O ano será passado como parâmetro de entrada e a sp utilizará este parâmetro como critério de pesquisa, retornando o nome do cliente, data de empréstimo, data de devolução prevista e data de devolução efetiva. Slide 7 7 CREATE PROCEDURE sp_Emprestimo_Ano @ano int AS BEGIN select nome_cli as 'Nome do Cliente', convert(char, data_emp,103) as 'Data do Emprestimo', convert(char, data_dev_prev, 103) as 'Data da Devolução Prevista', convert(char, data_dev_efet, 103) as 'Data da Devolução Efetiva' from Cliente inner join Emp_dev on cliente.CPF_cli = Emp_dev.CPF_cli where datepart(yy, data_emp) = @ano END EXEC sp_Emprestimo_Ano 2006 Slide 8 8 –Exemplo 2: Crie uma sp para encontrar todas as pessoas que emprestaram uma fita em um determinado período. Para isso, a sp terá dois parâmetros de entrada (data inicial e data final). Esses parâmetros serão utilizados como critério de pesquisa. A sp deve retornar o nome do cliente, data de empréstimo, data de devolução prevista e data de devolução efetiva. Slide 9 9 CREATE PROCEDURE sp_Emprestimo @data_inicio datetime, @data_fim datetime AS BEGIN select nome_cli as 'Nome do Cliente', convert(char, data_emp,103) as 'Data do Emprestimo', convert(char, data_dev_prev, 103) as 'Data da Devolução Prevista', convert(char, data_dev_efet, 103) as 'Data da Devolução Efetiva' from Cliente inner join Emp_dev on cliente.CPF_cli = Emp_dev.CPF_cli where data_emp between @data_inicio and @data_fim END EXEC sp_Emprestimo '2005-01-01', '2005-12-31' Slide 10 10 Exemplo 3: usando variáveis, parâmetros e instrução IF.. ELSE –Crie uma sp que receba uma substring do nome de um fornecedor e conte quantos fornecedores existem com aquela substring. Caso nenhum fornecedor tenha a substring, uma mensagem deve ser enviada notificando o usuário. Caso a quantidade de fornecedor seja maior que zero, então o usuário também deve ser notificado sobre o número de pessoas com a substring. Slide 11 11 CREATE PROCEDURE sp_BuscaFornecedor @Nome_forn varchar (50) AS DECLARE @Qtde int, @msg varchar (100) BEGIN select CNPJ_forn, Nome_forn fromFornecedor where nome_forn like '%' + @Nome_forn + '%' SET @Qtde = (select count(*) from Fornecedor where nome_forn like '%' + @Nome_forn + '%') IF @Qtde = 0 BEGIN select @msg = 'Nenhum fornecedor contem a substring ' + @Nome_forn print @msg END ELSE BEGIN select @msg = 'existem ' + convert(char,@Qtde) print @msg select @msg = 'fornecedores com a substring ' + @Nome_forn print @msg END EXEC sp_BuscaFornecedor 'M' Slide 12 12 Exemplo 4: –Crie uma sp que receba como parâmetros de entrada o nome de duas categorias de filmes e conte o número de empréstimos para cada uma das duas categorias em um determinado ano (que também será passado por parâmetro). O número de empréstimos de cada categoria deve ser armazenado em uma variável local. Em seguida, um teste é feito emitindo a mensagem de qual categoria teve mais filmes emprestados no ano selecionado. Slide 13 13 CREATE PROCEDURE sp_Maior_Emprestimo_Categoria @ano int, @categoria1 varchar(50), @categoria2 varchar(50) AS DECLARE @TotalCategoria1 int, @TotalCategoria2 int, @msg varchar (100) BEGIN SET @TotalCategoria1 = (select count(*) from fita inner join Emp_dev on fita.cod_fita = Emp_dev.cod_fita where categoria = @Categoria1 AND datepart(yy,data_emp) = @ano) SET @TotalCategoria2 = (select count(*) from fita inner join Emp_dev on fita.cod_fita = Emp_dev.cod_fita where categoria = @Categoria2 AND datepart(yy,data_emp) = @ano) IF (@TotalCategoria1 > @TotalCategoria2) BEGIN set @msg = 'o número de emprestimos de fitas da categoria ' + @categoria1 + ' é maior' print @msg END ELSE IF (@TotalCategoria2 > @TotalCategoria1) BEGIN set @msg = 'o número de emprestimos de fitas da categoria ' +@categoria1 + ' é menor' print @msg END ELSE BEGIN set @msg = 'o número emprestimos de fitas das duas categoria são iguais' print @msg END EXEC sp_Maior_Emprestimo_Categoria 2005, 'comedia', 'terror' Slide 14 14 Usando a Instrução WHILE Faz com que um conjunto de comandos execute enquanto uma dada condição for verdadeira Exemplo 5: –Crie uma nova tabela chamada Soma_Naturais com dois atributos inteiros: número e Soma_acumulada create table Soma_naturais ( Numero int, Soma_acumulada int ) Slide 15 15 Em seguida crie uma sp que calcule a soma dos n primeiros números e armazene as somas nas respectivas colunas. Por exemplo: NúmeroSoma Acumulada 11 23 36 CREATE PROCEDURE sp_CalculaSoma @num int As DELETE from Soma_naturais declare @contador int set @contador = 1 declare @soma int set @soma = 0 BEGIN WHILE (@contador 16 Instrução WHILE e BREAK CREATE PROCEDURE sp_Preço AS DECLARE @preco_MAX money WHILE (SELECT AVG(preco) FROM fita) < 5 BEGIN UPDATE fita SET preco = preco + 1.5 SET @preco_MAX = (Select MAX(preco) FROM fita) IF (@preco_MAX > 7) BREAK PRINT 'Isso foi apenas um teste!' select * from fita END exec sp_Preço Slide 17 17 Parâmetros de Saída para sp Para definir um parâmetro de saída basta colocar a palavra OUTPUT após a definição do tipo de parâmetro CREATE PROCEDURE sp_saída @saida int OUTPUT Crie uma sp que retorne o valor médio de preço das fitas para um estado que será passado como parâmetro de entrada Slide 18 18 CREATE PROCEDURE sp_MediaPreçoEstado @estado char(2), @media float OUTPUT AS BEGIN SET @media = (Select AVG(preco) as Media from fita INNER JOIN Fornecedor_Fita ON Fornecedor_Fita.cod_fita = fita.cod_fita INNER JOIN Fornecedor ON Fornecedor_Fita.CNPJ_forn = Fornecedor.CNPJ_forn where UF_forn = @estado) END PARA EXECUTAR: declare @resultado float exec sp_MediaPreçoEstado 'PR', @Resultado OUTPUT Print (@Resultado) Slide 19 19 Para alterar uma sp usa-se o seguinte comando: ALTER PROCEDURE nome_da_sp Para apagar uma sp usa-se o seguinte comando: DROP PROCEDURE nome_da_sp Para criptografar os comandos de uma sp: CREATE PROCEDURE sp_Clientes WITH ENCRYPTION AS select * from Cliente Exec sp_helptext sp_clientes Exec sp_helptext sp_Teste