Manipulating Large Data Sets

Já vimos anteriormente como popular uma tabela no momento da sua criação utilizando subquery, muitas vezes referida como “CREATE TABLE AS SELECT”, ou CTAS. Vamos rever esse tópico novamente mostrando uma sintaxe alternativa também.

A primeira observação a se fazer é que na subquery que irá popular a tabela no momento da sua criação deverá ter um nome para todas as colunas. Vejam no exemplo abaixo.

CREATE TABLE exemplo AS
SELECT coluna1, coluna2, coluna3 + coluna4 AS nome_alias
FROM tabela_origem;

Percebam que a terceira coluna da nossa tabela exemplo criada acima terá um nome dado pelo alias, no caso apenas para o exemplo foi nome_alias. Isso se faz necessário, caso não tivesse colocado um alias, a instrução teria falhado e nenhuma tabela teria sido criado.

Podemos utilizar qualquer tipo de subquery válida, com joins, funções, grupos e etc. Vejamos agora omo seria para criar a mesma tabela usando a sintaxe alternativa que mencionei no início do post.

CREATE TABLE exemplo (coluna1, coluna2, coluna3) AS
SELECT coluna1, coluna2, coluna3+coluna4
FROM tabela_origem;

Como já estudamos antes como utilizar INSERT utilizando subquery, neste post goo.gl/6q1wcV, iremos para o próximo artigo, que sera o UPDATE.

UPDATE and Correlated Subqueries

No mesmo post de subquery vimos algo sobre subquery correlacionada, aquela que depende da query pai em toda interação dela. Iremos utilizar a mesma ideia aqui para realizamos um UPDATE em mais de uma linha dependendo da query pai.

Vejamos um exemplo. Mas para isto, vamos criar uma tabela de teste, que é uma cópia da tabela EMPLOYEES;

CREATE TABLE copy_employees
AS
SELECT *
FROM EMPLOYEES;

Agora vamos dar um UPDATE nesta tabela recém criada com uma subquery correlated.

UPDATE copy_employees ce1
SET(ce1.salary, ce1.commission_pct)
=
(SELECT AVG(ce2.salary), AVG(ce2.commission_pct)
FROM copy_employees ce2
WHERE ce2.deparment_id = ce1.department_id
GROUP BY ce2.department_id
);

O que fizemos nesse UDPDATE? Atualizamos o salário e a sua comissão para a média do departamento, assim todos que pertencem ao mesmo departamento terão a mesma comissão e salário. Agora vamos para algumas observações dessa query.

Logo após a especificação das colunas que serão modificadas em parênteses temos o sinal “=”. Após isso temos a nossa subquery entre parênteses também, fazendo referência a tabela mais externa, no caso do update, característica de uma query correlated.

Multitable INSERTs

O Multitable Insert, faz um INSERT em várias tabelas ao mesmo tempo com ajuda de subquerys. Vejamos como fazer essa inserção de modo condicional ou não. Vejamos primeiro o mais simples, o modo não codicional, que não precisa satisfazer nenhuma condição booleana. Veja a sintaxe primeiro

INSERT ALL
INTO tabela1 VALUES(colunas)
INTO tabela2 VALUES(colunas)
...
subquery;

Algumas observações a se fazer. A palavra ALL é obrigatório em um Multitable INSERTs não condicional. Mas também podemos utilizar na versão condicional, veremos mais a frente. Podemos emitir a cláusula VALUES, ai será adicionado tudo da subquery, sempre obedecendo as regras que já conhecemos de quantidade de colunas, tipos de dados e etc.

Vejamos agora a sintaxe da versão condicional.

INSERT ALL | FIRST
WHEN condicao THEN
INTO tabela1 VALUES(colunas);
WHEN condicao2 THEN
INTO tabela2 VALUES(colunas);

[ELSE
INTO …]
subquery;

Podemos utilizar a palavra ALL que faz com que todas as condições sejam avaliadas para cada linha que é retornada pela subquery. Ou a palavra FIRST, que ao encontrar a primeira condição verdadeira irá ignorar as restantes passando para a próxima linha da subquery. Caso seja omitido sera utilizado o padrão ALL, mas não depomos omitir em uma Multitable INSERT não condicional. Podemos também colocar mais de um INTO por condição, quantos desejarmos. A cláusula ELSE é opcional, ela será executada caso nenhuma condição seja TRUE. As condições avaliadas devem avaliar uma ou mais colunas da subquery.

Vamos fazer alguns testes agora. Dei um TRUNCATE na tabela copy_employees que já utilizamos anteriormente, era uma cópia da tabela employees, então agora tem quase a mesma estrutura mas sem nenhuma linha. Criei uma segunda cópia vazia também emitindo a seguinte query:

