SQL Server Table-valued Functions

Summary: neste tutorial, aprenderá como usar a função valorizada em tabelas do SQL Server, incluindo a função valorizada em tabelas em linha e as funções valorizadas por múltiplos estados.

O que é uma função valorizada em tabelas do SQL Server

Uma função valorizada em tabelas é uma função definida pelo utilizador que retorna dados de um tipo de tabela. O tipo de retorno de uma função valorizada em tabela é uma tabela, portanto, pode usar a função valorizada em tabela tal como usaria uma tabela.

Criar uma função valorizada em tabela

O seguinte exemplo de declaração cria uma função valorizada em tabela que retorna uma lista de produtos incluindo o nome do produto, ano do modelo e o preço da lista para um ano específico do modelo:

CREATE FUNCTION udfProductInYear ( @model_year INT)RETURNS TABLEASRETURN SELECT product_name, model_year, list_price FROM production.products WHERE model_year = @model_year;
Code language: SQL (Structured Query Language) (sql)

A sintaxe é semelhante à que cria uma função definida pelo utilizador.

O RETURNS TABLE especifica que a função irá retornar uma tabela. Como pode ver, não há nenhuma declaração BEGIN...END. A declaração simplesmente consulta dados do production.products tabela.

O udfProductInYear função aceita um parâmetro chamado @model_year do tipo INT. Devolve os produtos cujos anos de modelo são iguais @model_year parâmetro.

Após a função com valor de tabela ser criada, pode encontrá-la em Programabilidade > Funções > Funções com valor de tabela como mostra a figura seguinte:

A função acima retorna o conjunto de resultados de uma única SELECT declaração, portanto, é também conhecida como uma função valorizada em tabela em linha.

Executar uma função valorizada em tabela

Para executar uma função valorizada em tabela, utiliza-se na instrução FROM cláusula da instrução SELECT:

SELECT * FROM udfProductInYear(2017);
Code language: SQL (Structured Query Language) (sql)

Neste exemplo, seleccionámos os produtos cujo ano modelo é 2017.

P>Pode também especificar que colunas devem ser devolvidas da função tabela-valorizada da seguinte forma:

SELECT product_name, list_priceFROM udfProductInYear(2018);
Code language: SQL (Structured Query Language) (sql)

Aqui é a saída parcial:

Modificar uma função com valor de tabela

Modificar uma função com valor de tabela, usa a palavra-chave ALTER em vez de CREATE. O resto do script é o mesmo.

Por exemplo, a seguinte declaração modifica o udfProductInYear alterando o parâmetro existente e adicionando mais um parâmetro:

ALTER FUNCTION udfProductInYear ( @start_year INT, @end_year INT)RETURNS TABLEASRETURN SELECT product_name, model_year, list_price FROM production.products WHERE model_year BETWEEN @start_year AND @end_year
Code language: SQL (Structured Query Language) (sql)

A função udfProductInYear devolve agora produtos cujo ano modelo entre um ano inicial e um ano final.

A seguinte declaração chama a função udfProductInYear para obter os produtos cujos anos modelo se encontram entre 2017 e :

SELECT product_name, model_year, list_priceFROM udfProductInYear(2017,2018)ORDER BY product_name;
Code language: SQL (Structured Query Language) (sql)

Aqui é a saída parcial:

Multi-statement table-funções valorizadas (MSTVF)

Uma função valorizada em tabelas ou MSTVF é uma função valorizada em tabelas que retorna o resultado de múltiplas declarações.

A função avaliada em tabelas multi-estações é muito útil porque se pode executar múltiplas consultas dentro da função e agregar resultados na tabela retornada.

Para definir uma função avaliada em tabelas multi-estações, usa-se uma variável de tabela como valor de retorno. Dentro da função, executa-se uma ou mais consultas e inserem-se dados nesta variável da tabela.

O seguinte udfContacts() função combina funcionários e clientes numa única lista de contactos:

CREATE FUNCTION udfContacts() RETURNS @contacts TABLE ( first_name VARCHAR(50), last_name VARCHAR(50), email VARCHAR(255), phone VARCHAR(25), contact_type VARCHAR(20) )ASBEGIN INSERT INTO @contacts SELECT first_name, last_name, email, phone, 'Staff' FROM sales.staffs; INSERT INTO @contacts SELECT first_name, last_name, email, phone, 'Customer' FROM sales.customers; RETURN;END;
Code language: SQL (Structured Query Language) (sql)

A seguinte declaração ilustra como executar uma função avaliada em tabelas de declarações múltiplas udfContacts:

SELECT * FROM udfContacts();
Code language: SQL (Structured Query Language) (sql)

Output:

Quando usar funções avaliadas em tabela

Usamos tipicamente funções avaliadas em tabela como vistas parametrizadas. Em comparação com procedimentos armazenados, as funções valorizadas em tabela são mais flexíveis porque podemos usá-las onde quer que sejam usadas tabelas.

Neste tutorial, aprendeu sobre a função valorizada em tabela do SQL Server incluindo funções valorizadas em tabela em linha e funções valorizadas em tabela multi-estações.