Data

12 jan, 2015

SQL – Usando UDF – User Defined Function no SQL Server

Publicidade

Neste artigo eu vou escrever um pouco sobre User Defined Function- UDF no SQL Server, seus benefícios, tipos de UDF e seus parâmetros. As UDFs foram introduzidas a partir do SQL Server 2000 são um segmento de código preparado que pode aceitar parâmetros, processar uma lógica e retornar dados em um banco de dados SQL Server.

Da mesma forma que os procedimentos armazenados, as User Defined Functions desempenham um papel muito importante no SQL Server. Embora haja diferenças entre Stored Procedures e UDFs, as UDFs podem ser usadas para executar uma lógica complexa, podem aceitar parâmetros e retornar dados. O resultado da função pode ser usado em instruções Select, com cláusula Where e em junções (Join).

Existem dois tipos principais de UDFs:

  • UDFs que retornam valores escalares
  • UDFs que retornam table values

Para as UDFS que retornam table values podemos ter UDFs que retornam tabelas embutidas e UDFs que retornam tabelas com várias instruções, conforme tabela a seguir:

Tipo de Retorno Características
Scalar Similar a funções em outras linguagens. Retorna um valor único de um tipo de dado scalar.
Inline Table Retorna um rowset de um tipo de dados table do SQL Server.
MultiStatement Table Define explicitamente a estrutura da tabela a retornar. Define nomes de colunas e tipo de dados na cláusula RETURN.

Neste artigo vamos criar e executar UDFs em uma base de dados SQL Server usando o banco de dados Northwind.mdf. Você pode baixar este arquivo neste link.

Preparando o ambiente

Para os exemplos mostrados neste artigo, eu estou usando o SQL Server 2012 Express Edition e o SQL Server Management Studio Express Edition.

Todas as instruções SQL serão executadas no SQL Server Management Studio. (SSMS)

Abaixo vemos a janela do SSMS, onde selecionamos o banco de dados Northwind e visualizamos no item Programmability as Functions e as opções para criar Table-valued Functions, Scalar-valued Functions, Aggregate Functions e as System Functions:

sql_udf11

1. Criando Scalar Function

Vamos criar uma UDF Scalar com o nome GetTotalPedido no banco de dados Northwind que calcula o valor total para um pedido e recebe como parâmetro o numero do pedido.

Abaixo vemos a instrução para criar a UDF, sua execução e a UDF criada com o parâmetro OrderID:

sql_udf13

Para testar a UDF criada podemos usar instruções SELECT conforme mostra os exemplos a seguir:

Obtendo o valor total de um pedido em uma instrução SELECT

A instrução a seguir retorna o valor total para o pedido 10248:

sql_udf14

Vemos abaixo a instrução SELECT dbo.GetTotalPedido(10248) que retorna o valor 440 para este pedido:

sql_udf15

Obtendo o valor total do pedido em uma instrução SELECT com uma cláusula WHERE

Neste exemplo estamos selecionando os campos OrderID, CustomerID e ShipCountry e exibindo o valor do pedido no ‘Total Pedido’ para os números de pedido maior que 10300:

sql_udf16

2. Criando Table Valued Function

As UDFs Table Value foram introduzidas no SQL Server 2005 e retornam um conjunto de resultados na forma de uma variável tabela.

Elas podem ser uma boa alternativa às Views, pois as Views não permitem parâmetros e as UDF Table Valued permitem.

A tabela retornada por UDF pode ser usada na cláusula FROM e o corpo de uma UDF Table Valued é apenas uma consulta em linha que usa uma instrução SELECT e retorna um resultset.

No exemplo a seguir estamos criando uma UDF Table Valued que retorna os detalhes dos pedidos para um determinado cliente.

Abaixo vemos a instrução para criar a UDF, sua execução e a UDF criada com o parâmetro CustomerID:

sql_udf17

Para testar a UDF criada, podemos usar instruções SELECT conforme mostra os exemplos a seguir:

Obtendo os detalhes dos produtos para um cliente em uma cláusula FROM

Neste exemplo estamos obtendo os detalhes para o cliente de código igual a VINET:

sql_udf18

3. Criando uma Multi-Statement Table Valued Function

Uma UDF Multi-Statement retorna uma tabela. Ele pode executar uma ou mais de uma instruções T-SQL. No Corpo de comandos da função é necessário definir a estrutura da tabela que será retornada.

Este tipo de UDF permite que você tenha uma lógica mais complexa do que a UDF table valued. Depois de criar esse tipo de UDF, você pode usá-la na cláusula FROM de uma instrução SELECT.

No exemplo abaixo estamos criando a UDF DetalhesPedidoCliente onde definimos a estrutura da tabela PedidosCliente que será retornada e usamos como parâmetro o código do cliente – CustomerID:

sql_udf19

Obtendo os detalhes de um pedido do cliente em uma cláusula FROM

Neste exemplo estamos obtendo os detalhes do pedido para o cliente de código igual a VINET:

sql_udf1a

Benefícios na utilização de UDFs:

  • Flexibilidade de usar a saída de uma UDF – Uma SQL UDF pode ser executada através de instruções Select ou consultas inline. A instrução SELECT pode mostrar a saída de uma UDF escalar como resultado. As funções escalares podem ser usadas em cláusulas SELECT, WHERE, HAVING e UDFs Table valued podem ser usadas em cláusulas FROM, JOIN, CROSS APPLY.

  • Código Enxuto – As UDF permite escrever blocos de código separados para uma lógica complexa e incluir na consulta principal. Isso torna o código menos complexo, fácil de escrever e manter.

  • Execução Rápida – Com os procedimentos armazenados, as UDFs reduzem o custo de compilação de código T-SQL através do cache do plano de execução e sua reutilização para execuções posteriores. Ele não necessita ser reprocessado e está otimizado com cada execução, resultando em execução muito mais rápida.

Limitações das UDFs:

  • Funções como GETDATE e RAND não podem ser usadas nas UDFs

  • UDFs retornam apenas um valo por resultset

  • UDFs não podem chamar Stored Procedures

  • Em uma UDF não podemos criar tabelas temporárias

  • As UDFs não suportam a cláusula FOR XML

  • As UDFs não suportam o tratamento de erro. Não podemos usar RAISEERROR ou @@ERROR.

Dessa forma, UDFs são rotinas que podem executar uma lógica complexa, fazer cálculos, usar um ou mais parâmetros e retornar um valor escalar ou um tipo de dados tabela. Também são rápidas e têm um código fácil de manter, mas também possuem suas limitações. Avalie e planeje antes de usar o recurso.