Fala, galera!
Tudo bem?
Continuando os estudos preparatórios p/ realizar o exame 1Z0-071, hoje vou falar um pouco sobre a utilização de algumas funções que ajudam muito no dia dia.
Single Row Functions (Funções de uma unica linha), o que significa que se o retorno do seu select tiver 1000 linhas, as funções serão executadas linha a linha.
O que podemos fazer com esse tipo de função:
Manipular dados, efetuar cálculos, alterações formatações de dados.. e etc;
P/ nos auxiliar nos exercícios criarei a seguinte tabela:
CREATE TABLE vendedores(cod_vendedor INT PRIMARY KEY
,nm_vendedor VARCHAR2(100)
,dt_contratacao DATE
,vl_comissao NUMBER (16,2))
/
INSERT INTO vendedores VALUES(1, 'Ana Paula', '01-jan-2020', 100);
INSERT INTO vendedores VALUES(2, 'Luiza Silva', '01-Fev-2020', 400);
INSERT INTO vendedores VALUES(3, 'Antonio Pereira', '17-jan-2020', 150);
INSERT INTO vendedores VALUES(4, 'Olivia Silva', '15-Mai-2020', 1100);
INSERT INTO vendedores VALUES(5, 'Thais Rodrigues', '16-Mar-2020', 100)
/
COMMIT
/
Vamos começar trabalhar com as funções de manipulação de caracteres.
Função UPPER, transforma p/ todo o conteúdo do campo p/ letras maiúsculas
SELECT nm_vendedor, UPPER(nm_Vendedor) "Upper" FROM vendedores;
Função LOWER, transforma p/ todo o conteúdo do campo p/ letras minusculas.
SELECT nm_vendedor, LOWER(nm_Vendedor) "LOWER" FROM vendedores;
As funções Upper e Lower também podem ser utilizadas no where do select auxiliando na recuperação dos dados, por exemplo, se eu quero recuperar um dos registros utilizando a coluna nm_vendedor no meu where, eu preciso passar os dados exatamente como estão preenchidos na coluna, caso o contrario o registro não será encontrado, ex:
O select abaixo não retornou nenhum registro devido ao parâmetro não estar igual ao dado preenchido na coluna.
SELECT * FROM vendedores WHERE nm_vendedor = 'ana paula';
Nós podemos utilizar uma das funções p/ efetuar a busca do dado, aqui eu converto o campo p/ letras minusculas, e isso me possibilita encontrar o dado, o mesmo pode ser feito com a função Upper.
SELECT * FROM vendedores WHERE LOWER(nm_vendedor)= 'ana paula';
Função Initcap, transforma a primeira letra das literais em maisuculas:
SELECT INITCAP('vendedor: '|| nm_vendedor) "INITCAP" FROM vendedores;
Funcão concat, onde ao invés de utilizar as barras(||) para concatenação, podemos utilizar a função concat, essa função não te da a opção de colocar nada entre as colunas, ela simplesmente junta os dados.
SELECT CONCAT(nm_vendedor, dt_contratacao) FROM vendedores;
Função Length, essa função efetua a contagem de caracteres na coluna, função muito utilizada no dia a dia.
SELECT LENGTH(nm_vendedor), nm_vendedor FROM vendedores;
Função Instr, essa função retorna a posição que encontrou o caractere que foi passado via parâmetro.
SELECT INSTR(nm_vendedor, 'a'), nm_vendedor FROM vendedores;
Função Substr, mais uma função que é bastante utilizada, a mesma recebe dois parâmetros, onde o primeiro parâmetro define o inicio da leitura do campo, e o segundo define a quantidade de caracteres que deve ser retornado, no exemplo abaixo, pedi p/ retornar a partir da primeira posição 5 caracteres.
SELECT SUBSTR(nm_vendedor, 1,5), nm_vendedor FROM vendedores;
Outras maneiras de se utilizar é passando apenas um parâmetro, dessa maneira a função irá retornar todo o restante do campo a partir do parâmetro que foi informado.
SELECT SUBSTR(nm_vendedor, 5), nm_vendedor FROM vendedores;
Podemos também utilizar o parâmetro com numeração negativa.
Quando utilizamos o Substr com parametrização negativa, a função trabalha com os caracteres de trás para frente.
Tome cuidado p/ não ultrapassar a quantidade de caracteres existentes no campo, caso contrario a função não irá retornar dado nenhum.
Podemos passar apenas o parâmetro negativo e desta maneira a função irá trazer apenas a quantidade parametrizada e isso de trás pra frente.
SELECT SUBSTR(nm_vendedor, -3), nm_vendedor FROM vendedores;
Quando utilizamos dois parâmetros, a função retorna os caracteres definidos a partir do primeiro parâmetro e isso de trás pra frente, meio confuso, mas creio que com o print fico mais tranquilo de entender.
SELECT SUBSTR('abcdefg',-5,2) FROM dual; (A partir da 5º posição de trás pra frente, traga 2 caracteres);
Função Replace, usada p/ substituir dados dos campos, no exemplo abaixo, eu parametrizo a função de maneira que substituia toda a letra "A", pela letra "B"
SELECT nm_vendedor, REPLACE(nm_vendedor, 'A', 'B') FROM vendedores;
Função LPAD e RPAD, usamos estas p/ complementação de caracteres, no exemplo abaixo eu deixo o meu campo c/ 20 caracteres, completando a informação já existente com a letra "A"
Utilizando o LPAD os dados são complementados a esquerda (left), no caso do RPAD, vão p/ a direita, (right).
SELECT nm_vendedor
,LPAD(nm_vendedor, 20, 'A')
,RPAD(nm_vendedor, 20, 'A')
FROM vendedores;
Funções numéricas.
As funções Round e Trunc podem ser utilizadas em campos numéricos e de data, mas só vou abordar a aplicação das funções em campos numéricos.
Função round, como o próprio nome já sugere, utilizada p/ fazer arredondamentos de valores, p/ demonstrar a utilização dessa função irei inserir mais dois registros na tabela.
INSERT INTO vendedores VALUES(6, 'Maick Sousa', '15-Mai-2020', 110.57);
INSERT INTO vendedores VALUES(7, 'Rubens Sousa', '16-Mar-2020', 95.42)
/
COMMIT
/
No exemplo abaixo, utilizo a função sem informar nenhum parâmetro, o round sempre irá arredondar os valores arredondando para cima caso a decima seja maior ou igual a 5, se for menor, arredonda p/ baixo.
SELECT nm_vendedor, vl_comissao, ROUND(vl_comissao) "Round" FROM vendedores WHERE cod_vendedor IN(6,7);
Utilizando com parâmetro, o mesmo indica o valor que será utilizado para efetuar o arredondamento, ou seja, no exemplo abaixo 1 casa após a virgula da esquerda p/ a direita.
SELECT nm_vendedor
,vl_comissao
,ROUND(vl_comissao,1) "Round"
FROM vendedores WHERE cod_vendedor IN(6,7);
No exemplo abaixo, passei 2 como parâmetro para a função, ou seja, usa como fator p/ arredondamento a segunda casa após a virgula.
SELECT nm_vendedor
,vl_comissao
,ROUND(vl_comissao,2) "Round"
FROM vendedores WHERE cod_vendedor IN(6,7);
O parâmetro também pode ser usado com numero negativo, o que fará a inversão do fator de arredondamento, ou seja, no exemplo abaixo, será o primeiro campo após a virgula da direita para a esquerda.
SELECT ROUND(110.57,-1) "Round(-)" FROM dual
Ajustando o parametro p/ -2
SELECT ROUND(110.57,-2) "Round(-)" FROM dual
Função Trunc, essa função realiza uma operação de truncamento em valores numéricos com base em uma precisão determinada, que assim como a função round também é opcional.
A diferença do Trunc p/ o Round é que no caso do Trunc não há arredondamentos a função simplesmente quebra o valor, sem arredondar nem pra cima e nem pra baixo. Alguns exemplos abaixo.
SELECT nm_vendedor
,vl_comissao
,TRUNC(vl_comissao) "TRUNC"
FROM vendedores WHERE cod_vendedor IN(6,7);
Utilizando o parâmetro de precisão:
SELECT nm_vendedor
,vl_comissao
,TRUNC(vl_comissao,1) "TRUNC"
FROM vendedores WHERE cod_vendedor IN(6,7);
No caso do valor de precisão ser negativo, a função fará a leitura da direita p/ a esquerda, no caso abaixo, o numero 9 será o valor de precisão.
SELECT TRUNC(159.35,-1) FROM dual
Função MOD, essa função traz a sobra de uma divisão.
No exemplo abaixo, se divimos 1000/3, temos uma sobra de 1.
SELECT MOD(1000,3) FROM dual;
