Desvendando o SQL - Parte X - Funções

Estamos novamente aqui com vocês, hoje pra falar de mais um assunto da linguagem SQL que são as FUNCTIONS ou Funções. No artigo passado falamos sobre Procedures, as FUNCTIONS são bem parecidas, pois também podem realizar alguns processos, receber parâmetros, com uma diferença, as funções devolvem um resultado.

Utilização


Podemos utilizá-las para criar rotinas reutilizáveis das maneiras abaixo:

  • Em instruções Transact-SQL, como SELECT;
  • Em aplicativos que chamam a função;
  • Na definição de outra função definida pelo usuário;Para parametrizar uma exibição ou aprimorar a funcionalidade de uma exibição indexada;
  • Para definir uma coluna em uma tabela;
  • Para definir uma restrição CHECK em uma coluna;
  • Para substituir um procedimento armazenado.


Sintaxe


--Transact-SQL Scalar Function Syntax
CREATE FUNCTION nome_funcao
( [ { @nome_parametro [ AS ][ type_schema_name. ] tipo_dado_parametro
    [ = default ] [ READONLY ] }
    [ ,...n ]
  ]
)
RETURNS tipo_dado_retorno
    [ WITH [ ,...n ] ]
    [ AS ]
    BEGIN
        function_body
        RETURN scalar_expression
    END
[ ; ]
--Transact-SQL Inline Table-Valued Function Syntax
CREATE FUNCTION [ schema_name. ] nome_funcao
( [ { @nome_parametro [ AS ] [ type_schema_name. ] tipo_dado_parametro
    [ = default ] [ READONLY ] }
    [ ,...n ]
  ]
)
RETURNS TABLE
    [ WITH [ ,...n ] ]
    [ AS ]
    RETURN [ ( ] select_stmt [ ) ]
[ ; ]

Instruções válidas



Todas as instruções a seguir são válidas em uma função, ou seja, podemos utilizar no interior de uma função:

  • Instruções de atribuição.
  • Instruções de controle de fluxo com exceção das instruções TRY...CATCH.
  • Instruções DECLARE que definem variáveis de dados locais e cursores locais.
  • Instruções SELECT que contêm listas de seleção com expressões que atribuem valores a variáveis locais.
  • Operações de cursor que fazem referência a cursores locais que são declaradas, abertas, fechadas e desalocadas na função. Apenas instruções FETCH que atribuem valores a variáveis locais usando a cláusula INTO são permitidas. Instruções FETCH que retornam dados ao cliente não são permitidas.
  • Instruções INSERT, UPDATE e DELETE que modificam variáveis de tabela locais.
  • Instruções EXECUTE que chamam procedimentos armazenados estendidos.

Limitações e restrições


Quando criamos funções não podemos utilizá-las para modificar o estado do banco, também não podemos usar OUTPUT INTO para retornar para uma tabela.

Aninhamento


As funções criadas podem ser aninhadas, ou seja, utilizar uma dentro de outra, até o 32º nível. Caso seja excedido esse número haverá uma falha na cadeia inteira de funções.

Exemplos


Comecemos a mostrar alguns exemplos de funções, vamos iniciar mostrando uma função que faz uma coisa simples, uma função que mostra o número da semana, tendo como parâmetro de entrada uma data especifica;

IF OBJECT_ID (N'dbo.NmSemana', N'FN') IS NOT NULL
    DROP FUNCTION dbo.NmSemana;
GO
CREATE FUNCTION dbo.NmSemana (@DATE datetime)
RETURNS int
WITH EXECUTE AS CALLER
AS
BEGIN
     DECLARE @NmSemana int;
     SET @NmSemana= DATEPART(wk,@DATE)+1
          -DATEPART(wk,CAST(DATEPART(yy,@DATE) as CHAR(4))+'0104');
--Special cases: Jan 1-3 may belong to the previous year
     IF (@NmSemana=0)
          SET @NmSemana=dbo.NmSemana(CAST(DATEPART(yy,@DATE)-1
               AS CHAR(4))+'12'+ CAST(24+DATEPART(DAY,@DATE) AS CHAR(2)))+1;
--Special case: Dec 29-31 may belong to the next year
     IF ((DATEPART(mm,@DATE)=12) AND
          ((DATEPART(dd,@DATE)-DATEPART(dw,@DATE))>= 28))
          SET @NmSemana=1;
     RETURN(@NmSemana);
END;
GO
SET DATEFIRST 1;
SELECT dbo.NmSemana(CONVERT(DATETIME,'08/14/2013',101)) AS 'Número da Semana';

Agora vamos criar uma função para retornar uma tabela especifica de acordo um o código do produto passado.



IF OBJECT_ID (N'FNMovimProdutos', N'IF') IS NOT NULL
    DROP FUNCTION FNMovimProdutos;
GO
CREATE FUNCTION FNMovimProdutos (@codigo int)
RETURNS TABLE
AS
RETURN
(
    SELECT DESC_PRODUTO, QTD_PRODUTO, TP_MOVIMENTO, DTH_MOVIMENTO, SUM(VR_MOVIMENTO)
      FROM
            TBPRODUTO P INNER JOIN
            TBMOVIMENTOPRODUTO M ON M.CD_PRODUTO = P.CD_PRODUTO INNER JOIN
            TBMOVIMENTODIARIO D ON D.CD_MOVIMENTO = M.CD_MOVIMENTO
      WHERE CD_PRODUTO = @CODIGO
      GROUP BY CD_PRODUTO, DESC_PRODUTO, QTD_PRODUTO, TP_MOVIMENTO, DTH_MOVIMENTO
);
GO


Para invocar a função, execute esta consulta.


SELECT * FROM FNMovimProdutos (6);

Aqui encerramos mais esse arquivo sobre a linguagem SQL onde mostramos um pouco da utilização de FUNÇÕES, caso tenha dúvidas ou sugestões, não se esqueça de comentar. No próximo artigo vamos falar um pouco sobre as TRIGGERS ou GATILHOS que podemos colocar nas tabelas.

Grande abraço e até lá.

Artigos anteriores:

Comentários

Postagens mais visitadas