A instrução Oracle INSERT ALL serve para inserir várias linhas em uma tabela ou várias tabelas. O Oracle fornece dois tipos de instruções de inserção em várias tabelas: incondicional e condicional.
Instrução incondicional: INSERT ALL
Inserindo várias linhas em uma tabela
Para inserir várias linhas em uma tabela, use a seguinte instrução Oracle INSERT ALL:
INSERT ALL
INTO table_name(col1,col2,col3) VALUES(val1,val2, val3)
INTO table_name(col1,col2,col3) VALUES(val4,val5, val6)
INTO table_name(col1,col2,col3) VALUES(val7,val8, val9)
Subquery;
Nesta instrução, cada expressão de valor val1, val2 ou val3 deve se referir a uma coluna retornada pela lista de seleção da subconsulta. Se você deseja usar valores literais em vez dos valores retornados pela subconsulta, use a seguinte subconsulta:
SELECT * FROM dual;
O exemplo a seguir demonstra como inserir várias linhas em uma tabela. Primeiro criamos uma tabela carros:
CREATE TABLE carro (
modelo VARCHAR2(100) PRIMARY KEY,
cor VARCHAR2(100) NOT NULL
);
Segundo, use a instrução Oracle INSERT ALL para inserir linhas na tabela de carro:
INSERT ALL
INTO carro(modelo, cor)
VALUES ('VW','Branco')
INTO carro(modelo, cor)
VALUES ('Mitsubishi','Preto')
INTO carro(modelo, cor)
VALUES ('Toyota','Cinza')
SELECT 1 FROM dual;
Terceiro, consulte os dados da tabela carro para verificar a inserção:
select * from carro
| MODELO | COR |
|---|---|
| VW | Branco |
| Mitsubishi | Preto |
| Toyota | Cinza |
Como você pode ver, três linhas foram inseridas na tabela de carro com êxito, conforme o esperado.
Inserir várias linhas em várias tabelas
Para inserir várias linhas em uma tabela, use a seguinte instrução Oracle INSERT ALL:
Além de inserir várias linhas em uma tabela, você pode usar a instrução INSERT ALL para inserir várias linhas em várias tabelas, conforme mostrado na seguinte sintaxe:
INSERT ALL
INTO table_name1(col1,col2,col3) VALUES(val1,val2, val3)
INTO table_name2(col1,col2,col3) VALUES(val4,val5, val6)
INTO table_name3(col1,col2,col3) VALUES(val7,val8, val9)
Subquery;
O insert abaixo funciona da mesma forma do insert acima, a palavra chave VALUES pode ser omitida no caso das colunas que vierem da subconsulta são compatíveis na quantidade de coluna e tipos de dados. A subconsulta retorna duas colunas sendo do tipo de dados INT e VARCHAR2 e as tabelas tab1, tab2 e tab3 possuem duas colunas ID e NOME sendo do tipo de dados INT e VARCHAR2, dessa forma pode se omitir o VALUES, do contrário se viesse mais colunas da subconsulta do que as da tabelas a serem inseridas, então deverá especificar o nome das colunas e seus valores (da forma como foi feita na consulta acima).
insert all
into tab1
into tab2
into tab3
select employee_id id, first_name nome
from hr.employees fetch first 10 rows only;
select count(*) from tab1
union
select count(*) from tab2
union
select count(*) from tab3
Instrução Condicional Oracle INSERT ALL
A instrução condicional de inserção em várias tabelas permite inserir linhas em tabelas com base nas condições especificadas. A seguir, é mostrada a sintaxe da instrução condicional:
INSERT [ ALL | FIRST ]
WHEN condition1 THEN
INTO table_1 (column_list ) VALUES (value_list)
WHEN condition2 THEN
INTO table_2(column_list ) VALUES (value_list)
ELSE
INTO table_3(column_list ) VALUES (value_list)
Subquery
Se você especificar a palavra-chave ALL, o Oracle avaliará cada condição nas cláusulas WHEN. Se uma condição for avaliada como verdadeira, o Oracle executará a cláusula INTO correspondente. No entanto, quando você especifica a palavra-chave FIRST, para cada linha retornada pela subconsulta, o Oracle avalia cada condição na cláusula WHEN de cima para baixo. Se o Oracle encontrar uma condição avaliada como verdadeira, ele executará a cláusula INTO correspondente e ignorará as cláusulas WHEN subsequentes da linha especificada.
Obs: uma única instrução condicional de inserção em várias tabelas pode ter até 127 cláusulas WHEN.
Exemplo condicional do Oracle INSERT ALL
As seguintes instruções CREATE TABLE criam três tabelas: small_orders, medium_orders e big_orders com as mesmas estruturas:
CREATE TABLE small_orders (
order_id NUMBER(12) NOT NULL,
customer_id NUMBER(6) NOT NULL,
amount NUMBER(8,2)
);
CREATE TABLE medium_orders AS
SELECT *
FROM small_orders;
CREATE TABLE big_orders AS
SELECT *
FROM small_orders;
A seguinte instrução Oracle INSERT ALL condicional a seguir insere dados do pedido nas três tabelas small_orders, medium_orders e big_orders com base nos valores dos pedidos:
INSERT ALL
WHEN amount < 10000 THEN
INTO small_orders
WHEN amount >= 10000
AND amount < = 30000 THEN
INTO medium_orders
WHEN amount > 30000 THEN
INTO big_orders
SELECT order_id,
customer_id,
(quantity * unit_price) amount
FROM orders
INNER JOIN order_items
USING(order_id);
Você pode obter o mesmo resultado usando a cláusula ELSE no lugar da inserção nas tabelas big_orders da seguinte maneira:
INSERT ALL
WHEN amount < 10000 THEN
INTO small_orders
WHEN amount >= 10000 AND amount <= 30000 THEN
INTO medium_orders
ELSE
INTO big_orders
SELECT order_id,
customer_id,
(quantity * unit_price) amount
FROM orders
INNER JOIN order_items USING(order_id);
Exemplo condicional do Oracle INSERT FIRST
Considere o seguinte exemplo:
INSERT FIRST
WHEN amount > 30000 THEN
INTO big_orders
WHEN amount >= 10000 THEN
INTO medium_orders
WHEN amount > 0 THEN
INTO small_orders
SELECT order_id,
customer_id,
(quantity * unit_price) amount
FROM orders
INNER JOIN order_items
USING(order_id);
Esta declaração não fará sentido com um INSERT ALL porque os pedidos cujo valor superior a 30.000 teriam acabado sendo inseridos nas três tabelas. No entanto, com INSERT FIRST, para cada linha retornada pela subconsulta, o Oracle avaliará cada condição WHEN de cima para baixo:
- Primeiro, se o valor do pedido for maior que 30.000, o Oracle inserirá os dados na big_orders e ignorará as condições WHEN subsequentes.
- Em seguida, se a primeira avaliação for falsa e a quantidade for maior ou igual a 10.000, o Oracle inserirá os dados na tabela medium_orders e também ignorará a avaliação da terceira cláusula WHEN.
- Por fim, se as duas primeiras condições WHEN avaliarem false, o Oracle executará a cláusula INTO na cláusula ELSE, que insere dados na tabela small_orders.
Restrições do Oracle INSERT ALL
A instrução de inserção em várias tabelas do Oracle está sujeita às seguintes restrições principais:
- Pode ser usado para inserir dados apenas em tabelas, não funciona em views ou views materializadas.
- Não pode ser usado para inserir dados em tabelas remotas.
- O número de colunas em todas as cláusulas INSERT INTO não deve exceder 999.
- A subconsulta da instrução de inserção em várias tabelas não pode usar uma sequence.
Referência
Multitable Inserts
The ultimate guide to Oracle INSERT ALL Statments
desenvoracle.home.blog 