BADA3 SEMANA 01 a 19

Prévia do material em texto

ITB INSTITUTO TECNOLÓGICO DE BARUERI
FUNCTIONS
Functions ou Funções (em português) são blocos T-SQL nomeados, muito
semelhantes as Stored Procedures. A diferença principal é que as Functions
obrigatoriamente devem retornar um valor.
As functions são muito utilizadas para computar valores, promover reusabilidade e
facilidade de manutenção e podem ser chamadas a partir de outros blocos T-SQL ou
até mesmo por instruções SQL. É muito comum utilizarmos funções em instruções
SQL para efetuar conversões de dados, formatar datas, realizar cálculos agregados,
contar o total de linhas, etc.
Existem também as funções internas do SQL Server que você pode usar para
manipular dados.
FUNCTIONS INTERNAS DO SQL
O SQL Server possui um conjunto imenso de funções, por
isso antes de sair criando funções, veja se a que você procura já
existe.
Segue nos próximos slides, alguns exemplos de functions
internas do SQL que são consideradas como as mais utilizadas
FUNCTIONS INTERNAS DO SQL
CAST e CONVERT
São funções que convertem um valor de um tipo para outro. CAST é SQL ANSI e CONVERT é T-SQL.
Exemplo: Converta a string
GETDATE()
GETDATE() é uma função que retorna a data e hora da instância do SQL Server na qual você está conectado, 
de forma resumida, data e hora corrente com o tipo datetime.
DATEPART
Dada uma data, com esta função extraímos parte da data. Por exemplo, quero extrair da data o mês, ou o ano, 
ou o dia. Esta função recebe como parâmetros a parte que será extraída (DAY, MONTH, YEAR) e uma data.
Exemplo: DATEPART(month
FUNCTIONS INTERNAS DO SQL
DATEADD
Use a função DATEADD, passando como parâmetros o que você vai somar YEAR, ou MONTH, ou DAY, o 
número que você vai somar e a data na qual você quer fazer a soma.
year
DATEDIFF
Retorna a diferença em dias, anos, meses entre duas datas.
DATEDIFF(day
FUNCTIONS INTERNAS DO SQL
Substring
Imagina que você precisa somente de uma parte de uma string. Para extrair parte de uma 
string use a função SUBSTRING passando como parâmetros, a string, a posição inicial e o 
tamanho da string.
abcde
LEN
Use a função LEN para saber o tamanho de uma string.
3
FUNCTIONS INTERNAS DO SQL
CONCAT Função para concatenar valores.
Essa função é utilizada para concatenar dois ou mais valores sem precisar se 
preocupar com os tipos de dados que está concatenando. Com a função 
-se concatenar string com data, com inteiro, float.
EXEMPLOS:
SELECT CONCAT(NOME_FILME_PT, ' ', DATA_LANCAMENTO) AS CONCATENADO
FROM FILME;
RETORNA VIVIANESANTANA
RETORNA VIVIANE SANTANA
* É possível também declarar variáveis e seus valores e executar a concatenação
FUNÇÕES DE AGREGAÇÃO
Funções de agregação são funções SQL que permitem 
executar uma operação aritmética nos valores de uma coluna em 
todos os registros de uma tabela. 
Retornam um valor único baseado em um conjunto de 
valores.
Sintaxe:
SELECT <função de agregação (campo)> FROM <tabela> 
[WHERE conditions] ;
FUNÇÕES DE AGREGAÇÃO
Estas funções agregam um conjunto de valores e retornam um resultado. 
Normalmente são usadas com a cláusula GROUP BY. Existem 
listadas abaixo:
COUNT Faz uma contagem
SUM Retorna o Total (Soma) de um conjunto de valores 
AVG Retorna a Média Aritmética de um conjunto de valores 
MAX Retorna o maior valor de um conjunto de valores 
MIN Retorna o menor valor de um conjunto de valores 
Todas recebem como parâmetro o nome da coluna. Cabe ressaltar que a função 
GROUP BY
A cláusula GROUP BY é usada em uma instrução 
SELECT para coletar dados em vários registros e agrupar 
os resultados por uma ou mais colunas. 
GROUP BY
Sintaxe:
SELECT <campo1>, <campo2>, <campo n>, <função agregada (campo)>
FROM <tabela> 
[WHERE conditions] 
GROUP BY <campo1>, <campo2>, <campo n>;
* Função agregada pode ser uma função, como: SUM, COUNT, MIN, MAX ou AVG.
FUNCTIONS - UDF - USER DEFINED FUNCTION
Você pode criar funções específicas para o seu contexto de negócio usando o T-SQL. 
Uma UDF - User Defined Function (Função definida pelo usuário) é um segmento de código 
preparado que pode aceitar parâmetros, processar uma lógica e retornar dados em um banco de dados 
SQL Server.
Da mesma forma que os procedimentos armazenados, as User Defined Functions desempenham 
um papel muito importante no SQL Server. 
Embora haja diferenças entre Stored Procedures e UDFs, as UDFs podem ser usadas para 
executar uma lógica complexa, podem aceitar parâmetros e retornar dados. O resultado da função pode 
ser usado em instruções Select, com cláusula Where e em junções (Join).
Existem dois tipos principais de UDFs:
UDFs que retornam valores escalares
UDFs que retornam table values
FUNCTIONS - UDF - USER DEFINED FUNCTION
Tipo de Retorno Características
Scalar Similar a funções em outras linguagens. Retorna um valor único de 
um tipo de dado scalar.
Inline Table Retorna um rowset de um tipo de dados table do SQL Server.
MultiStatement Table
Define explicitamente a estrutura da tabela a retornar. Define nomes 
de colunas e tipo de dados na cláusula RETURN.
CRIAÇÃO DE FUNCTION SCALAR
Uma funtion scalar retorna um único valor e que pode receber uma lista de 
parâmetro.
SINTAXE:
CREATE FUNCTION schema.Nome da Função
( @NomeParâmetro ASTipo Parâmetro)
RETURNS Data Type do retorno da Função
AS
BEGIN
Comandos
RETURNValor que a função deve retornar
END
EXEMPLO - CRIAÇÃO DE FUNCTION SCALAR
Para você ver o uso de uma função de forma mais prática, vamos criar uma função que subtrai dois valores:
CREATE FUNCTION SubtracaoPostBlog
( @Valor1 AS INT, 
@valor2 as INT)
RETURNS INT
AS
BEGIN
DECLARE @Retorno AS INT 
Set @Retorno = @Valor1 - @Valor2 
RETURN @Retorno
END
Para você validar a sua função, faça um SELECT com valores conhecidos e verifique o retorno
SELECT [dbo].[SubtracaoPostBlog] ( 20,10) GO 
CRIAÇÃO DE FUNCTION - IN LINE TABLE
Uma funtion in line table retorna uma tabela. 
Dado um parâmetro, você vai executar uma consulta. Este tipo de function que possui um único 
comando e o retorno é o resultado da execução deste comando é chamada de Inline Table-Valued Function.
SINTAXE:
CREATE FUNCTION schema.Nome da Função
( @NomeParâmetro AS Tipo Parâmetro)
RETURNS Data Type do retorno da Função
AS
BEGIN
RETURN Comando SELECT 
END 
EXEMPLO - CRIAÇÃO DE FUNCTION IN LINE TABLE
Veja o exemplo a seguir, onde a função retornará a quantidade de filmes por gênero 
indicados para alguma premiação (exemplo Teen Choice Award: Escolha de Filme do Verão 
Vamos usar nosso banco de dados Cinema.
EXEMPLO - CRIAÇÃO DE FUNCTION IN LINE TABLE
CREATE FUNCTION udf_qtde_filmes_por_genero_indicados
(@COD_PREMIACAO int)
RETURNS TABLE
AS
RETURN (SELECT GENERO.NOME_GENERO, COUNT(INDICACAO.COD_FILME) AS Quantidade_de_Filmes_Indicados
FROM GENERO INNER JOIN FILME_GENERO 
ON GENERO.COD_GENERO = FILME_GENERO.COD_GENERO 
INNER JOIN INDICACAO 
ON FILME_GENERO.COD_FILME = INDICACAO.COD_FILME
INNER JOIN PREMIACAO 
ON INDICACAO.COD_PREMIACAO = PREMIACAO.COD_PREMIACAO
WHERE PREMIACAO.COD_PREMIACAO = @COD_PREMIACAO 
GROUP BY GENERO.NOME_GENERO);
GO
EXEMPLO - CRIAÇÃO DE FUNCTION IN LINE TABLE
O retorno deste tipo de função é uma tabela e uma tabela 
deve ser usada na cláusula FROM.
Sendo assim para testar esse tipo de função você pode 
executar um comando SELECT usando a function na cláusula 
FROM.
SELECT * FROM dbo.udf_qtde_filmes_por_genero_indicados (120) 
CRIAÇÃO DE FUNCTION - MULTI-STATEMENTTABLE
SINTAXE:
CREATE FUNCTION schema.Nome da Função
( @NomeParâmetro AS Tipo Parâmetro)
RETURNS @Variável que armazena a tabela AS Definição da Tabela
AS
BEGIN
RETURN @Variável que armazena a tabela
END 
EXEMPLO - CRIAÇÃO DE FUNCTION MULTI-STATEMENT
TABLE
Veja o exemplo a seguir, onde a função retornará a quantidade de filmes por gênero 
indicados para alguma premiação (exemplo Teen Choice Award: Escolha de Filme do Verão 
-se o formato da tabela que retornará o resultado. 
Vamos usar nosso banco de dados Cinema.
EXEMPLO - CRIAÇÃO DE FUNCTION MULTI-STATEMENT
TABLE
CREATE FUNCTION dbo.udf_qtde_filmes_por_genero_indicados2(@COD_PREMIACAO int)
RETURNS @VariavelTabelaTABLE
(GENERO VARCHAR(70) NOT NULL,
QUANTIDADE_DE_FILMES_INDICADOS INT NOT NULL)
AS
BEGIN 
INSERT INTO @VariavelTabela
SELECT GENERO.NOME_GENERO, COUNT(INDICACAO.COD_FILME) AS Quantidade_de_Filmes_Indicados
FROM GENERO INNER JOIN FILME_GENERO 
ON GENERO.COD_GENERO = FILME_GENERO.COD_GENERO 
INNER JOIN INDICACAO 
ON FILME_GENERO.COD_FILME = INDICACAO.COD_FILME
INNER JOIN PREMIACAO 
ON INDICACAO.COD_PREMIACAO = PREMIACAO.COD_PREMIACAO
WHERE PREMIACAO.COD_PREMIACAO = @COD_PREMIACAO 
GROUP BY GENERO.NOME_GENERO
RETURN
END;
GO
EXEMPLO - CRIAÇÃO DE FUNCTION MULTI-STATEMENT
TABLE
O retorno deste tipo de função é uma tabela e uma tabela deve 
ser usada na cláusula FROM.
Sendo assim para testar esse tipo de função você pode 
executar um comando SELECT usando a function na cláusula FROM.
SELECT * FROM dbo.udf_qtde_filmes_por_genero_indicados2 (120)