CRIAR FUNÇÃO

Aplica-se a: do Azure Synapse AnalyticsAnalytics Platform System (PDW)

Cria uma função definida pelo usuário (UDF) no Azure Synapse Analytics ou no Analytics Platform System (PDW). 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. As funções com valor de tabela (TVFs) definidas pelo usuário retornam um tipo de dados de tabela.

Sugestão

Para sintaxe no Fabric Data Warehouse, veja a versão do CREATE FUNCTION para o Fabric Data Warehouse.

• No Analytics Platform System (PDW), o valor de retorno deve ser um valor escalar (único).

• No Azure Synapse Analytics, CREATE FUNCTION pode retornar uma tabela usando a sintaxe para funções com valor de tabela embutido (visualização) ou pode retornar um único valor usando a sintaxe para funções escalares.

• Em pools SQL sem servidor no Azure Synapse Analytics, pode criar funções de valor de tabela embutidas, CREATE FUNCTION mas não funções escalares.

  Use esta instrução para criar uma rotina reutilizável que pode ser usada das seguintes maneiras:

• Em Transact-SQL declarações como SELECT

• Em aplicativos que chamam a função

• Na definição de outra função definida pelo utilizador

• 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 diretiva de segurança

Transact-SQL convenções de sintaxe

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

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 embutido

-- 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
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ção devem estar em conformidade com as regras para identificadores e devem ser exclusivos dentro do banco de dados e para seu esquema.

Observação

Parênteses são necessários após o nome da função, mesmo que um parâmetro não seja especificado.

@ parameter_name

Um parâmetro na função definida pelo usuário. Um ou mais parâmetros podem ser declarados.

Uma função pode ter um máximo de 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 um padrão para o parâmetro seja definido.

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

Observação

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

parameter_data_type

O tipo de dados do parâmetro. Para funções Transact-SQL, todos os tipos de dados escalares suportados no Azure Synapse Analytics são permitidos. O tipo de dados timestamp (rowversion) não é um tipo suportado.

[ = padrão ]

Um valor padrão para o parâmetro. Se um valor padrão for definido, a função pode ser executada sem especificar um valor para esse parâmetro.

Quando um parâmetro da função tem um valor padrão, a palavra-chave DEFAULT deve ser especificada quando a função é chamada para recuperar o valor padrão. Esse comportamento é diferente do uso de parâmetros com valores padrão em procedimentos armazenados em que omitir o parâmetro também implica 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 suportados no Azure Synapse Analytics são permitidos. O tipo de dados decarimbo de data/hora/ não é um tipo suportado. Os tipos não escalares de cursor e tabela não são permitidos.

function_body

Série de declarações Transact-SQL. O function_body não pode conter uma SELECT instrução 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 Transact-SQL instruções que, juntas, avaliam até 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 embutido, não há corpo de função; A tabela é o conjunto de resultados de uma única SELECT instrução.

TABELA

Especifica que o valor de retorno da função com valor de tabela (TVF) é uma tabela. Apenas 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á vinculada aos objetos de banco de dados aos quais faz referência. Quando SCHEMABINDING é especificado, os objetos base não podem ser modificados de uma forma que afetaria a definição da função. A própria definição de função deve primeiro ser modificada ou descartada para remover dependências no objeto a ser modificado.

A associação da função aos objetos aos quais ela faz referência é removida somente quando ocorre uma das seguintes ações:

• A função é descartada.

• A função é modificada usando a instrução ALTER com a opção SCHEMABINDING não especificada.

Uma função pode ser vinculada ao esquema somente se as seguintes condições forem verdadeiras:

• Todas as funções definidas pelo usuário referenciadas pela função também são vinculadas ao esquema.

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

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

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

Para remover SCHEMABINDING, use ALTER.

DEVOLVE NULL NA ENTRADA NULA | CHAMADA NA ENTRADA NULA

Especifica o OnNULLCall atributo de uma função com 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.

Melhores práticas

Se uma função definida pelo usuário não for criada com a cláusula SCHEMABINDING, as alterações feitas nos objetos subjacentes podem afetar a definição da função e produzir resultados inesperados quando ela for invocada. É recomendável especificar a WITH SCHEMABINDING cláusula quando você estiver criando a função. Isso garante que os objetos referenciados na definição de função não possam ser modificados, a menos que a função também seja modificada.

Interoperabilidade

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

• Declarações de atribuição.

• Instruções Control-of-Flow, exceto TRY... Declarações CATCH.

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

Em uma função com valor de tabela embutido (visualização), apenas uma única instrução select é permitida.

Limitações

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

As funções definidas pelo usuário podem ser aninhadas; ou seja, uma função definida pelo usuário pode chamar outra. O nível de aninhamento é incrementado quando a função chamada inicia a execução e diminuído quando a função chamada termina a execução. Exceder os níveis máximos de aninhamento faz com que toda a cadeia de funções chamadoras falhe.

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

Metadados

Esta seção lista as exibições do catálogo do sistema que você pode usar para retornar metadados sobre funções definidas pelo usuário.

• sys.sql_modules: Exibe a definição de Transact-SQL funções definidas pelo usuário. 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

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

Esta 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 SQL sem servidor.

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

O exemplo a seguir cria uma função com valor de tabela embutido para retornar algumas informações importantes sobre módulos, filtrando pelo objectType parâmetro. 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 embutidas estão disponíveis nos pools SQL sem servidor, mas em visualização nos pools SQL dedicados.

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

Este exemplo simples usa o TVF embutido criado anteriormente para demonstrar como seus resultados podem ser combinados com outras tabelas usando aplicação cruzada. Aqui, selecionamos todas as colunas de ambas sys.objects e os resultados de ModulesByType todas as linhas correspondentes na type coluna. Para obter mais informações sobre como usar apply, consulte FROM clause plus JOIN, APPLY, PIVOT (Transact-SQL).

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

Observação

As funções de valor de tabela embutidas estão disponíveis nos pools SQL sem servidor, mas em visualização nos pools SQL dedicados.

Próximo passo

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