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;

Multitable Inserts Incondicionais e Condicionais - Técnica útil para otimizar o código 3

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

Quão útil foi este post ?

Clique em uma estrela para classificar o post

nota média 4.9 / 5. Contagem de votos: 37

Sem votos ! Seja o primeiro a classificar !

Multitable Inserts Incondicionais e Condicionais – Técnica útil para otimizar o código