CREATE FUNCTION (Azure Synapse Analytics e Microsoft Fabric)

  • Artigo

Aplica-se a:Azure Synapse AnalyticsPDW (Analytics Platform System)Ponto de extremidade de análise do SQL no Microsoft FabricWarehouse no Microsoft Fabric

Cria uma função definida pelo usuário (UDF) no Azure Synapse Analytics, no Analytics Platform System (PDW) ou no Microsoft Fabric. Uma função definida pelo usuário é uma rotina Transact-SQL que aceita parâmetros, executa uma ação, como um cálculo complexo, e retorna o resultado dessa ação como um valor.

  • Em PDW (Analytics Platform System), o valor retornado deve ser um valor escalar (único).

  • Em Azure Synapse Analytics, a função CREATE pode retornar uma tabela usando a sintaxe para funções com valor de tabela embutidas (versão prévia) ou pode retornar um único valor usando a sintaxe para funções escalares.

  • No Microsoft Fabric e em pools de SQL sem servidor no Azure Synapse Analytics, CREATE FUNCTION pode criar funções de valor de tabela embutidas, mas não funções escalares. As TVFs (funções com valor de tabela) definidas pelo usuário retornam um tipo de dados de tabela.

    Use essa instrução para criar uma rotina reutilizável que possa ser usada destas maneiras:

  • Em instruções Transact-SQL, como SELECT

  • Em aplicativos que chamam a função

  • Na definição de outra função definida pelo usuário

  • Para definir uma restrição CHECK em uma coluna

  • Para substituir um procedimento armazenado

  • Usar uma função embutida como um predicado de filtro para uma política de segurança

Convenções de sintaxe de Transact-SQL

Sintaxe

Sintaxe da função escalar

-- Transact-SQL Scalar Function Syntax  (in dedicated pools in Azure Synapse Analytics and Parallel Data Warehouse)
-- Not available in the serverless SQL pools in Azure Synapse Analytics or Microsoft Fabric

CREATE FUNCTION [ schema_name. ] function_name   
( [ { @parameter_name [ AS ] parameter_data_type   
    [ = default ] }   
    [ ,...n ]  
  ]  
)  
RETURNS return_data_type  
    [ WITH <function_option> [ ,...n ] ]  
    [ AS ]  
    BEGIN   
        function_body   
        RETURN scalar_expression  
    END  
[ ; ]  
  
<function_option>::=   
{  
    [ SCHEMABINDING ]  
  | [ RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ]  
}

Sintaxe da função com valor de tabela embutida

-- Transact-SQL Inline Table-Valued Function Syntax
-- Preview in dedicated SQL pools in Azure Synapse Analytics
-- Available in the serverless SQL pools in Azure Synapse Analytics and Microsoft Fabric
CREATE FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ] parameter_data_type
    [ = default ] }
    [ ,...n ]
  ]
)
RETURNS TABLE
    [ WITH SCHEMABINDING ]
    [ AS ]
    RETURN [ ( ] select_stmt [ ) ]
[ ; ]

Argumentos

schema_name

O nome do esquema ao qual a função definida pelo usuário pertence.

function_name

O nome da função definida pelo usuário. Os nomes de funções devem obedecer às regras de identificadores e devem ser exclusivos dentro do banco de dados e em seu esquema.

Observação

São necessários parênteses depois do nome de função mesmo que um parâmetro não seja especificado.

@parameter_name

É um parâmetro na função definida pelo usuário. Podem ser declarados um ou mais parâmetros.

Uma função pode ter no máximo 2.100 parâmetros. O valor de cada parâmetro declarado deve ser fornecido pelo usuário quando a função é executada, a menos que seja definido um padrão para o parâmetro.

Especifique um nome de parâmetro usando um sinal de arroba ( @ ) como o primeiro caractere. O nome do parâmetro precisa estar em conformidade com as regras para identificadores. Os parâmetros são locais para a função. Os mesmos nomes de parâmetro podem ser usados em outras funções. Os parâmetros só podem assumir o lugar de constantes. Eles não podem ser usados no lugar de nomes de tabela, nomes de coluna ou nomes de outros objetos de banco de dados.

Observação

ANSI_WARNINGS não é respeitado quando você passa parâmetros em um procedimento armazenado, função definida pelo usuário ou quando você declara e define variáveis em uma instrução em lote. Por exemplo, se a variável for definida como char(3) e, em seguida, configurada com um valor maior que três caracteres, os dados serão truncados até o tamanho definido e a instrução INSERT ou UPDATE terá êxito.

