Multitable Inserts Incondicionais e Condicionais
Como todos sabem em desenvolvimento um trecho de código pode ser escrito de diversas formas e com os mesmos resultados. Contudo, sempre gosto de utilizar como base em uma implementação o menor números de instrução possíveis para efetuar um processamento. Ao final de cada codificação efetuo a refatoração para que possa eliminar pontos desnecessários ou agregá-los em uma só instrução.
Essa metodologia tende a eliminar a troca de contexto que pode ser prejudicial para a performance do código quando em processamentos de grandes massas de dados ou quando são chamados muitas vezes paralelamente ou sequencialmente. Em resumo, a Oracle utiliza duas engines: uma SQL e outra PL/SQL. Quando existe o cruzamento entre elas, ou seja, quando é processada ora uma, ora outra em uma mesma execução a troca de contexto acontece. Explicarei esse tema com maior detalhes em outro artigo.
Mas indo para o ponto que interessa: Multitable Inserts é uma técnica extremamente útil, mas que poucas vezes é utilizada. Em um trecho de PL/SQL é possível eliminar diversos IF Then ou diversas estruturas de insert’s alinhadas e efetuar todas as ações em um único comando Insert, tornando o código mais limpo e performático
Multitable Inserts podem ser incondicionais e condicionais, o que lhe permite ajustar a suas mais complexas necessidades, como podemos verificar nos exemplos abaixo.
Primeiramente vamos criar as tabelas e dados para execução dos testes.
Cria-se uma tabela base com 4 colunas e 10 registros
CREATE TABLE tabela_base AS
(SELECT ROWNUM my_id,
Substr(dbms_random.String('U', 10), 1, 10) my_name,
Round(dbms_random.Value(1, 7000)) salary,
( CASE
WHEN ROWNUM > 3 THEN SYSDATE - ROWNUM
ELSE NULL
END ) fired_date
FROM dual
CONNECT BY LEVEL <= 10 -- quantidade de registro a serem criados
);Nesta segunda etapa estão sendo criadas 3 tabelas (EMPREGADOS, EMPREGADOS_DEMITIDOS E EMPREGADOS_HISTORICO) com a mesma estrutura da tabela_base, contudo sem quaisquer registros.
CREATE TABLE empregados AS
(SELECT *
FROM tabela_base
WHERE 1 = 2);
CREATE TABLE empregados_demitidos AS
(SELECT *
FROM tabela_base
WHERE 1 = 2);
CREATE TABLE empregados_historico AS
(SELECT *
FROM tabela_base
WHERE 1 = 2); 
Multitable Incondicional
INSERT ALL
INTO empregados (my_id, my_name, salary)
VALUES (id_tb, name_tb, salario_tb)
INTO empregados_demitidos (my_id, my_name, salary, fired_date)
VALUES (id_tb, name_tb, salario_tb, datad_tb)
INTO empregados_historico (my_id, my_name, salary, fired_date)
VALUES (id_tb, name_tb, salario_tb, datad_tb)
SELECT my_id id_tb,
my_name name_tb,
salary salario_tb,
fired_date datad_tb
FROM tabela_base;Para este caso todas as 3 (três) cláusula INTO serão processadas;
Serão inseridas 30 linhas ao todo.
A TABELA_BASE possui 10 registros e como não existem condicionantes serão inseridos os mesmos 10 registros para as tabelas EMPREGADOS, EMPREGADOS_DEMITIDOS e EMPREGADOS_HISTORICO, totalizando 30 linhas.
Observe que para a tabela EMPREGADO – diferentemente das outras 2 tabelas – a coluna FIRED_DATE não é preenchida, ficando, assim, como NULL. Ou seja, podemos eleger quais colunas serão alimentadas, bem como utilizar single-row functions e outras operações para tratar as informações a serem inseridas
Multitable Condicional
INSERT ALL
WHEN datad_tb IS NULL THEN
INTO empregados (my_id, my_name, salary)
VALUES (id_tb, name_tb, salario_tb)
WHEN datad_tb IS NOT NULL THEN
INTO empregados_demitidos (my_id, my_name, salary, fired_date)
VALUES (id_tb, name_tb, salario_tb, datad_tb)
WHEN 1 = 1 THEN
INTO empregados_historico (my_id, my_name, salary, fired_date)
VALUES (id_tb, name_tb, salario_tb, datad_tb)
SELECT my_id id_tb,
my_name name_tb,
salary salario_tb,
fired_date datad_tb
FROM tabela_base;
Para este caso está sendo utilizado lógica condicional para determinar qual ou quais cláusulas INTO serão processadas.
Interessante, aqui, é que mesmo quando satisfeita uma condição as demais serão testadas e caso verdadeira também produzirão a inserção da informação.
ALL não é obrigatório, podendo ser utilizado INSERT WHEN CONDICAO THEN INTO (…);
Neste exemplo serão inseridas 20 linhas. O motivo dá-se, pois existem 3 linhas com o campo FIRED_DATE como NULL e que serão inseridas na tabela EMPREGADOS, pois satisfaz a primeira condição. Outras 7 linhas possuem o campo FIRED_DATE preenchido, logo alimentará a tabela EMPREGADOS_DEMITIDOS, satisfazendo a segunda condicionante. Os outros 10 registros serão inseridos na tabela EMPREGADOS_HISTORICO, pois como foi dito todas as condições são testadas e a última sempre será satisfeita, haja vista que 1 sempre será igual a 1.
Caso exista um ELSE, este só será atendido se nenhuma outra condição for satisfeita.
Mas aí vem a questão: como ao satisfazer uma condição as demais não sejam testadas? A resposta é a utilização do INSERT FIRST, exemplificado abaixo:
INSERT FIRST
WHEN datad_tb IS NULL THEN
INTO empregados (my_id, my_name, salary)
VALUES (id_tb, name_tb, salario_tb)
WHEN datad_tb IS NOT NULL THEN
INTO empregados_demitidos (my_id, my_name, salary, fired_date)
VALUES (id_tb, name_tb, salario_tb, datad_tb)
WHEN 1 = 1 THEN
INTO empregados_historico (my_id, my_name, salary, fired_date)
VALUES (id_tb, name_tb, salario_tb, datad_tb)
SELECT my_id id_tb,
my_name name_tb,
salary salario_tb,
fired_date datad_tb
FROM tabela_base; 
Para este caso será inserido a linha para a primeira condição satisfeita, não sendo testadas as posteriores.
Neste exemplo serão processadas 10 linhas. Onde, 3 linhas atendem a primeira condição, sendo gravadas na tabela EMPREGADOS. As outras 7 linhas serão registradas na tabela EMPREGADOS_DEMITIDOS. Não será inserido nenhum registro na tabela EMPREGADOS_HISTORICO, haja vista que as primeiras condições sempre são atendidas antes de chegar a esta condicionante.
Por fim, Multitable Insert poderá ajudar na eliminação de troca de contextos, passando a responsabilidade para Engine de SQL, tornando, muitas vezes, o código mais limpo e performático.
Referências
- https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_9014.htm
- http://allthingsoracle.com/multi-table-insert-statements-in-oracle/
