SQL - Usando UDF - User Defined Function no SQL Server

 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)

Uma UDF - User Defined Function é 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:

  1. UDFs que retornam valores escalares
  2. 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: http://northwinddatabase.codeplex.com/

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:

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 :

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

1- 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 :

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

2- 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 aliás 'Total Pedido' para os números de pedido maior que 10300 :

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 retorno 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 :

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

1- 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:

2- 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 permitir que você tenha uma lógica mais complexa do que as 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 :

1- 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:

Benefícios na utilização de UDFs :

Limitações das UDFs:

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

    Lucas 9:23 E dizia a todos : Se alguém quer vir após mim, negue-se a si mesmo, e tome cada dia sua cruz, e siga-me.
    Lucas 9:24 Porque, qualquer que quiser salvar a sua vida, perdê-la-á; mas qualquer que, por amor de mim, perder a sua vida, a salvará.

Veja os Destaques e novidades do SUPER DVD Visual Basic (sempre atualizado) : clique e confira !

Quer migrar para o VB .NET ?

Quer aprender C# ??

Quer aprender os conceitos da Programação Orientada a objetos ?

Quer aprender o gerar relatórios com o ReportViewer no VS 2013 ?

  Gostou ?   Compartilhe no Facebook   Compartilhe no Twitter

Referências:

José Carlos Macoratti