artigo sql magazine 51 - UDFs e Funções Escalares

SQL

UDFs e Funções Escalares

Trabalhando com funções escalares definidas pelo usuário

A linguagem SQL oferece literalmente dezenas de funções internas que nos ajudam a solucionar problemas de tratamento de dados.

Cada sistema gerenciador de banco de dados (SGBD) oferece uma biblioteca interna de funções específica do produto. Muitas destas funções seguem o padrão ANSI SQL, mas sempre existem pequenas variações de sintaxe, dependendo do SGBD utilizado.

Neste caso, o que o desenvolvedor pode fazer caso a função que ele necessite não exista no seu SGBD?

Um recurso extremamente útil previsto no ANSI SQL é a possibilidade de criação de nossas próprias funções. Estas são as funções definidas pelo usuário, ou UDF, na sigla em inglês.

Neste artigo, vamos abordar a criação e uso das funções escalares criadas pelo usuário.

O que é uma função escalar

Chamamos de função escalar aquela que retorna um único valor como resultado.

Todas as funções internas do SQL mais conhecidas são escalares. Exemplo: SUM, DATEADD, COALESCE, etc. Elas sempre retornam um único valor como resultado.

A mesma idéia vale para as UDFs. Dado um conjunto de parâmetros para uma função escalar, esta deverá retornar um único valor como resultado. Mais do que isso, este resultado será sempre o mesmo para um conjunto de parâmetros (veja a Nota 1 sobre Funções Escalares Determinísticas).

Nota 1. Funções Escalares Determinísticas
Via de regra, as UDFs devem retornar o mesmo resultado quando passamos um dado conjunto de parâmetros para a função.
As funções que se comportam desta maneira são chamadas determinísticas. Porém, uma função pode ser escalar e não ser determinística. Um exemplo é a função interna RAND(), que retorna um número aleatório quando é chamada sem nenhum parâmetro.

O SGBD estudado

Para este trabalho, usamos um banco de dados criado no SQL Server 2005. Portanto, os exemplos que apresentamos neste artigo usam a sintaxe adotada pelo Transact-SQL.

Mas devemos lembrar que UDFs são parte integrante do padrão ANSI SQL e são suportadas pela maioria dos SGBDs disponíveis no mercado.

A sintaxe adotada para criação das funções varia ligeiramente, mas a estrutura geral e a forma de chamada às funções são as mesmas.

Localizando as UDFs

No SQL SERVER 2005, as UDFs são vinculadas ao banco de dados em que são criadas. Dentro do Management Studio, elas são listadas junto de outros objetos programáveis do banco de dados, como procedimentos armazenados e gatilhos. Observe que o nó “Functions” se divide em vários outros nós, sendo um deles específico para as funções escalares, como vemos na Figura 1 (“Scalar-valued Functions”).

Todas as UDFs escalares existentes no banco de dados “Farmaceutico” serão visíveis embaixo deste nó. No exemplo da Figura 1, ainda não criamos nenhuma função, por isso o nó aparece vazio.

Figura 1. Encontrando as UDFs dentro do Management Studio.

A sintaxe para criação de UDFs

Criamos funções no SQL SERVER usando uma declaração de CREATE FUNCTION. A Listagem 1 apresenta um exemplo que cria uma função para encontrar o primeiro número que resolve uma equação de segundo grau.

Listagem 1. Criando uma função

1  CREATE FUNCTION MeuEsquema.Equacao2Grau

2     (@A NUMERIC(9,2),

3     @B NUMERIC(9,2),

4     @C NUMERIC(9,2))

5  RETURNS NUMERIC(9,2)

6  WITH EXECUTE AS CALLER

7  AS

8  BEGIN

9   DECLARE @Solucao NUMERIC(9,2)

10   SET @Solucao =

11    (-@B-SQRT(POWER(@B,2)-4*@A*@C))/(2*@A)

12

13   RETURN(@Solucao)

14  END;

Analisando a Listagem 1, vemos inicialmente que a função criada foi associada ao esquema MeuEsquema. A associação explícita com esquemas é uma boa prática de programação em sistemas de grande porte. Facilita bastante a manutenção e organização dos objetos do banco (tabelas, procedimentos armazenados e também funções definidas pelo usuário, entre outros).

A sintaxe de definição da função de usuário requer um tipo de dados que será retornado pela função. Em geral, também declaramos dentro da função uma variável que receberá o valor a ser retornado (no caso, @Solucao).

A cláusula “WITH EXECUTE AS” é uma novidade do SQL Server 2005. Ela permite que o usuário do objeto, como uma função ou procedimento armazenado (stored procedure, se preferir), utilize as credenciais de outro usuário para executá-lo. No caso, o parâmetro usado (CALLER) especifica que a função que criamos será executada usando as permissões que estão disponíveis para o usuário que chama a função. CALLER é o parâmetro default desta cláusula.

Finalmente, vamos estudar o resultado da função. Se puxarmos pela memória, vamos nos lembrar das aulas na escola do ensino fundamental: funções de segundo grau podem ter até duas soluções, que chamamos de raízes. Mas funções escalares só podem retornar um único valor. Por esta razão é que especificamos que a função retorna apenas a primeira raiz da equação.

Se quiséssemos encontrar a segunda raiz, precisaríamos criar uma segunda função escalar!

O SQL Server 2005 suporta também a criação de UDFs (além de outros objetos) usando a plataforma .NET. Neste caso, ao invés de SQL puro, usamos a chamada Common Language Runtime (CLR). ...