O que devemos conhecer sobre SQL/Oracle Parte 15: Manipulando grandes massas de dados

Manipulando dados utilizando subconsultas

Já foi visto nos capítulos anteriores como as subconsultas são poderosas, agora veremos novas utilidades para elas, como criar tabelas com registros, ou inserir registros com base em consultas ou mesmo atualizar registros de uma tabela com subconsultas.

Em alguns casos, desejamos criar tabelas de forma rápida e depois inserir todos ou alguns dos dados de outra tabela, seja apenas para um teste ou um backup temporário em fim a razão não importa, o que importa é que podemos fazer isso de forma bem simples. Suponhamos que nós queremos separar em uma estrutura a parte todos os funcionários de um determinado cargo para tal poderíamos fazer o seguinte:

CREATE TABLE FUNCS_CARGO AS SELECT * FROM FUNCIONARIOS WHERE COD_CARGO = 1;

Bom nossa estrutura temporária foi criada, mas uma nova demanda pede que ela também tenha os funcionários do cargo três, obviamente poderíamos simplesmente remover a tabela e gera-la com uma nova consulta, porem se em nosso exemplo existirem muitos registros essa tarefa pode demorar, então o melhor mesmo seria inserir os registros, o que nesse caso também o faremos com uma subconsulta:

INSERT INTO FUNCS_CARGO (CODIGO, NOME, COD_CARGO, SALARIO) SELECT CODIGO, NOME, COD_CARGO, SALARIO FROM FUNCIONARIOS WHERE COD_CARGO = 3;

Agora para nosso exemplo foi definido que todos os registros da tabela temporária devem ter seus salários e cargos atualizados de acordo com o cargo/salário do funcionário que possui o maior salário, logo para tal poderíamos fazer o seguinte:

UPDATE FUNCS_CARGO SET (COD_CARGO, SALARIO) = (SELECT CARGO, SALARIO FROM( SELECT COD_CARGO CARGO, MAX(SALARIO) SALARIO FROM FUNCIONARIOS GROUP BY COD_CARGO ORDER BY SALARIO DESC ) WHERE ROWNUM = 1);

Resumindo:

• O comando CREATE TABLE AS SELECT, também conhecido como CTAS, usa subconsultas para preencher os novos registros da tabela;
• CTAS também pode ser usada para nomear cada coluna da nova tabela;
• CTAS também pode definir o tipo de dados de cada nova coluna;
• Subconsultas em CTAS devem prover nome para cada coluna, expressões complexas devem ser nomeadas com alias;
• O comando UPDATE pode utilizar uma subconsulta para setar valores para uma ou mais colunas de um ou mais registros;
• Subconsultas em comandos UPDATE podem referenciar o alias da tabela atualizada;
• O comando INSERT pode ser utilizado com uma subconsulta para inserir mais de um registro;

Descrevendo recursos dos INSERTs multitabelas

Os INSERTS multitabelas, como o próprio nome indica, ocorrem para varias tabelas de uma vez, onde a clausula INTO é repetida varias vezes, uma para cada tabela e no final do comando temos uma subconsulta que ira prover os dados para o INSERT. 

O primeiro tipo de INSERT MULTITABLE que vermos é o incondicional, para cada registro recuperado pela subconsulta ocorrera todos os INSERTS listados no comando:

INSERT ALL

INTO FUNC_BKP01(CODIGO, NOME, SALARIO) VALUES(CODIGO, NOME, SALARIO)

INTO FUNC_BKP02(CODIGO, NOME, SALARIO) VALUES(CODIGO, NOME, SALARIO)

INTO FUNC_BKP03(CODIGO, NOME, SALARIO) VALUES(CODIGO, NOME, SALARIO)

SELECT CODIGO, NOME, SALARIO FROM FUNCIONARIOS;

No exemplo acima todas as tabelas listadas receberão os dados da tabela funcionários.

O próximo tipo que veremos é o condicional, onde cada registro recuperado pela subconsulta passa por um teste, quando verdadeiro o comando INSERT é executado:

INSERT FIRST

