Microsoft SQL Server 2022: aprofundando em procedures e funções

Funções: UDF - Apresentação

Olá! Meu nome é Victorino Vila e serei instrutor no curso Microsoft SQL Server 2022: aprofundando em procedures e funções.

Esse curso é uma continuação do curso anterior. Veremos mais sobre T-SQL, que é a linguagem de programação que fornece uma extensão ao padrão ANSI do SQL Server.

Quem já tem experiência com programação sabe como funciona funções e procedures.

Inclusive, no SQL Server, já vimos uma série de funções existentes. Quando usamos o LTRIM para retirar espaços em branco do lado esquerdo de um texto, por exemplo, estamos usando uma função. A diferença é que essa função já está pronta no SQL Server.

Neste curso aprenderemos a criar uma função própria, a nossa função, com nossos códigos em T-SQL.

O mesmo vale para procedimentos (procedures). Normalmente, os procedimentos executam um determinado processo. Mas podem retornar valores através de um tipo de passagem de parâmetros utilizado durante a sua construção.

O terceiro assunto deste curso será cursor, que é uma estrutura do SQL que permite que possamos armazenar em memória o resultado de uma consulta e percorrer linha a linha dessa mesma consulta, fazendo assim operações em cada uma dessas linhas.

Espero que vocês gostem desse curso! Um abraço e até o próximo vídeo!

Funções: UDF - Definição de função

Já vimos em outros treinamentos que o SQL Server possui uma gama de funções que podem ser usadas não somente em comandos de seleção, mas também em comandos de T-SQL, inserts, updates, deletes e estruturas dentro de whiles e ifs.

Já fizemos alguns exemplos do uso de função, como por exemplo:

• Funções de texto: SUBSTRING, LEFT, TRIM, LTRIM, etc.
• Funções de data: DATEADD, DATEDIFF, MONTH, etc.
• Funções numéricas: CEILING, FLOOR, RAND, etc.
• Conversão de dados: CAST e CONVERT

Já vimos tudo isso. Mas uma novidade que aprenderemos é que podemos construir nossas próprias funções, usando nossa lógica, nossa programação.

Podemos usar essas funções da mesma maneira que utilizamos as funções predefinidas dentro do SQL Server.

Funções

O que é uma função?

Conjunto de declarações T-SQL que são armazenadas no banco de dados, e que são projetadas para retornar um conjunto de resultados (escalares ou tabelas).

Quando falamos que a função pode retornar um valor escalar, queremos dizer que pode retornar um string, um número ou uma data. "Escalar" significa qualquer tipo daqueles que usamos quando definimos campos no momento em que criamos uma tabela.

Essas funções são chamadas de funções do tipo UDF (User Defined-Function) é o nome dado às funções desenvolvidas pelos usuários do SQL Server.

Então, quando eu falar "Crie uma função UDF" estou querendo que você crie uma função sua, é uma função criada pela pessoa usuária que está conectada no SQL Server.

Características das funções UDF

• Seu corpo é delimitado por BEGIN e END;
• Deve sempre retornar um valor através de um RETURN;
• Após o nome da função devemos declarar as variáveis de entrada.

** Exemplo da forma com que se declara uma função:**

CREATE FUNCTION <NOME DA FUNÇÃO> (<PARAMETROS DE ENTRADA>)
RETURNS <TIPO DE RETORNO>
AS
BEGIN
-- CORPO DA FUNÇÃO
RETURN <VARIÁVEL DE RETORNO>
END

Nós usamos o comando CREATE FUNCTION para criar a função, em seguida escrevemos o nome da função e, entre parênteses, colocamos os parâmetros de entrada da função junto com seu tipo. Podemos ter um ou vários parâmetros, todos eles são declarados separados por vírgula.

Depois, usamos o comando RETURNS para determinar o tipo de retorno da função. Nesse comando RETURNS não colocamos o nome de uma variável, colocamos apenas o tipo que será retornado por aquela função.

Em seguida, colocamos AS BEGIN e digitamos o corpo da função. No corpo da função é que teremos as linhas em T-SQL descrevendo a regra de cálculo da função. Dentro desses comandos, em algum momento precisamos inserir o comando RETURN mostrando omomento em que a função exibe seus valores de saída.

Exemplo prático:

CREATE FUNCTION Concatenate
(
   @String1 VARCHAR(255),
   @String2 VARCHAR(255)
)
RETURNS VARCHAR(255)
AS
BEGIN
   RETURN @String1 + String2
     END

Temos aqui um exemplo prático de uma função que concatena duas strings.

Temos o comando CREATE FUNCTION, depois definimos o nome da função, no caso Concatenate. Entre parênteses declaramos as variáveis de entrada, duas strings junto com seu tipo.

