Compartilhar via

  • Artigo

Funções embutidas definidas pelo usuário

Funções embutidas definidas pelo usuário são um subconjunto de funções definidas pelo usuário que retornam um tipo de dados table. É possível usar funções embutidas para obter funções de exibições com parâmetros.

O seguinte exemplo retorna nomes de loja e cidades para uma região especificada:

USE AdventureWorks;
GO
CREATE VIEW CustomersByRegion
AS
SELECT DISTINCT S.Name AS Store, A.City
FROM Sales.Store AS S
    JOIN Sales.CustomerAddress AS CA ON CA.CustomerID = S.CustomerID
    JOIN Person.Address AS A ON A.AddressID = CA.AddressID
    JOIN Person.StateProvince SP ON 
        SP.StateProvinceID = A.StateProvinceID
WHERE SP.Name = N'Washington';
GO

Essa exibição seria melhor se fosse mais generalizada e permitisse que os usuários especificassem a região em cuja exibição estão interessados. Mas as exibições não oferecem suporte a parâmetros nos critérios de pesquisa especificados na cláusula WHERE. As funções embutidas definidas pelo usuário podem ser usadas para oferecer suporte a parâmetros em critérios de pesquisa especificados na cláusula WHERE. O exemplo a seguir cria uma função embutida que permite que os usuários especifiquem a região na consulta:

USE AdventureWorks;
GO
IF OBJECT_ID(N'Sales.ufn_CustomerNamesInRegion', N'IF') IS NOT NULL
    DROP FUNCTION Sales.ufn_CustomerNamesInRegion;
GO
CREATE FUNCTION Sales.ufn_CustomerNamesInRegion
                 ( @Region nvarchar(50) )
RETURNS table
AS
RETURN (
        SELECT DISTINCT S.Name AS Store, A.City
        FROM Sales.Store AS S
        JOIN Sales.CustomerAddress AS CA ON CA.CustomerID = S.CustomerID
        JOIN Person.Address AS A ON A.AddressID = CA.AddressID
        JOIN Person.StateProvince SP ON 
        SP.StateProvinceID = A.StateProvinceID
        WHERE SP.Name = @Region
       );
GO
-- Example of calling the function for a specific region
SELECT *
FROM Sales.ufn_CustomerNamesInRegion(N'Washington');
GO

Regras para funções embutidas definidas pelo usuário

As funções embutidas definidas pelo usuário seguem estas regras:

  • A cláusula RETURNS contém só a palavra-chave table. Você não precisa definir o formato de uma variável de retorno, porque ela é configurada pelo formato do conjunto de resultados da instrução SELECT na cláusula RETURN.

  • Não há function_body delimitado por BEGIN e END.

  • A cláusula RETURN contém uma única instrução SELECT entre parênteses. O conjunto de resultados da instrução SELECT forma a tabela retornada pela função. A instrução SELECT utilizada em uma função embutida está sujeita às mesmas restrições que as instruções SELECT usadas em exibições.

  • A função avaliada por tabela só aceita constantes ou argumentos @local_variable

Funções embutidas e exibições indexadas

Também é possível usar funções embutidas para aumentar o poder das exibições indexadas. A exibição indexada em si não pode usar parâmetros de sua cláusula WHERE ao pesquisar condições a fim de personalizar o conjunto de resultados armazenados para usuários específicos. Entretanto, é possível definir uma exibição indexada que armazene o conjunto de dados completo que combina com a exibição e depois definir uma função embutida sobre a exibição indexada que contenha critérios de pesquisa com parâmetros para permitir que os usuários personalizem seus resultados. Se a definição da exibição for completa, a maior parte do trabalho efetuado para criar um conjunto de resultados envolve operações como criação de agregados ou união de várias tabelas ao criar o índice clusterizado na exibição. Se você criar uma função embutida que faça referência à exibição indexada, a função pode aplicar os filtros com parâmetros do usuário para retornar linhas específicas do conjunto de resultados materializados da exibição indexada. Por exemplo:

  1. Defina a exibição vw_QuarterlySales que agrega todos os dados de vendas em um conjunto de resultados que relata os dados resumidos das vendas trimestrais de todas as lojas.

  2. Crie um índice clusterizado em vw_QuarterlySales para materializar um conjunto de resultados que contém os dados resumidos.

  3. Crie uma função embutida para filtrar os dados resumidos:

    CREATE FUNCTION dbo.ufn_QuarterlySalesByStore
     ( @StoreID int )
RETURNS table
AS
RETURN (
        SELECT *
        FROM SalesDB.dbo.vw_QuarterlySales
        WHERE StoreID = @StoreID
       )

  4. Em seguida os usuários podem obter os dados específicos de suas lojas pela seleção da função embutida:

    SELECT *
FROM fn_QuarterlySalesByStore(14432)

A maioria do trabalho necessário para atender as consultas emitidas na etapa 4 é para agregar os dados de vendas trimestrais. Este trabalho é feito de uma vez na etapa 2. Cada instrução SELECT individual da etapa 4 usa a função fn_QuarterlySalesByStore para filtrar os dados agregados específicos de sua loja.