WHERE (TO_CHAR(DATA_NOTA, ‘RRRR’) = ‘2010’) THEN

INSERT INTO BKPNOTAS2010(CD_NOTA, DT_NOTA, IN_NOTA)VALUES(COD, DTA, INF)

WHERE (TO_CHAR(DATA_NOTA, ‘RRRR’) = ‘2011’) THEN

INSERT INTO BKPNOTAS2011(CD_NOTA, DT_NOTA, IN_NOTA)VALUES(COD, DTA, INF)

ELSE

INSERT INTO BKPNOTAS(CD_NOTA, DT_NOTA, IN_NOTA)VALUES(COD, DTA, INF)

SELECT DTA_NOTA, COD, DTA, INF FROM NOTAS;

No exemplo ocorre uma consulta na tabela de notas, onde sua data é checada para cada registro, onde esses são inseridos de acordo com sua data, quando 2010 em uma tabela, quando 2011 em outra e as que não se enquadrarem nesses dois anos são colocadas na tabela localizada na condição ELSE.

Um detalhe muito importante a ser mencionado quanto as subconsultas utilizadas em INSERTS MULTITABLE é que os ALIAS definidos para as tabelas não são identificados pelos INSERTS, logo se usarmos na subconsulta uma junção onde existam colunas com o mesmo nome deveremos indicar o ALIAS para as colunas e ai sim, nos comandos INSERT utilizar esses apelidos.

Para encerrar veremos uma técnica conhecido por pivot, utilizada para transformar dados dispostos em uma planilha para o formato linha coluna, vamos ao exemplo:

Existe uma determinada planilha que controla o total de funcionários trabalhando, de férias e demitidos, onde sua estrutura traduzida para uma tabela seria a seguinte:

CREATE TABLE PLANILHA_TOT_FUNCS(CARGO VARCHAR2(50), TRABALHANDO NUMBER(4), FERIAS NUMBER(4), DEMITIDOS NUMBER(4));

Agora nossa intenção é inserir os dados dessa planilha em uma nova tabela, que possui a seguinte estrutura:

CREATE TABLE TOT_FUNCS(COD NUMBER(10), CARGO VARCHAR2(50), STATUS VARCHAR2(20), TOTAL NUMBER(4));

Perceba que na planilha temos uma coluna para cada um dos status sumarizados, enquanto na tabela temos apenas uma coluna que indica o status e outra com seu total. Agora para transferir os dados da planilha para a tabela utilizamos da técnica de pivot:

INSERT ALL

WHEN TRABALHANDO IS NOT NULL THEN

INTO TOT_FUNCS(COD, CARGO, STATUS, TOTAL)VALUES(COD, CARGO, 'TRABALHANDO', TRABALHANDO)

WHEN FERIAS IS NOT NULL THEN

INTO TOT_FUNCS(COD, CARGO, STATUS, TOTAL)VALUES(COD, CARGO, 'FERIAS', FERIAS)

WHEN DEMITIDOS IS NOT NULL THEN

INTO TOT_FUNCS(COD, CARGO, STATUS, TOTAL)VALUES(COD, CARGO, 'DEMITIDOS', DEMITIDOS)

SELECT ROWNUM COD, CARGO, TRABALHANDO, FERIAS, DEMITIDOS FROM PLANILHA_TOT_FUNCS;

A lógica é a seguinte, sempre que houver informação em uma das colunas de status da planilha um novo registro é gerado na tabela com o cargo, o status e seu total.

Resumindo:

