Partilhar via

  • Artigo

Executando funções definidas pelo usuário (Mecanismo de Banco de Dados)

As funções definidas pelo usuário podem ser chamadas em consultas ou em outras instruções ou expressões como as expressões de colunas computadas ou de cadeia de caracteres. Funções de valores escalares podem ser executadas usando-se a instrução EXECUTE.

Chamando funções definidas pelo usuário que retornam um valor escalar

Você pode chamar uma função definida pelo usuário que retorne um valor escalar sempre que uma expressão escalar do mesmo tipo de dados for permitida em instruções do Transact-SQL. As funções com valor escalar devem ser invocadas usando-se pelo menos o nome de duas partes da função. Para obter mais informações sobre nomes com várias partes, consulte convenções de sintaxe Transact-SQL (Transact-SQL).

Consultas

Funções definidas pelo usuário que retornam valores escalares são permitidas nesses locais:

  • Como uma expression na select_list de uma instrução SELECT:

    USE AdventureWorks;
GO
SELECT ProductID, ListPrice, dbo.ufnGetProductDealerPrice(ProductID, StartDate) AS DealerPrice,
    StartDate, EndDate
FROM Production.ProductListPriceHistory
WHERE ListPrice > .0000
ORDER BY ProductID, StartDate;
GO

  • Como uma expression ou string_expression em um predicado de cláusula WHERE ou HAVING:

    USE AdventureWorks;
GO
SELECT ProductID, ListPrice, StartDate, EndDate
FROM Production.ProductListPriceHistory
WHERE dbo.ufnGetProductDealerPrice(ProductID, StartDate) > .0000
ORDER BY ProductID, StartDate;
GO

  • Como uma group_by_expression em uma cláusula GROUP BY.

  • Como uma order_by_expression em uma cláusula ORDER BY.

  • Como uma expression na cláusula SET em uma instrução UPDATE:

    USE AdventureWorks;
GO
UPDATE Production.ProductListPriceHistory
SET ListPrice = dbo.ufnGetProductDealerPrice(ProductID, StartDate)
WHERE ProductID > 900;
GO

  • Como uma expression na cláusula VALUES de uma instrução INSERT:

    As funções definidas pelo usuário referenciadas nessas localizações são executadas logicamente uma vez por linha.

Restrições CHECK

As funções definidas pelo usuário que retornam valores escalares podem ser chamadas em restrições CHECK se os valores de argumento passados pela função referenciarem colunas somente na tabela ou em constantes. A cada vez que o processador de consultas verificar a restrição, o processador de consulta chama a função com os valores de argumento associados com a linha que estiver sendo verificada no momento. O proprietário de uma tabela deve ser também o proprietário da função definida pelo usuário chamada por uma restrição CHECK na tabela.

Definições DEFAULT

As funções definidas pelo usuário podem ser chamadas como a constant_expression das definições DEFAULT se os valores de argumento passados para a função contiverem apenas constantes. O proprietário de uma tabela deve ser também o proprietário da função definida pelo usuário chamada por uma definição DEFAULT na tabela.

Colunas computadas

Funções podem ser chamadas por colunas computadas se os valores de argumento passados para a função referenciarem colunas somente na tabela ou em constantes. O proprietário de uma tabela deve ser também o proprietário da função definida pelo usuário chamada por uma coluna computada na tabela.

Operadores de atribuição

Os operadores de atribuição (left_operand = right_operand) podem chamar funções definidas pelo usuário que retornem um valor escalar na expressão especificada como o operando direito.

Instruções controle de fluxo

As funções definidas pelo usuário que retornam valores escalares podem ser chamadas por instruções de controle de fluxo em expressões booleanas.

Expressões CASE

As funções definidas pelo usuário que retornam um valor escalar podem ser chamadas em todas as expressões CASE.

Instruções PRINT

As funções definidas pelo usuário que retornam uma cadeia de caracteres podem ser chamadas como a expressão string_expr das instruções PRINT.

Funções e procedimentos armazenados

  • Argumentos de função também podem fazer referência a uma função definida pelo usuário que retorna um valor escalar.

  • Instruções RETURN integer_expression em procedimentos armazenados podem chamar funções definidas pelo usuário que retornam um inteiro como integer_expression.

  • Instruções RETURN return_type_spec em funções definidas pelo usuário podem chamar funções definidas pelo usuário que retornam um tipo de dados escalar como return_type_spec, desde que o valor retornado pela função definida pelo usuário chamada possa ser implicitamente convertido ao tipo de dados de retorno da função de chamada.

Executando funções definidas pelo usuário que retornam um valor escalar

Você pode executar funções definidas pelo usuário que retornam valores escalares da mesma maneira como faz no caso de procedimentos armazenados. Ao executar uma função definida pelo usuário que retorna um valor escalar, os parâmetros são especificados da mesma maneira como no caso de procedimentos armazenados.

  • Os valores de argumento não estão entre parênteses.

  • Nomes de parâmetro podem ser especificados.

  • Se os nomes de parâmetros forem especificados, os valores de argumento não precisam estar na mesma seqüência dos parâmetros.

