Se você está acompanhando o blog já conhece muitas funcionalidades bacanas do SQL Server 2017, mas chegamos no momento de ir além! No post de hoje conheceremos um recurso muito útil chamado FUNCTIONS.
O que é uma Function?
Uma user defined function (função definida pelo usuário) é uma rotina, normalmente escrita com a linguagem T-SQL , que executa uma ação (como um cálculo complexo) e retorna o resultado dessa ação como um valor. O valor de retorno pode ser um valor escalar (único) ou uma tabela.
Existem também as funções internas do SQL Server que você pode usar para manipular dados.
Funções são muito legais, mas precisam ser usadas com muito cuidado porque melhoraram a produtividade do desenvolvedor, mas você também precisa entender os casos em que seu uso em determinado contexto pode acabar afetando negativamente o desempenho da consulta (conversaremos sobre isso nos próximos posts).
Um conceito importante é o determinismo das funções. As funções determinísticas sempre retornam o mesmo resultado quando recebem um dado conjunto de parâmetros. As funções não determinísticas podem retornar resultados diferentes cada vez que são executadas com um conjunto específico de parâmetros.
Funções internas do SQL
O SQL Server possui um conjunto imenso de funções, por isso antes de sair criando várias e várias funções, veja se a que você procura já existe . Vou listar aqui as funções que eu considero mais importantes…
CAST e CONVERT
São funções que convertem um valor de um tipo para outro. CAST é SQL ANSI e CONVERT é T-SQL.
Exemplo: Converta a string “01/02/2017” para uma data.
CONVERT(DATE, ‘01/02/2017’) OU CAST(‘01/02/2017’ AS DATE)
Dica de ouro… Se você tentar fazer uma conversão sem sentido, por exemplo, converter a palavra DB4B para data, sua query vai falhar. Existem as funções TRY_CAST e TRY_CONVERT que quando usadas não geram um erro se a conversão falhar, retornam um valor nulo.
GETDATE()
GETDATE() é uma função não determinística que que retorna a data e hora da instância do SQL Server na qual você está conectado, de forma resumida, data e hora corrente com o tipo datetime.
DATEPART
Dada uma data, com esta função extraímos parte da data. Por exemplo, quero extrair da data o mês, ou o ano, ou o dia. Esta função recebe como parâmetros a parte que será extraída (DAY, MONTH, YEAR) e uma data.
Exemplo: DATEPART(month, ‘20170212’) retornará o valor 2
DATEADD
Não tente fazer somas com datas diretamente, coisas do tipo ‘20170212’ + 1 , estão totalmente erradas!
Use a função DATEADD, passando como parâmetros o que você vai somar YEAR, ou MONTH, ou DAY, o número que você vai somar e a data na qual você quer fazer a soma.
Por exemplo, adicione um ano a data ‘20170212’: DATEADD(year, 1, ‘20170212’)
DATEDIFF
Retona a diferença em dias, anos, meses entre duas datas.
Por exemplo: qual a diferença em dias entre as datas ‘20160212’ e ‘20170212’?
DATEDIFF(day, ‘20160212’, ‘20170212’)
Substring
Imagina que você precisa somente de uma parte de uma string. Para extrair parte de uma string use a função SUBSTRING passando como parâmetros, a string, a posição inicial e o tamanho da string.
Exemplo: SUBSTRING(‘abcde’, 1, 3) retorna ‘abc’
LEN
Use a função LEN para saber o tamanho de uma string.
EXEMPLO: LEN( ‘abc’) retorna o valor 3
ISNULL
Esta é uma função coringa! Imagina que você quer fazer a soma de dois números, mas um deles é NULL… O retorno dessa soma será NULL. E provavelmente este não é o valor esperado.
Use a função ISNULL para substituir NULL por um valor.
Exemplo: a coluna ABC pode ser NULL e neste caso você quer retornar o valor 0. Use a função ISNULL(ABC, 0)
Funções de agregação
Estas funções agregam um conjunto de valores e retornam um resultado. São usadas com a cláusula GROUP BY.
Existem várias… As mais usadas são listadas abaixo:
- COUNT – Faz uma contagem
- AVG – Retorna o valor médio de uma coluna
- MAX – Retorna o maior valor de uma coluna
- MIN – Retorna o menor valor de uma coluna
- SUM – Retorna a soma de uma coluna
Todas recebem como parâmetro o nome da coluna. Cabe ressaltar que a função COUNT, pode receber como parâmetro o “*”, que indica a contagem de todas as linhas.
Como criar uma função
E se for necessário criar uma função?
Você pode criar funções específicas para o seu contexto de negócio usando o T-SQL.
Função escalar
Lembrando que uma uma função escalar retorna um único valor e que pode receber uma lista de parâmetros
CREATE FUNCTION schema.Nome da Função ( @NomeParâmetro AS Tipo Parâmetro) RETURNS Data Type do retorno da Função AS BEGIN Comandos RETURN Valor que a função deve retornar END
Para você ver o uso de uma função de forma mais prática, vamos criar uma função que subtrai dois valores:
CREATE FUNCTION dbo.SubtracaoPostBlog ( @Valor1 AS INT, @valor2 as INT) RETURNS INT AS BEGIN DECLARE @Retorno AS INT Set @Retorno = @Valor1 - @Valor2 RETURN @Retorno END
Para você validar a sua função, faça um SELECT com valores conhecidos e verifique o retorno
SELECT [dbo].[SubtracaoPostBlog] ( 20,10) GO
Table Valued Function
Este tipo de função retorna uma tabela.
Imagina que dado um parâmetro, você vai executar uma consulta. Este tipo de function que possui um único comando e o retorno é o resultado da execução doeste comando é chamada de Inline Table-Valued Function.
Se a sua function tem vários comandos e retornará uma tabela, ela é chamada de Multi-Statement Table-Valued Function.
Inline Table-Valued Function
CREATE FUNCTION schema.Nome da Função ( @NomeParâmetro AS Tipo Parâmetro) RETURNS Data Type do retorno da Função AS BEGIN RETURN Comando SELECT END
Veja o exemplo abaixo, onde a função retornará o valor total de vendas de cada produto de uma loja (vamos usar nosso banco de dados de testes).
CREATE FUNCTION Sales.ufn_SalesByStore (@storeid int) RETURNS TABLE AS RETURN ( SELECT P.ProductID, P.Name, SUM(SD.LineTotal) AS 'Total' FROM Production.Product AS P JOIN Sales.SalesOrderDetail AS SD ON SD.ProductID = P.ProductID JOIN Sales.SalesOrderHeader AS SH ON SH.SalesOrderID = SD.SalesOrderID JOIN Sales.Customer AS C ON SH.CustomerID = C.CustomerID WHERE C.StoreID = @storeid GROUP BY P.ProductID, P.Name ); GO
Concorda comigo que o retorno deste tipo de função é uma tabela? E uma tabela deve ser usada na cláusula FROM? Sendo assim para testar esse tipo de função você pode executar um comando SELECT usando a function na cláusula FROM.
Select * from Sales.ufn_SalesByStore(934)
Multi-Statement Table-Valued Function.
CREATE FUNCTION schema.Nome da Função ( @NomeParâmetro AS Tipo Parâmetro) RETURNS @Variável que armazena a tabela AS Definição da Tabela AS BEGIN Comandos RETURN @Variável que armazena a tabela END
Usando o exemplo anterior (por falta de criatividade mesmo)… Vou criar a V2 da função.
CREATE FUNCTION Sales.ufn_SalesByStore_V2 (@storeid int) RETURNS @VariavelTabela TABLE (IDProduto int NOT NULL, Nome nvarchar(255) NOT NULL, Total decimal(20,2) NOT NULL) AS BEGIN INSERT INTO @VariavelTabela SELECT P.ProductID, P.Name, SUM(SD.LineTotal) AS 'Total' FROM Production.Product AS P JOIN Sales.SalesOrderDetail AS SD ON SD.ProductID = P.ProductID JOIN Sales.SalesOrderHeader AS SH ON SH.SalesOrderID = SD.SalesOrderID JOIN Sales.Customer AS C ON SH.CustomerID = C.CustomerID WHERE C.StoreID = @storeid GROUP BY P.ProductID, P.Name RETURN END; GO
Vamos testar a função:
Select * from Sales.ufn_SalesByStore_V2(934)
Conclusão
As functions são um recurso maravilhoso! Mas use com cuidado porque elas podem impactar o desempenho das consultas (vamos conversar sobre isso nos próximos posts), e crie somente functions que não existem no SQL Server.
Faça testes! Use a nossa VM, crie consultas, busque funções, crie as suas… Não deixe para testar o que você está aprendnedo no seu trabalho.
Esta semana marcamos a nossa entrada na parte mais avançada do SQL Server 2017. Nas próximas semanas criaremos Stored Procedures e Triggers. E você vai ver que além de programar na sua linguagem favorita, você programa também usando o T-SQL.
Parabéns! A cada semana você está mais perto de ser um desenvolvedor diferenciado!
Downloads
Referências
https://docs.microsoft.com/pt-br/sql/t-sql/statements/create-function-transact-sql