Criar funções com valor de tabela
As funções com valor de tabela permitem encapsular lógica de consulta complexa em componentes reutilizáveis que retornam conjuntos de resultados. Você pode chamar essas funções diretamente em consultas, assim como tabelas ou exibições, tornando seu código mais modular e mantenedível.
Ao criar aplicativos de banco de dados, você geralmente precisa recuperar conjuntos de dados filtrados ou calculados com base em parâmetros de entrada. As funções com valor de tabela resolvem esse problema empacotando a lógica de consulta em funções que aceitam parâmetros e retornam tabelas. Ao contrário dos procedimentos armazenados, você pode usar funções com valor de tabela em JOIN cláusulas e SELECT instruções, proporcionando flexibilidade para tratar os resultados da função como fontes de dados.
Entenda os tipos de funções com valores de tabela
O SQL Server fornece dois tipos de funções com valor de tabela, cada uma adequada para cenários diferentes.
Função em linha com valor de tabela
Contém uma única SELECT instrução e retorna os resultados diretamente. Com funções embutidas, você não define a estrutura da tabela — o SQL Server a infere de sua SELECT instrução. O otimizador de consultas trata funções com valor de tabela em linha como visualizações com parâmetros, frequentemente produzindo melhores planos de execução.
Função com valor de tabela de várias instruções
Usa um BEGIN...END bloco e declara explicitamente a estrutura da tabela retornada. Esse tipo fornece mais controle quando você precisa executar várias instruções, executar cálculos complexos ou criar o conjunto de resultados iterativamente. No entanto, essa flexibilidade vem com uma compensação de desempenho, pois o otimizador trata essas funções de forma diferente.
A escolha entre essas funções depende de seus requisitos específicos. Para consultas simples com parâmetros, as funções embutidas fornecem melhor desempenho. Quando você precisa de lógica procedural ou de várias etapas para construir seu conjunto de resultados, as funções com múltiplas instruções tornam-se necessárias.
Criar funções de linha com valor de tabela
As funções com valor de tabela embutida oferecem uma maneira concisa de parametrizar consultas. Você os define com uma única instrução RETURN seguida por uma consulta SELECT.
O exemplo a seguir mostra uma função embutida que recupera pedidos para um cliente específico:
CREATE FUNCTION dbo.GetCustomerOrders
(
@CustomerID INT
)
RETURNS TABLE
AS
RETURN
(
SELECT
OrderID,
OrderDate,
TotalAmount,
Status
FROM Sales.Orders
WHERE CustomerID = @CustomerID
);
Agora você pode usar essa função em consultas como uma tabela:
SELECT OrderID, OrderDate, TotalAmount
FROM dbo.GetCustomerOrders(1001)
WHERE OrderDate >= '2024-01-01';
A função aceita o parâmetro de ID do cliente e retorna apenas os pedidos do cliente. Você pode filtrar JOINainda mais ou agregar os resultados conforme necessário. Essa abordagem mantém a consulta principal limpa ao encapsular a lógica de filtragem do cliente.
Criar funções com valor de tabela de múltiplas instruções
Funções com valor de tabela e múltiplas instruções oferecem mais flexibilidade quando você precisa executar várias operações para construir seu conjunto de resultados.
Considere uma função que calcula resumos de vendas de produtos com várias agregações:
CREATE FUNCTION dbo.GetProductSalesSummary
(
@StartDate DATE,
@EndDate DATE
)
RETURNS @SalesSummary TABLE
(
ProductID INT,
ProductName NVARCHAR(100),
TotalQuantity INT,
TotalRevenue DECIMAL(18,2),
AveragePrice DECIMAL(18,2)
)
AS
BEGIN
INSERT INTO @SalesSummary
SELECT
p.ProductID,
p.ProductName,
SUM(od.Quantity) AS TotalQuantity,
SUM(od.Quantity * od.UnitPrice) AS TotalRevenue,
AVG(od.UnitPrice) AS AveragePrice
FROM Production.Products p
INNER JOIN Sales.OrderDetails od ON p.ProductID = od.ProductID
INNER JOIN Sales.Orders o ON od.OrderID = o.OrderID
WHERE o.OrderDate BETWEEN @StartDate AND @EndDate
GROUP BY p.ProductID, p.ProductName;
RETURN;
END;
Observe como você declara explicitamente a variável @SalesSummary de tabela com colunas e tipos de dados específicos. O corpo da função insere dados nessa variável de tabela e, em seguida, retorna-os. Essa estrutura permite que você adicione lógica de processamento adicional, tratamento de erros ou instruções condicionais, conforme necessário.
Usar funções com valor de tabela em consultas
As funções com valor de tabela se integram perfeitamente às suas consultas, permitindo padrões avançados de recuperação de dados.
Você pode unir os resultados da função com outras tabelas:
SELECT
c.CustomerName,
s.ProductName,
s.TotalRevenue
FROM Customers c
CROSS APPLY dbo.GetProductSalesSummary('2024-01-01', '2024-12-31') s
WHERE s.TotalRevenue > 10000
ORDER BY s.TotalRevenue DESC;
O CROSS APPLY operador chama a função para cada linha da tabela Clientes, embora, neste exemplo, os parâmetros de função sejam constantes. Quando você passa valores de coluna como parâmetros, CROSS APPLY torna-se particularmente útil:
SELECT
c.CustomerName,
o.OrderID,
o.TotalAmount
FROM Customers c
CROSS APPLY dbo.GetCustomerOrders(c.CustomerID) o
WHERE o.Status = 'Completed';
Essa consulta recupera todos os pedidos concluídos para cada cliente, demonstrando como as funções com valor de tabela habilitam o processamento linha por linha em suas consultas. A função atua como uma subconsulta correlacionada, mas com melhor legibilidade e reutilização.
Para funções inline com valor de tabela que não exigem avaliação linha por linha, você também pode usar a seguinte sintaxe INNER JOIN.
SELECT
c.CustomerName,
o.OrderDate,
o.TotalAmount
FROM Customers c
INNER JOIN dbo.GetCustomerOrders(c.CustomerID) o ON 1=1
WHERE YEAR(o.OrderDate) = 2024;
Com essas técnicas, você pode criar consultas complexas com base em componentes de função mais simples e testados, melhorando a manutenção do código e a eficiência de desenvolvimento.