Insert condicional em várias tabelas

No post anterior mostrei com usar o INSERT para inserir em várias tabelas. Utilizado daquela maneira, cada linha é inserida em todas as tabelas incluídas no comando. Mas outra variação de INSERT permite utilizar expressões lógicas para indicar em qual tabela uma linha deve ser inserida. Sua sintaxe é:

INSERT[ALL]
WHEN
THEN INTO VALUES (lista de valores)

WHEN
THEN INTO VALUES (lista de valores)

...
[ELSE INTO VALUES (lista de valores)]
SELECT .... ;

Cada condição lógica controla se uma linha específica deve ser inserida na respectiva tabela. a palavra chave ALL indica se todas as condições lógicas devem ser avaliadas, permitindo que a linha seja inserida em mais de uma tabela (sempre que a condição for verdadeira). Se ALL for omitida, a linha é inserida na primeira tabela cuja respectiva condição lógica for verdadeira. A claúsula ELSE é opcional permite inserir a linha na tabela caso todas as condições lógicas resultem falso. Uma consulta SELECT é obrigatória e pode ser simples ou tão complexa quanto necessário.

Por exemplo, digamos que há três tabelas VENDAS, VENDAS_2011  e VENDAS_HIST. a primeira armazena vendas realizadas em 2012, a segunda vendas de 2011 e a terceira vendas realizadas em qualquer outro ano. As três tabelas tem exatamente a mesma estrutura. O INSERT abaixo utiliza as condições lógicas para definir em qual tabela cada linha gerada pelo SELECT deve ser inserida.

INSERT 
  WHEN EXTRACT(YEAR FROM data_venda) = 2012 THEN
       INTO vendas VALUES (vendas_id, data_venda, valor)
  WHEN EXTRACT(YEAR FROM data_venda) = 2011 THEN
       INTO vendas_2011 VALUES (vendas_id, data_venda, valor*0.95)
  ELSE INTO vendas_hist VALUES (vendas_id, data_venda, valor*0.9)
SELECT level vendas_id,
       TRUNC(sysdate - dbms_random.value(0, 500)) data_venda,
       TRUNC(dbms_random.value(1000, 2000), 2) valor
  FROM dual
  CONNECT BY level <= 100; 

O SELECT resulta em 100 linhas representando vendas realizadas entre hoje e 500 dias atrás e valor entre 1000 e 2000. As condições lógicas no INSERT controlam em qual tabela cada linha deve ser inserida. Nesta caso, como cada linha deve ser inserida em apenas uma tabela, a palavra chave ALL é desnecessária. Note que o valor de vendas é multiplicado por diferentes fatores em cada INSERT, apenas para demonstrar que é possível alterar os valores. Também seria possível utilizar tabelas com diferentes colunas. 

Há algumas restrições quanto ao SELECT, a mais relevante é quanto ao uso de sequences. O comando abaixo retorna um erro:


INSERT 
  WHEN EXTRACT(YEAR FROM data_venda) = 2012 THEN
       INTO vendas VALUES (vendas_id, data_venda, valor)
  WHEN EXTRACT(YEAR FROM data_venda) = 2011 THEN
       INTO vendas_2011 VALUES (vendas_id, data_venda, valor*0.95)
  ELSE INTO vendas_hist VALUES (vendas_id, data_venda, valor*0.9)
SELECT vendas_seq.nextval vendas_id,
       TRUNC(sysdate - dbms_random.value(0, 500)) data_venda,
       TRUNC(dbms_random.value(1000, 2000), 2) valor
  FROM dual
  CONNECT BY level <= 100;


SQL Error: ORA-02287: sequence number not allowed here
02287. 00000 -  "sequence number not allowed here"
*Cause:    The specified sequence number (CURRVAL or NEXTVAL) is inappropriate
           here in the statement.
*Action:   Remove the sequence number.

Entre as soluções possíveis, uma das simples é criar uma função para encapsular a sequence e depois usar a função no comando SELECT.


CREATE OR REPLACE
  FUNCTION new_vendas_id
    RETURN NUMBER
  IS
    retVal NUMBER;
  BEGIN
    SELECT vendas_seq.nextval INTO retVal FROM dual;
    RETURN retVal;
  END;



INSERT 
  WHEN EXTRACT(YEAR FROM data_venda) = 2012 THEN
       INTO vendas VALUES (vendas_id, data_venda, valor)
  WHEN EXTRACT(YEAR FROM data_venda) = 2011 THEN
       INTO vendas_2011 VALUES (vendas_id, data_venda, valor*0.95)
  ELSE INTO vendas_hist VALUES (vendas_id, data_venda, valor*0.9)
SELECT new_vendas_id vendas_id,
       TRUNC(sysdate - dbms_random.value(0, 500)) data_venda,
       TRUNC(dbms_random.value(1000, 2000), 2) valor
  FROM dual
  CONNECT BY level <= 100;



Esta solução para contornar o erro ORA-02287 pode ser útil em várias outras situações não relacionadas ao INSERT.