CREATE TABLE copy_employees2 AS
SELECT *
FROM employees
WHERE 1=2;

Por essa condição não ser satisfeita nunca, não será copiada nenhuma linha, mas a tabela será criada com toda a sua estrutura. Vamos adicionar então linhas a ambas as tabelas utilizando o MULTITABLE INSERTs.

INSERT ALL
INTO copy_employees
INTO copy_employees2
SELECT * FROM employees;

Com a query acima, iremos copiar todas as linhas da tabela employees para as outras duas tabelas que criamos. Lembrando que a subquery pode conter várias cláusulas como WHERE, GROUPING, JOIN, HAVING e etc. Ocultamos a cláusula VALUES, mas podemos utilizar conforme a nossa necessidade. Vejamos um exemplo agora condicional.

CONDITIONAL MULTITABLE INSERT

CONDITIONAL MULTITABLE INSERT

No exemplo acima pesquisamos apenas os funcionários que foram admitidos na empresa há mais de um ano. Colocamos em uma tabela apenas aqueles que ganham 10000 ou mais, e o restante colocamos na segunda tabela. Poderíamos ter colocado FIRST ao invés de ALL ou então um ELSE ao ter uma segunda condição. Ou ainda omitir o ALL. Todas essas opções dariam o mesmo resultado.

Agora, antes de terminar o post vejamos algumas informações a mais. Se utilizarmos uma ALIAS na tabela da subquery, não podemos referenciar esse alias fora da subquery, seja na condição do WHEN ou na inserção de um valor no INTO. Caso seja necessário utilizar uma ALIAS na tabela, utilize também na coluna, assim poderemos referenciar o alias da coluna. A outra observação que não podemos fazer um Multitable INSERT em uma view, apenas em uma tabela mesmo. Uma outra cautela que devemos ter ao trabalhar com Multitable INSERT é com o uso de sequences. Ao utilizar uma sequencia na subquery será gerado um erro. Mas devemos ter cuidado e nem é apropriado utilizar no resto da query. A sequência irá avançar mesmo se nenhuma condição for avaliada como TRUE. E mesmo que uma linha seja avaliada como TRUE em mais de uma condição, a sequencia só irá avançar apenas uma vez.

Pivot

Pivot é apenas um tipo de operação que podemos fazer com o uso de Multitable INSERT. Com o que vimos hoje podemos transformar dados externos em uma planilha que foi configurada em uma tabela externa para o formato comum de uma tabela de banco de dados, fazendo um “PIVOTs”, ou seja, girando, transformando assim as colunas em linhas, movendo os dados para uma tabela no banco de dados.

Anúncios

Meu nome é Tércio Costa, sou formado em Ciências da Computação pela UFPB, tenho a certificação Oracle SQL Expert e OCP PL/SQL, mantendo um blog reconhecido pela OTN(oraclepress.wordpress.com), no qual também publico artigos técnicos no portal OTN, no portal http://www.profissionaloracle.com.br/gpo e na revista SQL Magazine. Além de tudo isto sou um Oracle ACE Associate por estar sempre contribuindo para a comunidade com um bom nível de expertise.

Marcado com: , , , , , , , , , , , , ,
Publicado em Certificação Oracle, SQL, SQL Expert
4 comentários em “Manipulating Large Data Sets
  1. Muito bem explicado.
    O mais(+) funciona como concatenador?

    Curtir

  2. Dá um exemplo com sequence e pivot

    Curtir

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair /  Alterar )

Foto do Google

Você está comentando utilizando sua conta Google. Sair /  Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair /  Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair /  Alterar )

Conectando a %s

Este site utiliza o Akismet para reduzir spam. Saiba como seus dados em comentários são processados.

Esse Blog é reconhecido pela
Certificações
Sou articulista

Clique para seguir este blog e receber notificações via email de novos posts.

Tércio Costa

Tércio Costa

Meu nome é Tércio Costa, sou formado em Ciências da Computação pela UFPB, tenho a certificação Oracle SQL Expert e OCP PL/SQL, mantendo um blog reconhecido pela OTN(oraclepress.wordpress.com), no qual também publico artigos técnicos no portal OTN, no portal http://www.profissionaloracle.com.br/gpo e na revista SQL Magazine. Além de tudo isto sou um Oracle ACE Associate por estar sempre contribuindo para a comunidade com um bom nível de expertise.

Links Pessoais

Serviços verificados

Visualizar Perfil Completo →

Total de Visualizações da Página
  • 138.077 Visualizações
%d blogueiros gostam disto: