Artigos Técnicos :: SQL Server

Artigo
09/10/2008

Criando Funções Transact-SQL no Microsoft SQL Server 2000

Por Roberto Nantes – Application Engineer

Microsoft Brasil User Defined Functions - Considerações Gerais

User-Defined Functions, são funções definidas pelo próprio usuário, que não fazem parte do conjunto de funções do SQL Server, essas funções podem retornar valores comuns ou mesmo um valor no formato de uma tabela, com múltiplas ocorrências.

Uma função pode ser definida com a utilização de múltiplos comandos em Transact-SQL, retornando valores de acordo com o princípio para o qual foi desenvolvida. Dependendo da maneira como as instruções são colocadas no corpo de uma função, a função poderá ser classificada como sendo INLINE ou MULTI-STATEMENT.

Se o retorno da função for uma tabela que não corresponde à lista de colunas especificada, a função é considerada INLINE. Uma função INLINE é uma função que retorna uma tabela, contendo em seu corpo de definição apenas uma instrução SELECT. As colunas, incluindo os tipos de dados, da tabela a ser retornada pela função, são derivadas da lista de campos da cláusula SELECT definida no corpo da função.

Se existe uma definição sobre a estrutura da tabela, bem como os seus campos com os respectivos tipos de dados, a função será considerada como MULTI-STATEMENT.

As instruções a seguir, correspondem as operações que podemos realizar no corpo de definição de uma função MULTI-STATEMENT, o que não estiver nessa lista, não pode ser utilizado dentro de uma função definida pelo usuário:

Operações de atribuição:

Instruções de controle de fluxo(WHILE,CASE,IF).

Instruções de DECLARE para variáveis e cursores locais no escopo da função.

Instruções de SELECT para fazer operações de atribuição em variáveis locais do escopo da função.

Operações para manipulação de cursores locais na função, como abrir, fechar e etc. Apenas instruções FETCH que fazem atribuições em variáveis locais na função serão permitidas usando a cláusula INTO; FETCH que retorne dados diretamente para um cliente não é possível.

INSERT, UPDATE, e DELETE que modifique uma variável local do tipo table.

Além disso, não é possível utilizar nondeterministec functions no corpo de uma função.

Nondeterministec Functions são funções que sempre retornam valores diferentes cada vez que você faz uma chamada, independente dos valores de entrada. Veja alguns exêmplos na tabela a seguir:

@@CONNECTIONS @@TOTAL_ERRORS

@@CPU_BUSY @@TOTAL_READ

@@IDLE @@TOTAL_WRITE

@@IO_BUSY GETDATE

@@MAX_CONNECTIONS GETUTCDATE

@@PACK_RECEIVED NEWID

@@PACK_SENT RAND

@@PACKET_ERRORS TEXTPTR

@@TIMETICKS

Invocando Funções:

Funções que retornem valores comuns podem ser invocadas em expressões, incluindo valores COMPUTED ou CHECK constraint. Para invocar uma função que retorna um valor comum, utilizamos pelo menos 2 partes para definir o nome da função.

[database_name.]owner_name.function_name ([argument_expr][,...])

Funções que retornem valores no formato de tabelas, podem ser invocadas apenas com o simples nome da função.

[database_name.][owner_name.]function_name ([argument_expr][,...])

As funções de sistema que estão incluídas no Microsoft SQL Server 2000 devem ser invocadas utilizando o prefixo “::” antes do nome da função. Por exemplo:

FROM ::fn_helcollations

Permissões:

Os usuários devem ter a permissão para executar a instrução CREATE FUNCTION. Essa permissão é concedida por Default para os usuários integrantes dos grupos sysadmin, db_owner e db_ddladmin. Os membros de sysadmin e db_owner podem conceder permissão para outros LOGINS através do commando GRANT.

O usuário OWNER de uma função, tem permissão de execução naquela mesma função. Os outros usuários não poderão executar a função, a menos que a permissão seja concedida de forma explícita.

Cenário:

Você possui tabelas no seu banco de dados, que guardam informações referentes ao horário no qual os registros foram inseridos e atualizados. Usando a função GetDate(), que armazena data e hora no formato “2000-03-30 08:30:00.000.”.