parameter_data_type

É o tipo de dados de parâmetro. Para funções Transact-SQL, todos os tipos de dados escalares compatíveis com o Azure Synapse Analytics são permitidos. O tipo de dados de carimbo de data/hora (rowversion) não é um tipo compatível.

[ =default ]

É um valor padrão para o parâmetro. Se um valor default for definido, a função poderá ser executada sem a necessidade de especificar um valor para esse parâmetro.

Quando um parâmetro da função tiver um valor padrão, a palavra-chave DEFAULT deverá ser especificada quando a função for chamada para recuperar o valor padrão. Esse comportamento é diferente do uso de parâmetros com valores padrão em procedimentos armazenados nos quais a omissão do parâmetro também indica o valor padrão.

return_data_type

É o valor de retorno de uma função escalar definida pelo usuário. Para funções Transact-SQL, todos os tipos de dados escalares compatíveis com o Azure Synapse Analytics são permitidos. O tipo de dados de carimbo de data/hora rowversion/não é um tipo suportado. Os tipos não escalares de cursor e tabela não são permitidos.

function_body

Série de instruções Transact-SQL. O function_body não pode conter uma instrução SELECT e não pode fazer referência a dados do banco de dados. O function_body não pode fazer referência a tabelas ou exibições. O corpo da função pode chamar outras funções determinísticas, mas não pode chamar funções não determinísticas.

Em funções escalares, function_body é uma série de instruções Transact-SQL que juntas são avaliadas para um valor escalar.

scalar_expression

Especifica o valor escalar que a função escalar retorna.

select_stmt

É a instrução única SELECT que define o valor de retorno de uma função com valor de tabela embutido. Para uma função com valor de tabela embutida, não há corpo de função; A tabela é o conjunto de resultados de uma única SELECT instrução.

TABLE

Especifica que o valor retornado da TVF (função com valor de tabela) é uma tabela. Somente constantes e @local_variables podem ser passadas para TVFs.

Em TVFs embutidos (visualização), o valor de retorno TABLE é definido por meio de uma única SELECT instrução. As funções embutidas não têm variáveis de retorno associadas.

<function_option>

Especifica que a função tem uma ou mais das seguintes opções.

SCHEMABINDING

Especifica que a função está associada aos objetos de banco de dados referenciados por ela. Quando SCHEMABINDING for especificado, os objetos base não poderão ser modificadas de um modo que possa afetar a definição da função. A própria definição da função deve ser primeiro modificada ou descartada para remover as dependências no objeto a ser modificado.

A associação da função aos objetos referenciados por ela será removida somente quando ocorrer uma das ações a seguir:

  • A função for descartada.

  • A função for modificada com o uso da instrução ALTER sem a especificação da opção SCHEMABINDING.

Uma função poderá ser associada a esquemas apenas se as condições a seguir forem verdadeiras:

  • As funções definidas pelo usuário referenciadas pela função também são associadas a esquema.

  • As funções e outras UDFs referenciadas pela função são referenciadas usando um nome de uma ou duas partes.

  • Somente funções internas e outras UDFs no mesmo banco de dados podem ser referenciadas no corpo de UDFs.

  • O usuário que executou a CREATE FUNCTION instrução tem permissão REFERENCES nos objetos de banco de dados aos quais a função faz referência.

Para remover SCHEMABINDING, use ALTER.

RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT

Especifica o atributo OnNULLCall de uma função de valor escalar. Se não for especificado, CALLED ON NULL INPUT é implícito por padrão e o corpo da função é executado mesmo se NULL for passado como um argumento.

Práticas recomendadas

Se uma função definida pelo usuário não for criada com a cláusula SCHEMABINDING, as alterações feitas nos objetos subjacentes poderão afetar a definição da função e produzir resultados inesperados quando ela for chamada. É recomendável que você implemente um dos seguintes métodos para garantir que a função não se torne desatualizada devido a alterações em seus objetos subjacentes:

  • Especifique a cláusula WITH SCHEMABINDING quando você estiver criando a função. Isso garante que os objetos referenciados na definição da função não possam ser modificados, a menos que a função também seja modificada.

Interoperabilidade

As seguintes instruções são válidas em uma função de valor escalar:

  • Instruções de atribuição.

  • Instruções de controle de fluxo com exceção das instruções TRY...CATCH.

  • Instruções DECLARE que definem variáveis de dados locais.

