sexta-feira, 2 de abril de 2021

Estudando para o exame de certificação Oracle Database SQL | 1Z0-071 - Comando Merge


Fala, galera. Tudo bem?

Hoje vou falar um pouco sobre o comando MERGE.

O MERGE é um comando DML que combina as funções de insert, update e delete. O uso do MERGE possibilita a combinação dos comandos citados de maneira mais eficiente do que fazê-los separadamente, o que o torna a opção preferencial.

Vamos ao exemplo, para trazer o funcionamento do comando irei criar as tabelas abaixo/ inserir alguns dados pra gente poder trabalhar.


CREATE TABLE TAB01(ID NUMBER
                                         ,NOME VARCHAR2(10))
/                    

                    

CREATE TABLE TAB02(ID NUMBER
                                         ,NOME VARCHAR2(10))
/


INSERT INTO TAB01 VALUES(1, 'JOAO');
INSERT INTO TAB01 VALUES(2, 'MANOEL');
INSERT INTO TAB01 VALUES(3, 'ANTONIO');
INSERT INTO TAB01 VALUES(4, 'LUAN');
INSERT INTO TAB01 VALUES(5, 'ROGERIO');
--

INSERT INTO TAB02 VALUES(1, 'MARCOS');
INSERT INTO TAB02 VALUES(2, 'MARCELO');
INSERT INTO TAB02 VALUES(7, 'ANTONIO');
INSERT INTO TAB02 VALUES(8, 'LUAN');
INSERT INTO TAB02 VALUES(9, 'ROGERIO');
INSERT INTO TAB02 VALUES(10, 'DAVID');

COMMIT;
/

Query na tab01/ 02 p/ evidenciar os registros antes da execução do comando.










Obs: Note que os id's 1 e 2 existem em ambas as tabelas porém com nomes diferentes.


Sintaxe do comando (Retirada do Site Oficial da Oracle) https://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9016.htm#SQLRF01606





















Utilização do comando:



MERGE INTO TAB01

USING TAB02

  ON (TAB01.ID = TAB02.ID)

WHEN MATCHED THEN 

  UPDATE SET TAB01.NOME = TAB02.NOME

  DELETE WHERE TAB01.NOME = 'MARCOS'

WHEN NOT MATCHED THEN 

  INSERT VALUES(TAB02.ID, TAB02.NOME)

WHERE TAB02.ID >1;



O que foi feito no comando acima, fizemos um merge das linhas da tab02 na tab01, respeitando a condição definida no where onde só traria as linhas da tabela dois que tivessem o ID > 1 ou seja, é possível filtrar os registros, fizemos um update caso o mesmo id fosse encontrado nas duas tabelas, caso não fosse o registro seria inserido, também deletamos um dos registros na tab01, resultado:













Espero que gostem e até a próxima.


Leonardo Silva.




sexta-feira, 16 de outubro de 2020

Estudando para o exame de certificação Oracle Database SQL | 1Z0-071 - Utilizando funções de agrupamento/ Agrupando dados.


Fala, galera. Tudo bem?

Hoje vou falar um pouco sobre algumas funções de agrupamento de dados, funções essas que são extremamente utilizadas no dia a dia do programador Oracle.

Separei algumas delas, as quais julguei mais importante pra gente falar um pouco.


AVG => Retorna o valor médio de um conjunto de valores
COUNT => Retorna a quantidade de linhas de uma consulta
MAX => Retorna o valor máximo de (X)
MIN => Retorna o valor mínimo de (X)
SUM => Retorna a soma de (X)


Evidencia da tabela que utilizarei no decorrer dos estudos.










AVG

Vamos começar falando da função AVG, essa função retorna o valor médio da coluna selecionada, note que o banco efetua o agrupamento e devolve a média, no caso do exemplo abaixo, eu estou trazendo a média do campo vl_salario, a função efetua a soma de todos os valores e após isso divide pela quantidade de registros.


SELECT AVG(vl_salario) media
  FROM funcionarios;









COUNT

