SQL Server: Funções de usuário – (User Functions)

Este artigo tem como objetivo mostrar ao leitor os tipos de funções de usuário, e os benefícios que este recurso traz aos projetos de banco de dados em performance, e em partes de códigos reutilizáveis.

por Thiago Pastorello Gervazoni

No Sql Server 2000 um dos recursos que a nova versão apresentou foi as user functions, ou funções de usuário, o usuário cria suas próprias funções para auxiliar na busca da informação. As user functions podem receber um ou mais parâmetros, e não aceitam parâmetro de output.

As funções de usuário podem ser de três tipos

• Scalar Function
• Multi-statement Table-valued Function
• In-Line Table-valued Function

A criação de uma user function é similar à criação de uma stored procedure ou uma view.

Exemplo :

USE Northwind
CREATE FUNCTION fn_Region(@myinut nvarchar(30))
RETURNS nvarchar(30)
    BEGIN
       IF @myinput IS NULL
        SET @myinput = "Não Aplicável"
        RETURN @myinput
    END

Exemplo1. Função que retorna "Não Aplicável" em valores NULL

Restrições em funções

As user functions não aceitam funções não deterministicas, isto significa que não podemos usar funções que retornem valores diferentes a cada chamada como:

• @@Error
• @@Identity
• @@Rowcount
• @@Trancount
• APP_NAME
• CURRENT_TIMESTAMP
• CURRENT_USER
• DATENAME
• FORMATMESSAGE
• GETANSINULL
• GETDATE
• HOST_ID
• HOST_NAME
• IDENT_INCR
• IDENT_SEED
• IDENTITY
• NEWID
• PERMISSIONS
• SESSION_USER
• STATS_DATE
• SYSTEM_USER
• TEXTPTR
• TEXTVALID
• USER_NAME

As users functions podem ser criadas com schemabinding que colam a função a outro objeto, depois do schemabinding só é possível acessar o objeto em duas partes dbo.nome, o schemabinding permite uma integridade, não deixando apagar o objeto sem antes retirar o schemabinding.

As user functions precisam de permissões de execute para poderem funcionar, e permissão de CREATE, ALTER ou DROP como qualquer outro objeto, como as user functions podem retornar tabelas é preciso ter acesso também de REFERENCES, ou seja, este tipo de permissão que é dada a tabelas também permite que eu acesse tabelas que são referenciadas através de Foreign Keys, que na verdade são parte da informação que está em outra tabela.

Scalar Function

As funções scalares são bem parecidas com as bult-in functions, que existem no banco de dados como a Getdate(), e este tipo de função retorna apenas um parâmetro, como o exemplo abaixo eu montei uma scalar function que retorna o endereço mais o código postal.

Exemplo:

CREATE Function sc_RetornaSupplier(@Supplier smallint)
RETURNS Nchar(40)
AS
BEGIN
  DECLARE @Retorno Nchar(40) 
  SET @Retorno=(SELECT Address + " *** " + PostalCode FROM dbo.Suppliers WHERE SupplierID=@Supplier)
  RETURN @Retorno
END

Modo de usar:

SELECT ProductID
   ,productName
   ,MinhaFuncao=dbo.sc_RetornaSupplier(SupplierID) 
FROM products

Retorno:

Posso também chamar isoladamente.

Modo de usar:

select MinhaFuncao=dbo.sc_RetornaSupplier(1)                                                                                

Retorno:

Multi-statement Table-valued Function

Como o próprio nome já diz a Multi-Statement é utilizada para lógicas mais complexas que costumam conter condicional, outra diferença considerável também é que este tipo de função retorna uma tabela, e o que é bastante interessante que eu mencionei na minha coluna anterior sobre integridade disse que uma tabela sem chave primária é uma tabela sem integridade de domínio, e esta sujeita a erros pertinentes a esta falta de integridade.

Aqui é possível definir chave primária, identity etc, no retorno da tabela, deixando bem claro que este tipo de função retorna dados confiáveis e íntegros.

Exemplo:

CREATE Function ms_RetornaProducts(@Valor decimal(18,2)=NULL,@Descontinuado bit=NULL)
RETURNS @TabelaProduto table(Codigo INT PRIMARY KEY,Nome varchar(50),Estoque varchar(50),obs varchar(50))
AS
BEGIN
     IF @Valor IS NOT NULL
       INSERT INTO @TabelaProduto
           SELECT ProductID,ProductName,unitsInStock,case when unitsInstock<10 then "Estoque baixo" 
           else "Estoque ok"   end FROM dbo.Products WHERE unitPrice>@Valor
     ELSE IF @Descontinuado=1
           INSERT INTO @TabelaProduto
           SELECT ProductID,ProductName,"Sem estoque",case when unitsInstock<5 then "Possui estoque" 
           else "limpar estoque" end FROM dbo.Products WHERE Discontinued=@Descontinuado
  RETURN
END

Modo de usar:

SELECT * FROM dbo.ms_RetornaProducts(18.00,null)                                                                                       

Retorno:

Modo de usar para ver os descontinuados:

SELECT * FROM dbo.ms_RetornaProducts(null,1)                                                                                     

Retorno:

É possível ainda tratar esta função como uma tabela propriamente dita veja o exemplo selecionando campos e filtrando retorno.

SELECT codigo,obs FROM dbo.ms_RetornaProducts(5.00,null) WHERE Nome LIKE "%queso%"        

Retorno:

In-Line Table-valued Function

As in-lines functions são muito utilizadas para parametrizar views, este tipo de função não requer BEGIN, END na criação do seu corpo, e também dá muita performance quando usada em views indexadas, o retorno deste tipo de função também é uma tabela, ma costuma-se criar o esquema em momento de execução.

Exemplo:

CREATE Function il_RetornaCustomers(@Regiao nvarchar(30))
RETURNS table
AS
RETURN (SELECT CustomerID as Codigo,CompanyName as Nome FROM Customers WHERE    Region=@Regiao)

Modo de usar:

SELECT * FROM dbo.il_RetornaCustomers("WA")                                                                                     

Retorno:

Boas práticas em performance

• Usar scalar functions complexas em resultados pequenos
• Usar Multi-statement functions ao invés de stored procedures para retornar tabelas
• Usar In-line functions para criar views parametrizadas
• Usar In-line functions para filtrar views indexadas

As user-functions são muito úteis e um ótimo recurso a ser utilizado dentro do Sql Server 2000, são melhores que os famosos CASE WHEN dentro de procedures, ou mesmo os IF´s, porque são objetos e os objetos criam planos de execução, que dão mais performance a cada vez que são chamadas, se no seu projeto de banco de dados não possui user functions, fica uma ótima sugestão para implementar e colher os resultados em performance. Eu utilizo bastante todos os tipos de funções citadas acima e aconselho, claro que tudo deve ser maturado e usado na hora certa e maneira correta para que possa ser um benefício.

Até mais.