Em uma função com valor de tabela embutida (versão prévia), é permitida apenas uma instrução SELECT.

Limitações

Funções definidas pelo usuário não podem ser usadas para executar ações que modificam o estado do banco de dados.

Funções definidas pelo usuário podem ser aninhadas, isto é, uma função definida pelo usuário pode chamar outra. O nível de aninhamento é incrementado quando a execução da função é iniciada, e reduzido quando a execução da função chamada é concluída. Até 32 níveis de funções definidas pelo usuário podem ser aninhados. Se o máximo de níveis de aninhamento for excedido haverá falha em toda a cadeia de funções da chamada de aninhamento.

Objetos, incluindo funções, não podem ser criados no banco de dados master do pool de SQL sem servidor no Azure Synapse Analytics.

Metadados

Esta seção lista as exibições do catálogo do sistema que podem ser usadas para retornar metadados sobre funções definidas pelo usuário.

  • sys.sql_modules : exibe a definição de funções definidas pelo usuário do Transact-SQL. Por exemplo:

    SELECT definition, type   
FROM sys.sql_modules AS m  
JOIN sys.objects AS o   
    ON m.object_id = o.object_id   
    AND type = ('FN');

  • sys.parameters : Exibe informações sobre os parâmetros definidos em funções definidas pelo usuário.

  • sys.sql_expression_dependencies : Exibe os objetos subjacentes referenciados por uma função.

Permissões

Requer a permissão CREATE FUNCTION no banco de dados e a permissão ALTER no esquema no qual a função está sendo criada.

Exemplos: Azure Synapse Analytics e PDW (Analytics Platform System)

R. Usar uma função definida pelo usuário com valor escalar para alterar um tipo de dados

Essa função simples usa um tipo de dados int como uma entrada e retorna um tipo de dados decimal(10,2) como uma saída.

CREATE FUNCTION dbo.ConvertInput (@MyValueIn int)  
RETURNS decimal(10,2)  
AS  
BEGIN  
    DECLARE @MyValueOut int;  
    SET @MyValueOut= CAST( @MyValueIn AS decimal(10,2));  
    RETURN(@MyValueOut);  
END;  
GO  
  
SELECT dbo.ConvertInput(15) AS 'ConvertedValue';

Observação

As funções escalares não estão disponíveis nos pools de SQL sem servidor ou Microsoft Fabric.

Exemplos: Azure Synapse Analytics

R. Criar uma função com valor de tabela embutida

O exemplo a seguir cria uma função com valor de tabela embutida para retornar algumas informações importantes sobre módulos, filtrando pelo parâmetro objectType. Ele inclui um valor padrão para retornar todos os módulos quando a função é chamada com o DEFAULT parâmetro. Este exemplo usa algumas das exibições do catálogo do sistema mencionadas em Metadados.

CREATE FUNCTION dbo.ModulesByType(@objectType CHAR(2) = '%%')
RETURNS TABLE
AS
RETURN
(
    SELECT 
        sm.object_id AS 'Object Id',
        o.create_date AS 'Date Created',
        OBJECT_NAME(sm.object_id) AS 'Name',
        o.type AS 'Type',
        o.type_desc AS 'Type Description', 
        sm.definition AS 'Module Description'
    FROM sys.sql_modules AS sm  
    JOIN sys.objects AS o ON sm.object_id = o.object_id
    WHERE o.type like '%' + @objectType + '%'
);
GO

A função pode então ser chamada para retornar todos os objetos de exibição (V) com:

select * from dbo.ModulesByType('V');

Observação

As funções de valor de tabela em linha estão disponíveis nos pools de SQL sem servidor, mas em versão prévia nos pools de SQL dedicados.

B. Combinar resultados de uma função com valor de tabela embutido

Este exemplo simples usa o TVF embutido criado anteriormente para demonstrar como é possível combinar os resultados com outras tabelas usando Cross Apply. Aqui, selecionamos todas as colunas de ambas sys.objects e os resultados de para todas as linhas correspondentes na coluna de ModulesByType tipo. Confira mais detalhes sobre como usar Apply em Cláusula FROM e JOIN, APPLY, PIVOT.

SELECT * 
FROM sys.objects o
CROSS APPLY dbo.ModulesByType(o.type);
GO

Observação

As funções de valor de tabela em linha estão disponíveis nos pools de SQL sem servidor, mas em versão prévia nos pools de SQL dedicados.

Próxima etapa

Criar funções definidas pelo usuário (mecanismo de banco de dados)