Essa função faz a conta da quantidade de registros em uma determinada consulta.
Abaixo segue exemplo da utilização da função p/ verificar a quantidade de registros existentes na tabela.

SELECT COUNT(1)
  FROM funcionarios;










MAX e MIN

Como os próprios nomes já sugerem, utilizamos as funções acima p/ retornar um valor máximo, ou mínimo de um conjunto de dados. Abaixo, efetuarei uma consulta p/ retornar o maior/ menor salários
cadastrados na tabela:


SELECT MAX(vl_salario) maior_salr
               ,MIN(vl_salario) menor_salr
   FROM funcionarios  










O MIN e o MAX também podem ser utilizados com caracteres, onde o banco fará a busca utilizando o fator de ordem alfabética p/ recuperar o Mínimo e o Máximo. P/ campos de datas, vai considerar a menor(MIN) e a maior como (MAX).


SELECT MAX(dt_contratacao) min_dt
      ,MIN(dt_contratacao) max_dt
      ,MIN(nm_func) min_nm
      ,MAX(nm_func) max_nm
      ,MIN(nm_departamento) Min_dept
      ,MAX(nm_departamento) MAX_dept
  FROM funcionarios;











SUM

Efetua a somatória de uma determinada coluna, no caso abaixo, estou efetuando a soma da coluna salario, de todos os registros da tabela.

SELECT SUM(vl_salario) soma
  FROM funcionarios 




Agora vamos falar um pouco sobre como agrupar esses dados, falaremos da clausula GROUP BY e da clausula HAVING.

GROUP BY

Utilizamos essa clausula quando se faz necessário o agrupamento de dados de colunas em comum.
Suponhamos que eu queira saber a quantidade de funcionários contratados por ano, pra isso utilizamos o GROUP BY, onde combinamos o agrupamento + as funções de grupo, exemplo:

No SELECT abaixo, eu formatei o campo de data p/ mostrar apenas o ano, efetuei o agrupamento por essa coluna, e inseri a função COUNT para contar os registros.

SELECT to_char(dt_contratacao, 'rrrr') ano, 
               COUNT(1) qt_contratados
  FROM funcionarios 
 GROUP BY to_char(dt_contratacao, 'rrrr')






HAVING

Essa clausula é utilizada quando existe a necessidade de filtrarmos o resultado dos agrupamentos, a mesma é inserida após o GROUP BY, no exemplo abaixo, selecionarei apenas os registros onde o numero de contratados é maior do que 1.

SELECT to_char(dt_contratacao, 'rrrr') ano, 
       COUNT(1) qt_contratados
  FROM funcionarios 
 GROUP BY to_char(dt_contratacao, 'rrrr')
 HAVING COUNT(1) >1



A clausula HAVING pode ser utilizada com outras funções de grupo, abaixo mais um exemplo de utilização do GROUP BY + HAVING, agora utilizando a função AVG. No SELECT abaixo, estou trazendo a media salarial dos funcionários contratados no ano, e filtrando apenas as medias maiores do que 5000.

SELECT to_char(dt_contratacao, 'rrrr') ano, 
               AVG(vl_salario) media
  FROM funcionarios 
 GROUP BY to_char(dt_contratacao, 'rrrr')
 HAVING AVG(vl_salario)> 5000


















Espero que tenham gostado, 

Até a próxima.


Leonardo Silva.

sábado, 3 de outubro de 2020

Estudando para o exame de certificação Oracle Database SQL | 1Z0-071 - Utilizando funções de conversão e condicionais. Cont.

Fala, galera. Tudo bem?

Continuando a falar um pouco sobre funções de conversão e funções condicionais.

Hoje vamos falar de algumas funções muito utilizadas por nós desenvolvedores Oracle.


NVL
NVL2
COALESCE
NULLIF
CASE
DECODE


NVL

Muitas vezes temos colunas no banco de dados onde não existe nenhuma informação gravada, ou seja o campo está nulo, porém no resultado do nosso select é necessário a exibição de algum dado, pra isso podemos usar o NVL, exemplos abaixo:

Retornando todos os dados da tabela

Select * from Aluno;

Agora utilizando o NVL na coluna cod_turma, para que o comando funcione corretamente, o dado inserido na função pra retornar quando o campo estiver nulo, deve ser do mesmo tipo de dado da tabela, caso possível conversões também podem ser efetuadas. No exemplo abaixo, eu estou retornando "0" no campo cod_turma, sempre que o campo for nulo.


select cod_aluno, 
          nm_aluno, 
          nvl(cod_turma, o) cod_turma
from aluno;















Mais um exemplo da utilização do NVL, agora passando um campo varchar como parâmetro, repare que você pode escrever algo p/ retornar na sua query caso a coluna esteja nula.

select nm_instrumento
         ,tp_instrumento
        ,nvl(tp_instrumento, 'S/ Tipo cadastrado') tp_instrumentoNVL 
from instr_musicais;




NVL2

No caso do NVL2 é possível trabalharmos com duas opções de retorno, passamos a coluna da tabela como parâmetro, caso a informação não seja nula, retorna a primeira literal escrita, caso seja nulo, retorna a segunda, no caso exemplo abaixo, se não for nulo retorna "S", e se for retorna "N", muito parecido com a estrutura de um IF, se você conhece lógica de programação, vai entender o que eu estou querendo dizer.

select cod_aluno,
          nm_aluno,
          cod_turma,
         nvl2(cod_turma,'S','N') "Tem turma?"
  from aluno;







COALESCE

Função COALESCE, é utilizando quando existe a necessidade de verificar se mais de uma coluna da tabela está nula, nessa função é possível utilizarmos N parâmetros, lembrando que todos os parâmetros devem ser do mesmo tipo de dado.

Exemplos:

No exemplo abaixo, estou testando dois parâmetros, caso estejam nulos, retorna a literal

select coalesce(null, null, 'TesteCoalesce') Teste from dual;






Nessa função você pode incluir quantas colunas forem necessárias, caso todas estejam nulas, a
função retornara o ultimo parâmetro informado, no exemplo abaixo inclui varios parametros nulos p/ deixar bem claro os testes da função.

select coalesce(null, null, null, 
                         null, null, null, 
                         null, 'TesteCoalesce') Teste 
  from dual;




Note no exemplo abaixo que se um dos dados não estiver nulo, a função retornará o primeiro valor que encontrar.

select coalesce('C', null, null, 
                'B', null, null, 
                'A', 'TesteCoalesce') Teste 
  from dual;




















NULLIF

A função NULLIF retornará null caso os parâmetros informados estejam iguais, sempre que forem diferentes, a função retornará o primeiro parâmetro informado, exemplo:


select Nullif('A', 'A') Teste1
      ,Nullif('B', 'C') Teste2 
      ,Nullif('C', 'D') Teste3 
      ,Nullif('D', 'D') Teste4 
  from dual;




















CASE

Se trata de uma função onde é possível trabalharmos com estruturas de decisões, como se incluíssemos uma estrutura de IF dentro do select, no exemplo abaixo, inclui uma estrutura de decisão p/ manipular os dados da coluna vl_salario, dependendo da data de contratação, adicionei um valor de aumento de salario.


select nm_func
      ,dt_contratacao
      ,vl_salario
      ,case
         when dt_contratacao between to_date('01/01/2017','dd/mm/rrrr') 
                                 and to_date('31/12/2017','dd/mm/rrrr') then
           vl_salario * 1.10
         when dt_contratacao between to_date('01/01/2018','dd/mm/rrrr') 
                                 and to_date('31/12/2018','dd/mm/rrrr') then
           vl_salario * 1.05
         when dt_contratacao between to_date('01/01/2019','dd/mm/rrrr') 
                                 and to_date('31/12/2019','dd/mm/rrrr') then
           vl_salario * 1.03
         else 
           vl_salario
        end vl_ajuste_salario
  from funcionarios;






