• Inserts multitabela são uteis para aplicar lógica condicional nos dados a serem inseridos;
• Lógica condicional pode avaliar registros de dados em vários passos, utilizando uma série de validações condicionais oferecendo alternativas estratégicas para adicionar os dados;
• Inserts multitabela oferecem flexibilidade e desempenho eficiente;
• Inserts multitabela podem utilizar operadores condicionais como as clausulas WHEN e ELSE;
• A condição WHEN pode ser usada para avaliar se os dados devem ser inseridos, se sim em que tabelas e quais colunas;
• A clausula ELSE é a ultima alternativa a ser executada no caso de nenhuma das condições WHEN ser atendida;
• Ambos WHEN e ELSE estão associados com suas próprias diretivas do comando INSERT, dependendo das condições que se aplicarem o comando INSERT apropriado será executado;
• Cada condição pode inserir dados de diferentes maneiras em diferentes tabelas;
• O comando INSERT FIRST testa cada condição WHEN e executa o comando da primeira (somente) que retornar true;
• O comando INSERT ALL executa todos os comandos cuja a condição WHEN retornar true;
• A clausula ELSE é executada quando nenhuma das condições WHEN é satisfeita;
• A subconsulta de um insert multitabela determina os dados que serão considerados na logica do insert, podem ser consultas complexas, incluir joins, clausulas group by, operadores set, entre outros;

Utilizando o MERGE

O comando MERGE combina as funcionalidades do INSERT, UPDATE e DELETE, sendo capaz de executar todos esses comandos em um único acesso a base:

MERGE INTO FUNC_BKP01 B

USING FUNCIONARIOS F

ON (B.CODIGO = F.CODIGO)

WHEN MATCHED THEN UPDATE SET

B.SALARIO = F.SALARIO, B.COD_CARGO = F.COD_CARGO

DELETE WHERE B.COD_CARGO = 99

WHEN NOT MATCHED THEN INSERT

(B.CODIGO, B.NOM, B.SALARIO, B.COD_CARGO, B.SEXO)

VALUES

(F.CODIGO, F.NOM, F.SALARIO, F.COD_CARGO, F.SEXO)

WHERE F.SALARIO > 1000;

O comando acima realiza o seguinte, executa uma consulta na tabela de funcionários e compara o código de cada um com os códigos dos funcionários da tabela de backup, quando forem iguais o salario e o cargo são atualizados e os funcionários do cargo 99 são removidos, quando não um novo funcionário é inserido, lembrando que essas alterações acontecem na tabela de backup.

Resumindo:

• O comando MERGE é um dos comandos SQL DML, como o INSERT, UPDATE e DELETE;
• MERGE replica algumas das funcionalidades encontradas no INSERT, UPDATE e DELETE, combinando todos em um único comando;
• MERGE não faz nada de novo em relação os comandos DML padrão, porem ele o faz de maneira mais eficiente, em combinação;
• O comando MERGE possui as clausulas UPDATE CLAUSE e INSERT CLAUSE;
• WHEN MATCHED THEN UPDATE forma o UPDATE CLAUSE;
• WHEN NOT MATCHED THEN INSERT forma o INSERT CLAUSE;
• A clausula DELETE do comando MERGE apenas remove registros que foram inicialmente atualizados no UPDATE CLAUSE e que se encontram atualizados com sucesso, e devem ser encontrados na clausula WHERE do DELETE;

Rastreando alterações de dados sobre determinado período de tempo

No Oracle é possível consultar resultados anteriores as alterações realizadas por um comando DML após um COMMIT. Ou seja, é possível rastrear alterações, o tempo desses registros anteriores é definido por configuração. Veja no quadro abaixo o que é possível:

Comparação dos métodos para recuperar dados
Opção	Exemplo	Tempo para a recuperação	Uso recomendado
Tansaction Control Language (TCL)	COMMIT, ROLLBACK, SAVEPOINT	Sessão atual	Desenvolvimento da aplicação ou código em produção
Operações Flashback	FLASHBACK TABLE, SELECT AS OF, SELECT VERSIONS BETWEEN, etc.	Sessões recentes(minutos/horas, depende da configuração)	Desenvolvimento de aplicações, recuperando alterações recentes para analise e comparação
Recuperação de backup	Utilitários de importação e exportação	Mais antigos (dias/semanas, depende da configuração)	Recuperar dados antigos

Existem três possibilidades quanto ao FLASHBACK:

• Flashback Query (FQ)

Consulta uma tabela como ela existiu anteriormente.

Após inserir alguns registros na tabela de funcionários poderíamos conferir o registro das alterações com o seguinte comando:

