sexta-feira, 23 de dezembro de 2011

Insert em várias tabelas

O comando INSERT é amplamente utilizado para inserir dados em tabelas. Com pequenas variações, há duas formas bastante conhecidas:

INSERT INTO Contabil.Vendas(Id, Produto_ID, Qty, Data_Venda)
VALUES (Contabil.Vendas_Seq.Nextval, 1020, 5, Sysdate);

e

INSERT INTO Data_Whse.Vendas
(Produto_ID, Produto_Grupo_ID, Qty, Data_Venda)
SELECT Produto_ID, Produto_Grupo_ID, Qty, trunc(Data_Venda, 'HH') 
  FROM Contabil.Vendas V,
       Contabil.Produtos P
 WHERE V.Produto_ID = P.Produto_ID
   AND Trunc(V.DataVenda) = Trunc(Sysdate)-1;

A primeira forma insere uma única linha na tabela Vendas (esquema Contabil); a segunda forma insere muitas linhas na tabela Vendas (esquema Data_Whse). Tipicamente a primeira seria parte da aplicação, a segunda de um processo diário que copia dados de um esquema para outro (Contabil para Data_Whse).

Mas o Oracle permite uma variação do comando INSERT que poderia ser muito útil numa sitaução semelhante porque permite, em um único comando, inserir dados em várias tabelas. 

INSERT ALL 
  INTO Contabil.Vendas(Id, Produto_ID, Qty, Data_Venda)
       (Vendas_ID, Produto_ID, Qty, Data_Venda)
  INTO Data_Whse.Vendas(Produto_ID, Produto_Grupo_ID, Qty, Data_Venda)
       (Produto_ID, Produto_Grupo_ID, Qty, Trunc(Data_Venda) )
SELECT 20 as Vendas_ID, P.Produto_ID, P.Produto_Grupo_ID, 
       5 as QTY, Sysdate as Data_Venda
  FROM Contabil.Produto P
 WHERE P.Produto_ID = 1;
       
Com este INSERT, duas linhas são inseridas, uma em cada tabela, num único passo. A lista de colunas em cada tabela pode ser diferente e os valores podem ser manipulados, como n caso da coluna Data_Venda: uma tabela receberá o valor Sysdate, outra o valor truncado.

A sintaxe permite inserir em muitas tabelas ao mesmo tempo. O SELECT pode retornar várias linhas. Mas a combinação correta é sempre INSERT ALL/SELECT. 

Há algumas poucas restrições, a mais importante é que o SELECT não pode utilizar uma sequence diretamente. Note que coloquei o valor 20 (fixo) para Vendas_ID. É óbvio que isto funciona porque o SELECT retorna uma única linha neste exemplo.

Mas digamos que 5 itens de cada produto da empresa tenha sido vendido. Neste caso o SELECT teria que retornar muitas linhas e é necessário utilizar a sequence. Eis a solução:


INSERT ALL 
  INTO Contabil.Vendas(Id, Produto_ID, Qty, Data_Venda)
       ( Contabil.Vendas_Seq.Nextval , Produto_ID, Qty, Data_Venda)
  INTO Data_Whse.Vendas(Produto_ID, Produto_Grupo_ID, Qty, Data_Venda)
       (Produto_ID, Produto_Grupo_ID, Qty, Trunc(Data_Venda, 'HH') )
SELECT P.Produto_ID, P.Produto_Grupo_ID, 
       5 as QTY, Sysdate as Data_Venda
  FROM Contabil.Produto P;


Potencialmente, este SELECT reduz a necessidade de escrever código complementar, seja em triggers ou em outras procedures. O dado é inserido a partir de um único ponto em todas as tabelas onde é necessário, facilitando a compreensão e manutenção do sistema. E, claro, trata-se de uma única transação, ou seja, ou insere em todas as tabelas ou não insere em nenhuma. 


Nenhum comentário:

Postar um comentário