Criar funções escalares
As funções escalares são ferramentas essenciais no SQL Server que permitem encapsular a lógica reutilizável e retornar um único valor. Você pode usá-las diretamente em SELECT instruções, WHERE cláusulas e outras expressões T-SQL , tornando suas consultas mais mantenedíveis e seu código mais modular.
Entender os conceitos básicos da função escalar
Uma função escalar aceita zero ou mais parâmetros e retorna um único valor de um tipo de dados especificado. Ao contrário dos procedimentos armazenados, as funções escalares podem ser inseridas diretamente em expressões SQL onde quer que você use uma coluna ou variável.
As principais características das funções escalares incluem a capacidade de aceitar parâmetros de entrada, executar cálculos ou transformações e retornar exatamente um valor. Você define o tipo de dados de retorno explicitamente na definição de função, que o SQL Server valida no momento da criação.
Ao criar uma função escalar, você está criando uma parte reutilizável da lógica que outros desenvolvedores podem chamar em todo o banco de dados. Isso promove a reutilização de código e ajuda a manter a consistência em seus aplicativos.
Definir sintaxe de função escalar
Para criar uma função escalar, use a CREATE FUNCTION instrução com componentes de sintaxe específicos. A estrutura básica inclui o nome da função, os parâmetros, o tipo de retorno e o corpo da função.
Aqui está o padrão de sintaxe fundamental:
CREATE FUNCTION schema_name.function_name
(
@parameter1 datatype,
@parameter2 datatype
)
RETURNS return_datatype
AS
BEGIN
-- Function logic here
RETURN @result
END
A RETURNS cláusula especifica o tipo de dados do valor único que a função retorna. Dentro do bloco BEGIN...END, você escreve sua lógica T-SQL e usa RETURN para enviar de volta o resultado.
Por exemplo, você pode criar uma função simples que calcula o imposto sobre vendas:
CREATE FUNCTION dbo.CalculateSalesTax
(
@Amount DECIMAL(10,2),
@TaxRate DECIMAL(5,4)
)
RETURNS DECIMAL(10,2)
AS
BEGIN
DECLARE @TaxAmount DECIMAL(10,2)
SET @TaxAmount = @Amount * @TaxRate
RETURN @TaxAmount
END
Essa função aceita dois parâmetros e retorna o valor do imposto calculado. Você pode usar essa função em qualquer SELECT instrução.
Implementar funções escalares com lógica de negócios
As funções escalares se destacam em encapsular regras de negócios e cálculos que você precisa aplicar consistentemente em seu banco de dados. Com funções escalares, você centraliza a lógica que, de outra forma, pode ser duplicada em várias consultas ou código do aplicativo.
Considere um cenário em que você precisa calcular a posse do funcionário em anos. Você cria uma função escalar que aceita uma data de contratação e retorna o número de anos completos:
CREATE FUNCTION dbo.GetEmployeeTenure
(
@HireDate DATE
)
RETURNS INT
AS
BEGIN
DECLARE @Tenure INT
SET @Tenure = DATEDIFF(YEAR, @HireDate, GETDATE())
RETURN @Tenure
END
Você pode usar essa função em consultas para exibir informações de posse:
SELECT
EmployeeName,
HireDate,
dbo.GetEmployeeTenure(HireDate) AS YearsOfService
FROM Employees
WHERE dbo.GetEmployeeTenure(HireDate) >= 5
Essa abordagem garante um cálculo de posse consistente em todo o banco de dados. Se as regras de negócios forem alteradas, você modificará a função uma vez em vez de atualizar várias consultas.
Observação
Essa função usa GETDATE(), o que a torna não determinística. Funções não determinísticas não podem ser usadas em exibições indexadas ou índices em colunas computadas. Para cenários que exigem determinismo, passe a data atual como um parâmetro.
Aplicar práticas recomendadas para funções escalares
Ao criar funções escalares, várias práticas recomendadas ajudam a garantir o desempenho e a manutenção ideais. Entender essas práticas ajuda você a evitar armadilhas comuns e criar funções eficientes e confiáveis.
Primeiro, mantenha suas funções escalares determinísticas sempre que possível. Uma função determinística sempre retorna o mesmo resultado, considerando os mesmos parâmetros de entrada. As funções que fazem referência a funções ou tabelas de data/hora do sistema não são determinísticas e podem impedir determinadas otimizações de consulta.
Além disso, evite efeitos colaterais em suas funções. As funções escalares não devem modificar o estado do banco de dados ou ter dependências em recursos externos. Essa restrição existe porque o SQL Server pode executar funções várias vezes ou em ordens diferentes do esperado.
Por fim, esteja atento às implicações de desempenho. Quando você usa uma função escalar em uma WHERE cláusula ou SELECT lista com tabelas grandes, o SQL Server pode executar a função para cada linha. Isso pode afetar significativamente o desempenho da consulta. Para esses cenários, considere as funções valoradas em tabela inline como uma alternativa.
Aqui está um exemplo de uma função escalar bem projetada que segue estas práticas:
CREATE FUNCTION dbo.FormatPhoneNumber
(
@PhoneNumber VARCHAR(10)
)
RETURNS VARCHAR(14)
AS
BEGIN
DECLARE @FormattedNumber VARCHAR(14)
IF LEN(@PhoneNumber) = 10
SET @FormattedNumber = '(' + SUBSTRING(@PhoneNumber, 1, 3) + ') ' +
SUBSTRING(@PhoneNumber, 4, 3) + '-' +
SUBSTRING(@PhoneNumber, 7, 4)
ELSE
SET @FormattedNumber = @PhoneNumber
RETURN @FormattedNumber
END
Essa função é determinística, não tem efeitos colaterais e executa uma transformação simples. Ele manipula a entrada inválida normalmente retornando o valor original quando o número de telefone não corresponde ao formato esperado.
Modificar e gerenciar funções escalares
Depois de criar uma função escalar, você pode modificar sua definição usando a ALTER FUNCTION instrução. A ALTER FUNCTION sintaxe espelha CREATE FUNCTION , mas permite que você altere a função sem removê-la e recriá-la, o que preserva permissões e dependências.
ALTER FUNCTION dbo.CalculateSalesTax
(
@Amount DECIMAL(10,2),
@TaxRate DECIMAL(5,4)
)
RETURNS DECIMAL(10,2)
AS
BEGIN
DECLARE @TaxAmount DECIMAL(10,2)
-- Updated logic with rounding
SET @TaxAmount = ROUND(@Amount * @TaxRate, 2)
RETURN @TaxAmount
END
Para remover uma função escalar, use a instrução DROP FUNCTION:
DROP FUNCTION IF EXISTS dbo.CalculateSalesTax
A IF EXISTS cláusula impede erros se a função não existir, o que é útil em scripts de implantação. Antes de remover uma função, verifique se nenhum outro objeto de banco de dados depende dela verificando exibições do sistema como sys.sql_expression_dependencies.