Você deseja representar essas datas em diferentes formatos, a função irá receber o caracter separador de datas e irá reformatar a informação obtida em alguma coluna de acordo com o separador que você escolheu.

Tarefa:

Você irá criar uma função que servirá para a formatação de datas em qualquer campo do tipo datetime.

Inicie o SQL Server Query Analyzer, e use o banco de dados Northwind. Crie a Função conforme o script abaixo.

CREATE FUNCTION udf_MyDate (@indate datetime, @separator char(1))
RETURNS Nchar(20)
AS
BEGIN
  RETURN 
  CONVERT(Nvarchar(20), datepart(mm,@indate))
  + @separator
  + CONVERT(Nvarchar(20), datepart(dd, @indate))
  + @separator
  + CONVERT(Nvarchar(20), datepart(yy, @indate))
END
GO

Utilize o Query Analyzer para visualizar o formato das colunas datetime na tabela orders, executando o seguinte comando:

SELECT OrderDate, RequiredDate, ShippedDate 
FROM Orders WHERE OrderID < 11000

Use a função criada no passo 2, para visualizar a coluna OrderDate no formato mm/dd/yyyy. Executando o seguinte comando:

SELECT OrderID, 
'Order Date' = dbo.udf_MyDate(OrderDate, '/')
FROM Orders WHERE OrderID < 11000

Utilize a função criada no passo 2, para representar as colunas OrderDate e ShippedDate no formato mm-dd-yyyy. Executando o seguinte comando:

SELECT OrderID,
'Order Date' = dbo.udf_MyDate(OrderDate, '-'),
'Ship Date' = dbo.udf_MyDate(ShippedDate, '-')
FROM Orders WHERE OrderID < 11000

Criando funções e retornando tabelas

Podemos criar funções que retornem tabelas, como sendo um poderoso mecanismo e alternativa se comparado a uma View. Podemos utilizar uma função que retorne uma tabela, em qualquer expressão onde uma tabela ou uma view seja aplicável nas instruções Transact-SQL. Enquanto uma view esta simplesmente limitada a uma simples instrução de SELECT, em uma função, podemos incluir outras instruções Transact-SQL incrementando a complexidade lógica da solução, o que não seria possível através da utilização de uma view. Uma função que retorne uma tabela, pode também ser utilizada no lugar de uma stored-procedure que retorne uma Result Set. A tabela a ser retornada pode ser referenciada na cláusula FROM de uma instrução Transact-SQL, o que não seria possível através de uma stored-procedure. Vamos então criar uma função onde passaremos um parâmetro e obtemos um resultado no formato de uma tabela.

Vamos então criar uma função que faça um JOIN entre as tabelas Shippers e Orders e retorne um Result Set.

Crie uma função através da execução do Script a seguir:

CREATE FUNCTION udf_LargeFreight (@FreightParm money)
RETURNS @OrderShipperTab TABLE
  (ShipperID int,
  ShipperName Nvarchar(80),
  OrderID int,
  ShippedDate datetime,
  Freight money)
AS
BEGIN
  INSERT @OrderShipperTab
  SELECT S.ShipperID, S.CompanyName,
    O.OrderID, O.ShippedDate, O.Freight
  FROM Shippers AS S, Orders AS O
  WHERE S.ShipperID = O.ShipVia
  AND O.Freight > @FreightParm
  RETURN
END

Utilize a função criada no passo 1, em uma instrução SELECT, conforme o comando abaixo:

SELECT * FROM udf_LargeFreight($500)

Agora, você pode utilizar a instrução DROP FUNCTION para eliminar a função criada no passo 1.

DROP FUNCTION dbo.udf_LargeFreight

Você pode também criar uma INLINE function, conforme discutimos anteriormente, executando o script a seguir:

CREATE FUNCTION udf_CustNamesInRegion (@RegionParameter Nvarchar(30))
RETURNS table
AS
RETURN ( SELECT CustomerID, CompanyName, City, Region
  FROM Customers
  WHERE Region = @RegionParameter )
GO

Execute a função criada no passo anterior através das seguintes queries:

SELECT DISTINCT CompanyName, City 
FROM udf_CustNamesInRegion ('WA')