Estrutura do CASE - WHEN - THEN - ELSE - END.
CASE - Abre a estrutura.
WHEN - Quando (Condição)
THEN - Então (O que deve ser feito, caso entre na condição)
ELSE - Exceção das condições anteriores.
END - Fecha a estrutura



DECODE

Também se trata de uma função onde é possível se trabalhar c/ estruturas de decisões, porém no meu ponto de vista p/ atender necessidades menos complexas, as quais eu utilizaria o CASE p/ resolver.

No exemplo abaixo eu estou manipulando o campo tp_instrumento e retornando um numero no meu select de acordo com a literal recuperada da coluna.

select nm_instrumento,
       tp_instrumento, 
       decode(tp_instrumento, 
              'Corda',     1,
              'Percussão', 2,
              'Digital',   3,
              'Aerofone',  4,
              'Sopro',     5,
              6) teste_decode
  from instr_musicais








No caso do DECODE, o ultimo cadastro na função será a sua exceção, no exemplo acima foi o número 6.


Utilizando o case p/ efetuar o mesmo tratamento de coluna do ultimo exemplo.


select nm_instrumento,
       tp_instrumento, 
       case
         when tp_instrumento = 'Corda' then
           1
         when tp_instrumento = 'Percussão' then
           2
         when tp_instrumento = 'Digital' then
           3
         when tp_instrumento = 'Aerofone' then 
           4
         when tp_instrumento = 'Sopro' then
           5
       else
         6
       end        teste_decode
  from instr_musicais







































Não creio que para o exemplo em questão exista codificação melhor ou pior em relação ao uso do CASE ou do DECODE, o que vai determinar qual será utilizado será sua demanda/ o que for mais confortável pra você.

Um abraço e espero que tenham gostado.





Leonardo Silva


sábado, 26 de setembro de 2020

Parâmetros de Sessão - Alterando NLS da conexão

Fala, galera.

Hoje vamos falar um pouco sobre o NLS no Oracle.

Significado da sigla = National Language Suporte, como a própria descrição já sugere, nesses parâmetros é possível se efetuar as configurações de região e linguagem, abaixo vou compartilhar alguns testes que fiz onde faço alterações desses parâmetros apenas p/ a sessão em que estou conectado.

Consultando os parametros:


select * from v$nls_parameters;




 










Note nas configurações que a linguagem está setada p/ o americano, o que faz com que o resultado da query abaixo seja em inglês.

SELECT to_char(SYSDATE, 'Day') FROM dual;








Agora vou alterar o parâmetro  de idioma da minha sessão p/ português e vou executar a mesma query, vejam o resultado.

ALTER SESSION SET NLS_LANGUAGE = 'BRAZILIAN PORTUGUESE';






Também é possível alterarmos a formatação da data, pra essa sessão a configuração está no padrão americano, note o que acontece se eu tentar executar a query abaixo:

SELECT * FROM funcionarios WHERE dt_contratacao = '04/12/2017'










Pra essa query anterior funcionar, eu teria que usar uma formatação com um to_char, ou to_date, mas esse não é o intuito dos testes.

Agora vou alterar a configuração do formato da data pra minha sessão.

ALTER SESSION SET NLS_DATE_FORMAT = 'DD/MM/RRRR';

Executei a mesma query após a alteração do parâmetro de formatação de data.



Espero que tenham gostado.


Abs, 


Leonardo Silva.

Estudando para o exame de certificação Oracle Database SQL | 1Z0-071 - Utilizando funções de conversão e condicionais.

Fala, galera. Tudo bem?

Hoje vou falar um pouco sobre funções de conversão e funções condicionais.

Pode acontecer de precisarmos converter um tipo de dado p/ realizar algum procedimento, as vezes converter um formato de uma data, ou a formatação de um numero, lembre-se que essas conversões acontecem a nível de select, a nível da consulta que estivermos fazendo, e não a nível de banco de dados.

Pra ajudar na demonstração das funções, criarei a tabela abaixo.