O exemplo a seguir cria uma função definida pelo usuário que retorna um valor escalar decimal.

IF OBJECT_ID(N'dbo.ufn_CubicVolume', N'FN') IS NOT NULL
    DROP FUNCTION dbo.ufn_CubicVolume;
GO
CREATE FUNCTION dbo.ufn_CubicVolume
-- Input dimensions in centimeters.
   (@CubeLength decimal(4,1), @CubeWidth decimal(4,1),
    @CubeHeight decimal(4,1) )
RETURNS decimal(12,3) -- Cubic Centimeters.
WITH SCHEMABINDING
AS
BEGIN
   RETURN ( @CubeLength * @CubeWidth * @CubeHeight )
END;
GO

O exemplo a seguir executa a função dbo.ufn_CubicVolume. Usando a instrução EXECUTE do Transact-SQL, os argumentos são identificados em uma ordem diferente dos parâmetros na definição de função:

DECLARE @MyDecimalVar decimal(12,3);
EXEC @MyDecimalVar = dbo.ufn_CubicVolume @CubeLength = 12.3,
                        @CubeHeight = 4.5, @CubeWidth = 4.5;
SELECT @MyDecimalVar;
GO

O exemplo a seguir executa a função dbo.ufn_CubicVolume sem especificar os nomes de parâmetro:

DECLARE @MyDecimalVar decimal(12,3);
EXEC @MyDecimalVar = dbo.ufn_CubicVolume 12.3, 4.5, 4.5;
SELECT @MyDecimalVar;
GO

Você também pode usar a sintaxe ODBC CALL para executar a função dbo.ufn_CubicVolume de aplicativos OLE DB ou ODBC:

-- First use SQLBindParam to bind the return value parameter marker
-- to a program variable of the appropriate type
SQLExecDirect(hstmt,
              "{ CALL ? = dbo.ufn_CubicVolume(12.3, 4.5, 4.5) }",
              SQL_NTS);

Chamando funções definidas pelo usuário que retornam uma tabela de tipo de dados

Você pode chamar uma função definida pelo usuário que retorne uma table em que as expressões de tabela sejam permitidas na cláusula FROM das instruções SELECT, INSERT, UPDATE ou DELETE. Uma chamada de uma função definida pelo usuário que retorna uma tabela pode ser seguida por um alias de tabela opcional. O exemplo a seguir ilustra a chamada da função dbo.ufnGetContactInformation com valor de tabela na cláusula FROM de uma instrução SELECT.

USE AdventureWorks;
GO
SELECT ContactID, FirstName, LastName, JobTitle, ContactType
FROM dbo.ufnGetContactInformation(2200);
GO
SELECT ContactID, FirstName, LastName, JobTitle, ContactType
FROM dbo.ufnGetContactInformation(5);
GO

Quando uma função definida pelo usuário que retorna uma tabela é chamada na cláusula FROM de uma subconsulta, os argumentos da função não podem referenciar nenhuma coluna da consulta externa.

Cursores estáticos, somente leitura, são o único tipo de cursor que pode ser aberto em uma instrução SELECT, cuja cláusula FROM faça referência a uma função definida pelo usuário que retorna uma tabela.

Uma instrução SELECT que referencia uma função definida pelo usuário que retorna um table invoca a função uma vez.

Invocando funções com valor de tabela interna

Há várias funções com valor de tabela interna que retornam um valor de tabela. A chamada dessas funções internas definidas pelo usuário pode ser não qualificada ou pode usar o esquema qualificador sys. Você deve usar o esquema qualificador sys para funções com valor de tabela interna porque isso evita conflitos com funções definidas pelo usuário que tenham o mesmo nome. O exemplo a seguir mostra como chamar a função interna de sistema fn_helpcollations.

SELECT *
FROM sys.fn_helpcollations();
GO

Usando dicas com funções com valor de tabela

Ao criar uma função definida pelo usuário, você pode aplicar uma dica de tabela em qualquer consulta que forme a definição da função. As dicas aplicadas a exibições que referenciam funções com valor de tabela do Transact-SQL também são aplicadas às funções. Essas funções podem estar em conflito com as dicas na definição de função. Para obter mais informações, consulte Resolução de exibição.

Você não pode aplicar dicas em exibições que referenciam funções CLR com valor de tabela.

ObservaçãoObservação

A capacidade do Mecanismo de Banco de Dados para aplicar dicas em exibições de funções com valor de tabela de instruções múltiplas que fazem parte da definição da exibição será removida em uma futura versão do SQL Server.

Você não pode aplicar uma dica de tabela ao resultado de nenhuma função com valor de tabela na cláusula FROM de uma consulta.