Função com valor de tabela complexa

As funções com valor de tabela complexas (ou Multi-Statement Table-Valued Functions) no SQL Server são uma forma poderosa de encapsular lógicas de consulta que retornam um conjunto de resultados em formato de tabela. Diferente das funções com valor de tabela inline, que consistem em uma única instrução SELECT, as funções complexas permitem múltiplas instruções, o que possibilita realizar operações mais elaboradas e manipulações de dados antes de retornar os resultados. Neste post, vamos explorar a função com valor de tabela complexa no SQL Server.

Sintaxe básica de uma função com valor de tabela complexa

A sintaxe para criar uma função com valor de tabela complexa é a seguinte:

CREATE FUNCTION NomeDaFuncao(@Parametro Tipo)
RETURNS @TabelaRetorno TABLE (Coluna1 Tipo1, Coluna2 Tipo2, ...)
AS
BEGIN
    -- Declaração de variáveis (opcional)
    DECLARE @Variavel Tipo;

    -- Lógica da função
    INSERT INTO @TabelaRetorno (Coluna1, Coluna2)
    SELECT Valor1, Valor2
    FROM Tabela
    WHERE Condicao = @Parametro;

    -- Outras operações, se necessário

    RETURN;
END;

Explicação:

• CREATE FUNCTION: Comando para criar a função.
• @Parametro: Parâmetro(s) de entrada da função.
• RETURNS @TabelaRetorno TABLE: Define o tipo de retorno como uma tabela, especificando suas colunas e tipos.
• BEGIN...END: Bloco que contém a lógica da função.
• INSERT INTO: Insere dados na tabela de retorno.

Exemplo prático de função com valor de tabela complexa

Vamos criar uma função que retorna uma lista de funcionários com base em seu departamento, incluindo um cálculo adicional, como a contagem de funcionários por departamento.

Exemplo: Função para retornar funcionários e contagem por departamento

CREATE FUNCTION FuncionariosPorDepartamento(@DepartamentoID INT)
RETURNS @TabelaRetorno TABLE (
    FuncionarioID INT,
    Nome VARCHAR(100),
    ContagemFuncionarios INT
)
AS
BEGIN
    -- Inserir dados de funcionários no resultado
    INSERT INTO @TabelaRetorno (FuncionarioID, Nome, ContagemFuncionarios)
    SELECT F.FuncionarioID, F.Nome, (SELECT COUNT(*) FROM Funcionarios WHERE DepartamentoID = @DepartamentoID) AS ContagemFuncionarios
    FROM Funcionarios F
    WHERE F.DepartamentoID = @DepartamentoID;

    RETURN;
END;

Explicação:

• Função: FuncionariosPorDepartamento aceita um parâmetro @DepartamentoID e retorna uma tabela com três colunas: FuncionarioID, Nome e ContagemFuncionarios;
  
  
• Lógica interna: A função insere dados na tabela de retorno, calculando a contagem de funcionários em um subselect.

Usando a função:

Agora que a função está criada, você pode utilizá-la em suas consultas:

SELECT * 
FROM FuncionariosPorDepartamento(3);

Isso retornará todos os funcionários do departamento com ID 3, junto com a contagem de funcionários desse departamento.

Outro exemplo prático

CREATE FUNCTION multi_tabelas(@nome VARCHAR(30)) // função com parâmetro - recebe um nome
RETURNS @valores Table // retorna uma tabela - objeto do tipo Table
(nome VARCHAR(30), // estrutura da tabela
 soma_notas REAL,
 media REAL)
AS
BEGIN
DECLARE // variáveis usadas nos cálculos 
   @soma REAL,
   @media REAL

// cálculos da soma e média das notas
SELECT @soma = (nota1 * 2 + nota2 + nota3 + nota4)
FROM notas_alunos WHERE nome = @nome
SELECT @media = (nota1 * 2 + nota2 + nota3 + nota4)/5.00
FROM notas_alunos WHERE nome = @nome 	

// preenche a tabela estruturada nesta função
INSERT @valores(nome, soma_notas, media) // especifica os campos a seres preenchidos
// especifica os valores e variáveis que preencherão os campos
SELECT nt.nome, @soma, @media 
FROM notas_alunos AS nt 
WHERE nome = @nome // filtro - a condição é o nome passado por parâmetro
RETURN
END

Para chamar a função basta digitar:

SELECT *
   // especifica o nome do aluno do qual serão usadas as notas para os cálculos
   FROM multi_tabelas('nome') 

Vantagens das funções com valor de tabela complexas

As funções com valor de tabela complexas oferecem várias vantagens:

1. Flexibilidade: Permitem a execução de múltiplas instruções SQL, proporcionando maior flexibilidade na lógica de negócios;
   
   
2. Organização do código: Facilitam a organização do código SQL, permitindo encapsular lógicas complexas em uma única função;
   
   
3. Reuso: Uma vez definida, a função pode ser reutilizada em várias partes do seu aplicativo, promovendo a DRY (Don’t Repeat Yourself);
   
   
4. Integração com consultas: Podem ser facilmente integradas em consultas SQL, como junções e subconsultas, melhorando a legibilidade e a estrutura do código.

Desvantagens das funções com valor de tabela complexas

Apesar de suas vantagens, as funções com valor de tabela complexas também apresentam algumas desvantagens:

1. Desempenho: Podem ser menos eficientes do que as funções com valor de tabela inline, especialmente em consultas complexas, pois cada execução pode resultar em um plano de execução menos otimizado;
   
   
2. Limitações em funções de agregação: Não podem ser usadas diretamente em cláusulas de GROUP BY ou ORDER BY, pois são tratadas como tabelas temporárias;
   
   
3. Complexidade: Podem tornar-se complexas e difíceis de manter se a lógica interna não for bem organizada ou se o número de operações crescer.

Comparação com outros tipos de funções

Característica	Função com Valor de Tabela Complexa	Função com Valor de Tabela Inline	Função Escalar
Tipo de Retorno	Tabela (múltiplas linhas/colunas)	Tabela (múltiplas linhas/colunas)	Valor único (int, varchar, etc.)
Desempenho	Pode ser mais lento	Geralmente mais rápido	Pode ser lento em grandes conjuntos
Complexidade	Alta	Baixa	Baixa
Reuso de Código	Alta	Alta	Alta

Dúvidas ou sugestões? Deixem nos comentários! Para mais dicas, acesse o nosso canal no YouTube:
 https://youtube.com/criandobits

Tags: tabela complexa sql server