Compartilhar via

  • Artigo

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

As funções definidas por usuário são criadas usando a instrução CREATE FUNCTION, modificadas usando a instrução ALTER FUNCTION e removidas usando a instrução DROP FUNCTION. Cada nome de função definido pelo usuário completamente qualificado (schema_name.function_name) deve ser exclusivo.

Diretrizes

Transact-SQL erros que fazem com que uma instrução seja cancelada e que continue com a instrução seguinte no módulo (como os gatilhos ou os procedimentos armazenados) são tratados de modo diferente na função. Nas funções, esses erros fazem com que a execução da função seja interrompida. Em troca, isso faz com que a instrução que chamou a função seja cancelada.

As instruções em um bloco BEGIN... END não podem ter nenhum efeito colateral. Os efeitos colaterais da função são as alterações permanentes realizada no estado de um recurso que tem um escopo fora da função como uma modificação em uma tabela do banco de dados. As únicas alterações que podem ser feitas pelas instruções na função são alterações em objetos locais à função, como cursores ou variáveis locais. As modificações em tabelas de banco de dados, operações em cursores que não são locais à função, envio de e-mail, tentativa de modificação em catálogo e geração de um conjunto de resultados retornados ao usuário são exemplos de ações que não devem ser realizadas em uma função.

ObservaçãoObservação

Se a instrução CREATE FUNCTION produzir efeitos colaterais contra os recursos que não existem quanto a instrução CREATE FUNCTION é emitida, o SQL Server executa a instrução. Porém, o SQL Server não executa a função quando é chamada.

O número de vezes em que uma função especificada em uma consulta é realmente executada pode variar entre os planos de execução desenvolvidos pelo otimizador. Um exemplo é a função chamada por uma subconsulta em uma cláusula WHERE. O número de vezes em que a subconsulta e sua função são executadas pode variar com os caminhos de acesso diferentes escolhidos pelo otimizador.

Instruções válidas em uma função

Os tipos de instruções que são válidos em uma função incluem:

  • As instruções DECLARE podem ser usadas para definir variáveis de dados e cursores que são locais à função.

  • A atribuição de valores a objetos locais à função, como o uso de SET para atribuir valores para escalar e para as variáveis locais à tabela.

  • As operações de cursor que referenciam cursores locais são declaradas, abertas, fechadas e desalocadas na função. As instruções FETCH que retornam os dados aos clientes não são permitidas. Somente instruções FETCH que atribuem valores a variáveis locais usando a cláusula INTO são permitidas.

  • Instruções de controle-de-fluxo exceto as instruções TRY ... CATCH.

  • Instruções SELECT com listas de seleção com expressões que atribuem valores às variáveis que são locais à função.

  • As instruções UPDATE, INSERT e DELETE que modificam variáveis de tabela que são locais à função.

  • Instruções EXECUTE que chamam um procedimento armazenado estendido.

Funções incorporadas no sistema

As funções não-determinísticas internas a seguir podem ser usadas nas funções definidas por usuário Transact-SQL.

CURRENT_TIMESTAMP

@@MAX_CONNECTIONS

GET_TRANSMISSION_STATUS

@@PACK_RECEIVED

GETDATE

@@PACK_SENT

GETUTCDATE

@@PACKET_ERRORS

@@CONNECTIONS

@@TIMETICKS

@@CPU_BUSY

@@TOTAL_ERRORS

@ @ DBTS

@@TOTAL_READ

@@IDLE

@@TOTAL_WRITE

@@IO_BUSY

 

As funções não-determinísticas incorporadas a seguir não podem ser usadas nas funções definidas por usuário Transact-SQL.

NEWID

RAND

NEWSEQUENTIALID

TEXTPTR

Para obter uma lista das funções determinísticas e não-determinísticas incorporadas no sistema, consulte Funções determinísticas e não determinísticas.

Funções associadas a esquema

CREATE FUNCTION dá suporte à cláusula SCHEMABINDING que associa a função ao esquema de qualquer objeto que ela referencia, como tabelas, exibições e demais funções definidas pelo usuário. Uma tentativa para alterar ou descartar qualquer objeto referenciado por uma função associada a esquema falhará.

Essas condições devem ser cumpridas antes de especificar SCHEMABINDING em CREATE FUNCTION:

  • Todas as exibições e as funções definidas pelo usuário referenciadas pela função devem ser associadas a esquema.

  • Todos os objetos referenciados pela função devem estar no mesmo banco de dados da função. Os objetos devem ser referenciados usando nomes de uma única parte ou nomes de duas partes.

  • Você deve ter permissão REFERENCES em todos os objetos (tabelas, exibições e funções definidas pelo usuário) referenciados na função.

Você pode usar ALTER FUNCTION para remover a associação a esquema. A instrução ALTER FUNCTION deve redefinir a função sem especificar WITH SCHEMABINDING.

Especificando parâmetros

Uma função definida pelo usuário usa parâmetros de entrada zero ou mais e retorna um valor escalar ou uma tabela. A função pode ter um máximo de 1024 parâmetros de entrada. Quando um parâmetro da função tiver um valor padrão, a palavra-chave DEFAULT deve ser especificada quando a função for chamada para obter o valor padrão. Esse comportamento é diferente dos parâmetros com valores padrão nos procedimentos armazenados definidos pelo usuário nos quais a omissão de parâmetro também implica o valor padrão. Funções definidas pelo usuário não dão suporte aos parâmetros de saída.