Tópicos Especiais em Sistemas de Bancos de Dados Prof. M.Sc. Marcelo Fermann Guimarães mscfermann @ gmail.com 17 março 2020 Agenda - Stored Procedures / Functions - APS – Atividade Prática Supervisionada Stored Prodecure (sp) • Uma stored procedure (sp) é uma coleção de comandos nomeada que é salva no BD. • 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 • 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 • SP com variável interna: comando DECLARE 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 SP com Parâmetros de Entrada • Os parâmetros são sempre declarados como @ e devem ter o tipo de dados definido. 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, Emp_dev where datepart(yy, data_emp) = @ano and cliente.CPF_cli = Emp_dev.CPF_cli END EXEC sp_Emprestimo_Ano 2017 SP com Parâmetros de Saída 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_MediaPrecoEstado @estado char(2), @media float OUTPUT AS BEGIN SET @media = (Select AVG(preco) as Media from fita, Fornecedor_Fita, Fornecedor where UF_forn = @estado AND Fornecedor_Fita.cod_fita = fita.cod_fita AND Fornecedor_Fita.CNPJ_forn = Fornecedor.CNPJ_forn) END PARA EXECUTAR: declare @resultado float exec sp_MediaPrecoEstado 'PR', @Resultado OUTPUT Print (@Resultado) • STORED PROCEDURES (SP) - Exercício: »Considerando o BD TrabNovela: – Criar uma StoredProcedure que retorne 0 caso a inclusão de um registro na Tabela NovelaPersonagem foi correta ou 9 caso o registro já exista: CREATE PROCEDURE sp_IncluiNovelaPersonagem @codNovela int, @codPersonagem int, @retorno int OUTPUT • FUNCTION: »Objetivo – O usuário pode criar suas próprias funções para auxiliar na busca da informação. As user functions podem receber um ou mais parâmetros, e não aceitam parâmetro de output. – As funções de usuário podem ser de três tipos: - Scalar - Multi-statement Table-valued - In-Line Table-valued • FUNCTION: Scalar: são bem parecidas com as bult-in functions, que existem no banco de dados como a Getdate(). Este tipo de função retorna apenas um parâmetro. Ex.: retorna o endereço mais o código postal. CREATE Function sc_RetornaSupplier(@Supplier smallint) RETURNS Nchar(40) AS BEGIN DECLARE @Retorno Nchar(40) SET @Retorno=(SELECT Address + " *** " + PostalCode FROM Suppliers WHERE SupplierID=@Supplier) RETURN @Retorno END • FUNCTION: Scalar: Modo de usar: SELECT ProductID , productName, dbo.sc_RetornaSupplier(SupplierID) as MinhaFuncao FROM Products • FUNCTION: Multi-statement Table-valued É utilizada para lógicas mais complexas que costumam conter condicional. Outra diferença considerável também é que este tipo de função retorna uma tabela. É possível definir chave primária, identity, etc, no retorno da tabela, deixando bem claro que este tipo de função retorna dados confiáveis e íntegros. – Ex. Lista várias situações dos produtos em estoque. • FUNCTION: Multi-statement Table-valued CREATE Function ms_RetornaProducts(@Valor decimal(18,2)=NULL, @Descontinuado bit=NULL) RETURNS @TabelaProduto table(Codigo INT PRIMARY KEY, Nome varchar(50), Estoque varchar(50), obs varchar(50)) AS BEGIN IF @Valor IS NOT NULL INSERT INTO @TabelaProduto SELECT ProductID,ProductName,unitsInStock, case when unitsInstock<10 then "Estoque baixo" else "Estoque ok" end FROM dbo.Products WHERE unitPrice>@Valor …. • FUNCTION: Multi-statement Table-valued CREATE Function ms_RetornaProducts(@Valor decimal(18,2)=NULL, @Descontinuado bit=NULL) …. ELSE IF @Descontinuado=1 INSERT INTO @TabelaProduto SELECT ProductID,ProductName,"Sem estoque", case when unitsInstock<5 then "Possui estoque" else "limpar estoque" end FROM dbo.Products WHERE Discontinued=@Descontinuado RETURN END • FUNCTION: Multi-statement Table-valued CREATE Function ms_RetornaProducts(@Valor decimal(18,2)=NULL, @Descontinuado bit=NULL) Modo de usar: SELECT * FROM dbo.ms_RetornaProducts(18.00, null) Retorno: • FUNCTION: Multi-statement Table-valued CREATE Function ms_RetornaProducts(@Valor decimal(18,2)=NULL, @Descontinuado bit=NULL) Modo de usar para ver os descontinuados: SELECT * FROM dbo.ms_RetornaProducts(null,1) Retorno: • FUNCTION: Multi-statement Table-valued CREATE Function ms_RetornaProducts(@Valor decimal(18,2)=NULL, @Descontinuado bit=NULL) É possível ainda tratar esta função como uma tabela propriamente dita. Por exemplo, selecionando campos e filtrando retorno: SELECT codigo,obs FROM dbo.ms_RetornaProducts(5.00,null) WHERE Nome LIKE "%queso%" Retorno: • FUNCTION: In-Line Table-valued Muito utilizadas para parametrizar views. Este tipo de função não requer BEGIN, END na criação do seu corpo. O retorno deste tipo de função também é uma tabela, mas costuma-se criar o esquema em momento de execução. Exemplo: CREATE Function il_RetornaCustomers(@Regiao nvarchar(30)) RETURNS table AS RETURN (SELECT CustomerID as Codigo, CompanyName as Nome FROM Customers WHERE Region=@Regiao) • FUNCTION: In-Line Table-valued Modo de usar: SELECT * FROM dbo.il_RetornaCustomers("WA") Retorno: Tópicos Especiais em Sistemas de Bancos de Dados • Links: – Youtube Professor Drausio https://www.youtube.com/watch?v=0r84pdFbulQ Aprenda como Criar Funções no SQL Server 21 min. https://www.youtube.com/watch?v=D3uah62lpfo Criar Stored Procedure de Consultar (Básica) 38 min. – Thiago Pastorello Gervazoni – Linha de Código http://www.linhadecodigo.com.br/artigo/687/sql-server-funcoes-de -usuario-user-functions.aspx Tópicos Especiais em Sistemas de Bancos de Dados • Trabalho 2: – Em duplas – Entrega em 24/03/2020 – terça-feira – Entrega via MOODLE AVA Perguntas
