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