No final, colocamos o comando RETURNS e o tipo de retorno AS BEGIN e colocamos o corpo da função e a palavra RETURN indicando o retorno, seguido por um END.

Estes foram os exemplos. No próximo vídeo criaremos a nossa primeira função.

Funções: UDF - Primeira função UDF

Vamos criar nossa primeira função. Para isso, estamos no Microsoft SQL Server Management Studio. Estou conectado no meu servidor de SQL Server e estou visualizando a base SUCOS_VENDAS.

Agora, vamos clicar em "Nova Consulta" e verificar se estamos visualizando mesmo a base SUCOS_VENDAS.

Vamos determinar o faturamento total de uma nota fiscal.

Se multiplicarmos a quantidade pelo preço teremos o faturamento. Como o preço está definido com cedilha, vamos colocá-lo entre colchetes.

Vamos pegar o SUM dessa multiplicação, colocar FROM [dbo].ITENS NOTAS FISCAIS e um filtro WHERE NUMERO = 100.

SELECT SUM(QUANTIDADE * [PREÇO])
FROM [dbo].[ITENS NOTAS FISCAIS]
WHERE NUMERO = 100

Essa consulta vai dar o faturamento da nota fiscal número 100. O campo NUMERO representa o número da nota fiscal.

Podemos clicar em "Executar" para verificar qual será o resultado:

O faturamento da nota fiscal 100 é 3080,168.

Agora, vamos fazer uma função que retornará o faturamento de uma nota e o parâmetro que passaremos para essa função é o número da nota fiscal.

Para começar a criação dessa função usaremos CREATE FUNCTION e o nome que definiremos para essa função, entre parênteses vamos definir o número de entrada, que é o número da nota.

Como sabemos que o número da nota fiscal, da tabela de itens, é um inteiro, vamos definir esse parâmetro como sendo um INT.

CREATE FUNCTION FaturamentoNota (@NUMERO AS INT)

Em seguida, vamos colocar RETURNS e o tipo do dado que essa função vai retornar.

Quando executamos a consulta anterior, o valor que foi retornado foi um número com casas decimais. Então, podemos definir o tipo de retorno como sendo um FLOAT.

CREATE FUNCTION FaturamentoNota (@NUMERO AS INT)
RETURNS FLOAT
AS
BEGIN



END;

Entre o BEGIN e o END colocaremos o conteúdo da função que queremos rodar, que é a que fizemos anteriormente. Mas vamos melhorar a função.

Primeiro vamos declarar uma variável chamada @FATURAMENTO, que vai receber o faturamento da nota fiscal, do tipo FLOAT.

E o SUM(QUANTIDADE * [PREÇO]) será atribuído à variável @FATURAMENTO. Vamos também substituir 100 por @NUMERO.

Agora, basta darmos RETURN @FATURAMENTO.

CREATE FUNCTION FaturamentoNota (@NUMERO AS INT)
RETURNS FLOAT
AS
BEGIN
   DECLARE @FATURAMENTO FLOAT
   SELECT @FATURAMENTO = SUM(QUANTIDADE * [PREÇO])
   FROM [dbo].[ITENS NOTAS FISCAIS]
   WHERE NUMERO = @NUMERO
   RETURN @FATURAMENTO 
END;

Temos o nosso comando. Agora vamos selecionar o bloco de código e clicar em "Executar".

Teremos o seguinte retorno:

Comandos concluídos com êxito.

Podemos verificar no painel da estrutura dos arquivos que temos uma pasta chamada "Programação". A função que acabamos de criar está no caminho Programação/Funções/Funções com valor escalar/dbo.FaturamentoNota.

Como usar a função?

Podemos usar essa função recém-criada dentro de um SELECT, por exemplo.

SELECT NUMERO, dbo.FaturamentoNota(NUMERO) AS FATURAMENTO
FROM [dbo].[NOTAS FISCAIS] 

Ao executar essa seleção conseguiremos visualizar o valor do faturamento de cada nota do banco de dados:

É isso! Criamos a nossa primeira função.

Sobre o curso Microsoft SQL Server 2022: aprofundando em procedures e funções

O curso Microsoft SQL Server 2022: aprofundando em procedures e funções possui 132 minutos de vídeos, em um total de 38 atividades. Gostou? Conheça nossos outros cursos de SQL e Banco de Dados em Data Science, ou leia nossos artigos de Data Science.

Matricule-se e comece a estudar com a gente hoje! Conheça outros tópicos abordados durante o curso:

• Funções: UDF
• Funções: criando loop, retornando tabela, alterando funções
• Procedures
• Cursor