CREATE TABLE funcionarios(id_func INT PRIMARY KEY
                                                  ,nm_func VARCHAR2(50)
                                                  ,dt_contratacao  DATE
                                                  ,vl_salario  NUMBER(16,2)
                                                  ,nm_departamento VARCHAR2(20))
/


INSERT INTO funcionarios VALUES(1, 'Antonio Pereira'  ,'04/12/2017', 5500, 'MANUTENCAO');
INSERT INTO funcionarios VALUES(2, 'Luiza Silva'      ,'10/02/2019', 7500, 'TECNOLOGIA');
INSERT INTO funcionarios VALUES(3, 'Ana Pereira'      ,'15/04/2010', 8000, 'LOGISTICA');
INSERT INTO funcionarios VALUES(4, 'Vinicius Pereira' ,'05/01/2018', 4300, 'MARKETING');
INSERT INTO funcionarios VALUES(5, 'Sergio Bavini'    ,'04/12/2017', 4000, 'RECURSOS HUMANOS')

/

COMMIT
/


Principais funções utilizadas p/ conversão de dados.

TO_NUMER() Converte caracteres p/ numérico, sendo necessário que o dado a ser convertido seja um número.
TO_DATE() Converte números ou caracteres p/ data.
TO_CHAR() Converte dados p/ caractere.

Conversão Implícita.

Acontece quando a literal é passada no mesmo tipo de dado da coluna da tabela, dessa maneira o próprio banco de dados faz  a conversão. 

Exemplos:

SELECT * FROM funcionarios
WHERE vl_salario > 5000









Outro exemplo utilizando um campo de data.

SELECT * FROM funcionarios
 WHERE dt_contratacao > '01/01/2019'


Conversão explicita, é quando na codificação você já informa pro banco de dados o tipo de dado que será utilizado p/ fazer a consulta. 

Exemplo:

SELECT *
  FROM funcionarios
 WHERE vl_salario > to_number(7000);






Vamos agora falar um pouco sobre a função to_char, essa função é necessária quando precisamos recuperar uma informação do banco e alterar sua formatação, alguns exemplos abaixo:

Formatações p/ campos de data.

YYYY - Ano em números
RRRR - Ano em números
YEAR - Ano escrito
MM - Dois dígitos para o mês
MONTH - Mês por escrito
MON - Três letras para abreviação do mês
DY - 3 letras para abreviação do dia da semana
DAY - Nome do dia da semana
DD - Dia

O primeiro campo no select está em sua formatação original, da maneira em que está gravado no banco de dados, as demais colunas demonstram as formatações citadas acima, essas formatações são Case Sensitive, ou seja, dependendo da maneira em que são escritas, o retorno virá em letras maiúsculas ou minusculas.

SELECT dt_contratacao
      ,to_char(dt_contratacao, 'YYYY') ano_numyy
      ,to_char(dt_contratacao, 'rrrr') ano_numrr
      ,to_char(dt_contratacao, 'Year') ano_escrito
      ,to_char(dt_contratacao, 'MM') mes2
      ,to_char(dt_contratacao, 'Month') mes_escrito
      ,to_char(dt_contratacao, 'MON') mes_3abrev
      ,to_char(dt_contratacao, 'DY') dd_3abrev
      ,to_char(dt_contratacao, 'DAY') Dia_sem
      ,to_char(dt_contratacao, 'DD') dia
  FROM funcionarios











Mais formatações possiveis p/ o to_char.

AM    - AM ou PM
CC    - Século
D     - Dia da semana (1-7)
DDD   - Dia do ano
DY    - Dia da semana abreviado ('SUN')
Ddspth - Dia por extenso
FM    - Tira os blanks ou Zeros da esquerda
HH    - Hora do dia (0-12)
HH24  - Hora do dia (0-24)
MI    - Minutos da Hora
MM    - Mês com 2 dígitos
PM    - AM ou PM
RR    - Ano com 2 dígitos - especial
RRRR  - Ano com 4 dígitos
SS    - Segundos do minuto(0 - 59)
SSSSS - Segundos do dia
W     - Semana do Mês
WW    - Semana do Ano
YY    - Ano com 2 dígitos

Mais alguns exemplos de formatação de datas:

SELECT f.dt_contratacao
      ,TO_CHAR(f.dt_contratacao, 'fmDD Month YYYY')AS dt_contratacao       
      ,'Hoje é dia: ' || TO_CHAR(TRUNC(SYSDATE), 'fmDD Month YYYY') || ' ' ||
       'E são exatamente ' || TO_CHAR(SYSDATE, 'hh24:mi:ss') || ' ' ||
       'da' || TO_CHAR(SYSDATE, 'Am')  AS dia_corrente
  FROM funcionarios f;



Agora uns exemplos de formatações de números.

Formatações possíveis.

9 determina a largura de exibição 9999 = 1234
0 exibe zeros a esquerda 09999 = 01234
$ Exibe o dólar = $1234
L Exibe a moeda L9999 = R$1234
D Caractere decimal 9999D = 1234,
. Mostra uma virgula ou ponto 9999.99  = 1234.00
G Separado de grupos 999G9 = 123.4
, Ponto / virgula na posição 999,9 = 123,4
MI Sinal negativo à direita* 9999MI = 1234-*
PR põe valores negativos entre <>* 9999PR = <1234>*
EEEE informa no padrão cientifico 9999EEEE = 1E+03
U Símbolo monetário
S Informa o sinal S9999 = +1234
B Troca valores zero por vazio** B9999 = ‘ ‘ ** 

Alguns exemplos:

SELECT vl_salario
      ,to_char(vl_salario, '999G999G999G999D99')  vl_salario
      ,to_char(vl_salario, 'S999G999G999G999D99') vl_salarioSinal
      ,to_char(vl_salario, 'L999G999G999G999D99') vl_salariomoeda           
      ,to_char(-1234.22, 'B999G999G999G999D99PR') vl_negativo
      ,to_char(-1234.22, 'B999G999G999G999D99MI') vl_sinnegativo     
  FROM funcionarios f




Bom, espero que tenham gostado.


Abs.


Leonardo Silva.

quinta-feira, 17 de setembro de 2020

Estudando para o exame de certificação Oracle Database SQL | 1Z0-071 - Utilizando funções simples(Single Row Functions) Cont.

Fala, galera. Tudo bem?

Continuando com o assunto de funções simples(Single Row Functions), resolvi quebrar o artigo em dois, pra deixar as funções de datas separadas, funções essas que são extremamente utilizadas por nós que trabalhamos com Oracle.

Função SYSDATE

Função pra retornar a data e hora atual do servidor.

SELECT SYSDATE FROM dual;








Função CURRENT_DATE

Mais uma função pra retornar data, a diferença do current_date p/ o sysdate é que a função current_date retorna a data e hora de acordo com o fuso horário da sessão do usuário.

SELECT current_date FROM dual;


Somando dias em datas.

Toda data quando adicionamos ou subtraimos um número, c/ isso alteramos a data em quantidade de dias, exemplo:

SELECT SYSDATE
               ,SYSDATE + 5 
               ,SYSDATE -30
    FROM dual;


Podemos também subtrair (data - data)
Obs: Menos de 100 dias p/ o natal...rs

SELECT SYSDATE, (TRUNC(SYSDATE) - TO_DATE('25/12/2020')) FROM DUAL;








Outra coisa legal que da pra fazer com as funções de datas é, suponhamos que você precise saber  quantas semanas um determinado funcionário está trabalhando na sua empresa, utilizando a data de contratação, conseguimos calcular a quantidade de semanas:

Dt_Contratacao = 04/12/2017
Conta = Data atual - Dt_Contratacao/ 7 (Quantidade de dias que tem uma semana)

145 semanas é o resultado.

SELECT (TRUNC(SYSDATE) - to_date('04/12/2017','dd/mm/rrrr') )/7 FROM DUAL;


Agora mais algumas coisas que da pra fazer com as datas/ horas.

Caso seja necessário somar uma quantidade de minutos, ou menos segundos a uma data, basta fazer as contas e aplicar, exemplos.

Adicionando 5 minutos a uma data/ hora, basta colocarmos 5/1440 que é a quantidade de minutos que temos em um dia (1440/60) = 24.

SELECT TO_CHAR(SYSDATE,'DD/MM/YYYY HH24:MI:SS') Hora_Atual,
               TO_CHAR(SYSDATE + 5/1440, 'DD/MM/YYYY HH24:MI:SS') "Hora+5min"
  FROM DUAL;









Função ADD_MONTHS, muito utilizada, adiciona ou subtrai meses a uma data desejada.

SELECT TRUNC(SYSDATE) dt_atual
              ,add_months(TRUNC(SYSDATE),2) "+2 Meses"
             ,add_months(TRUNC(SYSDATE),-2) "-2 Meses"
  FROM DUAL;









Função MONTHS_BETWEEN, traz a quantidade de meses entre uma data e outra.

SELECT TRUNC(SYSDATE) dt_atual
              ,months_between(to_Date('17/01/2021','dd/mm/rrrr'), TRUNC(SYSDATE)) meses
 FROM DUAL;








Função NEXT_DAY, traz pra você o próximo dia da semana, conforme parâmetro especificado.

SELECT NEXT_DAY(SYSDATE,'SEGUNDA') "NEXT DAY"
  FROM DUAL;








Obs: a função acima só respeita o português, devido as variáveis de ambiente do banco ao qual eu estou trabalhando estarem configuradas p/ o português, caso contrario, parâmetros deverão ser informados em inglês. 

Função Last_Day, retorna o ultimo dia do mês.

SELECT LAST_DAY(SYSDATE) FROM DUAL;








Outros exemplos, o round arredondou a data p/ cima, e o trunc p/ baixo.

SELECT ROUND(SYSDATE, 'MONTH')
             ,TRUNC(SYSDATE, 'MONTH')
   FROM DUAL;









Ultimo ponto, a nível de curiosidade.

Trabalhando com as mascaras YYYY x RRRR

Quando temos a data armazenada com o ano com 4 dígitos não há diferença entre as mascaras.

Você só vai notar diferença entre essas mascaras, caso esteja utilizando datas com formação de ano com 2 dígitos, exemplos abaixo:

SELECT to_date('01-jan-77','dd/mm/rrrr') FROM dual








Agora utilizando a mesma data, porém com a mascara YYYY

SELECT to_date('01-jan-77','dd/mm/yyyy') FROM dual


Por que isso ocorre?

Se a data tem apenas dois dígitos e utilizamos a formatação YYYY, o banco sempre vai assumir os dois dígitos iniciais 00, conforme exemplo abaixo.

SELECT to_date('01-jan-01','dd/mm/yyyy') dt1
               ,to_date('01-jan-49','dd/mm/yyyy') dt2
               ,to_date('01-jan-50','dd/mm/yyyy') dt3
               ,to_date('01-jan-99','dd/mm/yyyy') dt4
   FROM dual











Utilizando o RRRR, o banco aplica a seguinte regra, se o ano estiver entre 00 e 49 assume o século atual, se estiver entre 50 e 99, assume o século anterior;

SELECT to_date('01-jan-01','dd/mm/rrrr') dt1
               ,to_date('01-jan-49','dd/mm/rrrr') dt2
               ,to_date('01-jan-50','dd/mm/rrrr') dt3
               ,to_date('01-jan-99','dd/mm/rrrr') dt4
   FROM dual











Bom, espero que tenham gostado.

Um abraço.

Leonardo Silva.


terça-feira, 15 de setembro de 2020

Estudando para o exame de certificação Oracle Database SQL | 1Z0-071 - Utilizando funções simples(Single Row Functions)

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;









Estudando para o exame de certificação Oracle Database SQL | 1Z0-071 - Comando Merge

Fala, galera. Tudo bem? Hoje vou falar um pouco sobre o comando MERGE. O MERGE é um comando DML que combina as funções de insert, update e d...