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.

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.

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

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

Fala, galera!

Tudo bem?

Comecei meus estudos preparatórios p/ realizar o exame 1Z0-071 da Oracle, que certifica como OCA os aprovados.

Bom hoje eu vou falar um pouco sobre SQL mais especificamente sobre o comando Select.

Vou começar com um pouco da história de como surgiu o SQL.

A linguagem SQL surgiu em meados da década 70 e foi desenvolvida pela IBM Corporation, o sucesso da linguagem foi tão grande, que foi necessário ser padronizado(ANSI), como já citei em um outro artigo que escrevi, nos dias de hoje podemos utilizar o SQL em diversos BD's, cada um podendo ter pequenas variações, no entanto não afetando na padronização global da linguagem.

Enfim, vamos ao que interessa.

Como podemos usar o SQL?

>Consultar dados

>Inserir, deletar e atualizar linhas de uma tabela (DML) - Data Manipulation Language

>Criar, alterar, renomear e deletar objetos de banco de dados (DDL)- Data Definition Language

>Controlar o acesso dos usuários aos objetos do banco - (DCL) - Data Control Language

>Garantir a integridade de dados - Commit e Rollback

Hoje demonstrarei com alguns exemplos práticos, como podemos efetuar consultas de dados utilizando o comando Select. O Select é um dos comandos mais utilizados e também é considerado como um comando (DML).

P/ auxiliar nos estudos, criarei uma tabela, e incluirei alguns registros.

CREATE TABLE empregados(cod_emp   INT PRIMARY KEY

                       ,nm_emp    VARCHAR2(100)

                       ,nm_depto  VARCHAR2(100)

                       ,vlr_sal   NUMBER)

/                       

INSERT INTO empregados VALUES(1, 'Leonardo Silva', 'Almoxarifado', 1500);

INSERT INTO empregados VALUES(2, 'Fabio Brustelo', 'Vendas', 3500);

INSERT INTO empregados VALUES(3, 'Tiago Gomes', 'Compras', 2500);

INSERT INTO empregados VALUES(4, 'Pedro Franchi', 'RH', 1500);

INSERT INTO empregados VALUES(5, 'Pedro Penido', 'Marketing', 4500)

/

COMMIT

/

Comando Select.

Select = Selecione 

* = Todas as colunas

empregados = Tabela

SELECT *

  FROM empregados;

Especificando colunas

SELECT nm_emp, vlr_sal
   FROM empregados;

Utilizando operações aritméticas, contas de somar, subtrair, dividir, tudo pode ser feito, pode ser usada a regra de precedência, é só lembrar da época da escola, das operações matemáticas que vai ajudar.

SELECT nm_emp, vlr_sal, vlr_sal+100
   FROM empregados;

Operação aritmética utilizando a regra de precedência, repare na ultima coluna, o valor fica diferente devido ao uso dos parenteses. 

SELECT nm_emp Nome_Empregado, vlr_sal, 12* vlr_sal+200, 12*(vlr_sal+200)
   FROM empregados;

Alterando os nomes das colunas (Alias), você pode precisar montar um relatório direto do grid de dados, dessa maneira podem ser colocados títulos nas colunas.

SELECT nm_emp nome_empregado, vlr_sal valor_salario
   FROM empregados;

Caso seja necessário trabalhar o titulo c/ letras maiúsculas e minusculas e espaços.

SELECT nm_emp AS "Nome Empregado", vlr_sal as "Valor Salario"
   FROM empregados;

Concatenação, significa juntar, ligar, no caso abaixo, uniremos um valor literal + a coluna nm_emp.

SELECT 'Empregado: '|| nm_emp AS "Nome Empregado", vlr_sal as "Valor Salario" 
   FROM empregados;

Distinct

Pra falar sobre esse comando, vou inserir mais um registro na tabela de exemplo.

INSERT INTO empregados VALUES(6, 'Bernardo Brustelo', 'Vendas', 4500)

/

COMMIT

/

Reparem no select abaixo, onde eu trago apenas a coluna nm_depto, onde uma das informações se repete.

SELECT nm_depto
  FROM empregados;

Com o uso do comando Distinct, eliminamos as informações duplicadas.

Bom, por hoje é só, e até a próxima.

Abs,

Leonardo SIlva.