SELECT CODIGO, ORA_ROWSCN, SCN, SCN_TO_TIMESTAMP(ORA_ROWSCN) FROM FUNCIONARIOS

O comando acima retornará os registros inseridos, note a pseudocoluna ORA_ROWSCN, SCN quer dizer system change number, é uma numeração gerada de forma automática pelo Oracle a cada alteração na base, e a função SCN_TO_TIMESTAMP nos informa a data de alteração.

Agora se removermos o registro, podemos consulta-los com base em uma nova cláusula, AS OF, indicando o tempo aproximado de exclusão dos registros, que no exemplo será de 90 segundos:

SELECT * FROM FUNCIONARIOS AS OF TIMESTAMP SYSTIMESTAMP – INTERVAL ‘0 0:01:30’ DAY TO SECOND;

No comando acima estamos procurando por registros com tempo igual ao tempo atual menos o tempo corrente, menos o intervalo de 0 dias, 0 horas, 1 minuto e trinta segundos.

O tempo que esses registros permanecem na base depende do período de retenção, geralmente é de minutos, horas e ate um dia, não mais do que isso, para verificar a configuração atual:

SELECT NAME, VALUE FROM V$SYSTEM_PARAMETER WHERE NAME LIKE ‘UNDO%’;

• Flashback Version Query (FVQ)

Para consulta de vários pontos do passado de uma única vez, comparando os registros de vários pontos no tempo.

Nessa abordagem é possível fazer uma analise de múltiplas versões de alterações dentro de um período:

SELECT CODIGO, VERSIONS_STARTSCN, VERSIONS_ENDSCN, VERSIONS_OPERATION FROM FUNCIONARIOS VERSIONS BETWEEN TIMESTAMP MINVALUE AND MAXVALUE;

Repare que no comando acima utilizamos algumas pseudocolunas, VERSIONS_STARTTIME/VERSIONS_STARTSCN (tempo de criação do registro ou SCN da criação), VERSIONS_ENDTIME/VERSIONS_ENDSCN (tempo de expiração SCN da versão do registro), VERSIONS_OPERATION (indica a operação que gerou o registro I, U ou D). Existe aindao VERSION_XID, que identifica a transação que criou o registro.

Em conjunto com o ultimo comando também é possível utilizar a cláusula AS OF, caso utilizada deve sempre vir depois do VERSIONS BETWEEN.

• Flashback Transaction Query (FTQ)

Consulta no dicionário de dados uma variedade de metadados sobre os registros alterados, incluindo informações sobre as transações que causaram essas mudanças.

Esses metadados estão disponíveis em uma visão chamada FLASHBACK_TRANSACTION_QUERY, aqui é possível obter varias informações como o nome da tabela, o nome do dono da tabela, o horário em que a alteração ocorreu, o SQL para restaurar o registro para seu estado anterior a alteração entre outros.

Agora, como exemplo, suponhamos que nossa intenção seja desfazer algumas alterações, para tal vamos realizar um consulta na visão mencionada acima para recuperar os comandos SQL:

SELECT UNDO_SQL FROM FLASHBACK_TRANSACTION_QUERY WHERE XID = (SELECT VERSIONS_XID FROM FUNCIONARIOS VERSIONS BETWEEN TIMESTAMP MINVALUE AND MAXVALUE;

Resumindo:

• A cláusula AS OF do comando SELECT pode consultar dados da tabela que existiram a partir de um determinado tempo no passado, definido por TIMESTAMP ou SCN, com uma limitação do período de retenção (configurável);
• A cláusula AS OF vem após a clausula FROM e antes da clausula WHERE;
• VERSIONS BETWEEN mostra registros que existiram em seus vários estados de alteração dentro do período de tempo, demarca períodos nos termos SCN ou TIMESTAMP e ativa uma serie de pseudocolunas para identificar dados associados ao histórico dos dados;
• A visão do dicionário de dados FLASHBACK_TRANSACTION_QUERY pode ser usada com VERSIONS BETWEEN para identificar metadados associados a transações que causaram alterações nos registros;