Il modo migliore per inserire inserti multiriga in Oracle?

Sto cercando un buon modo per eseguire inserti multi-riga in un database Oracle 9. Il seguente funziona in MySQL ma non sembra essere supportato in Oracle.

INSERT INTO TMP_DIM_EXCH_RT (EXCH_WH_KEY, EXCH_NAT_KEY, EXCH_DATE, EXCH_RATE, FROM_CURCY_CD, TO_CURCY_CD, EXCH_EFF_DATE, EXCH_EFF_END_DATE, EXCH_LAST_UPDATED_DATE) VALUES (1, 1, '28-AUG-2008', 109.49, 'USD', 'JPY', '28-AUG-2008', '28-AUG-2008', '28-AUG-2008'), (2, 1, '28-AUG-2008', .54, 'USD', 'GBP', '28-AUG-2008', '28-AUG-2008', '28-AUG-2008'), (3, 1, '28-AUG-2008', 1.05, 'USD', 'CAD', '28-AUG-2008', '28-AUG-2008', '28-AUG-2008'), (4, 1, '28-AUG-2008', .68, 'USD', 'EUR', '28-AUG-2008', '28-AUG-2008', '28-AUG-2008'), (5, 1, '28-AUG-2008', 1.16, 'USD', 'AUD', '28-AUG-2008', '28-AUG-2008', '28-AUG-2008'), (6, 1, '28-AUG-2008', 7.81, 'USD', 'HKD', '28-AUG-2008', '28-AUG-2008', '28-AUG-2008'); 

Funziona in Oracle:

 insert into pager (PAG_ID,PAG_PARENT,PAG_NAME,PAG_ACTIVE) select 8000,0,'Multi 8000',1 from dual union all select 8001,0,'Multi 8001',1 from dual 

La cosa da ricordare qui è usare la from dual istruzione.

( fonte )

In Oracle, per inserire più righe nella tabella t con colonne col1, col2 e col3 puoi utilizzare la seguente syntax:

 INSERT ALL INTO t (col1, col2, col3) VALUES ('val1_1', 'val1_2', 'val1_3') INTO t (col1, col2, col3) VALUES ('val2_1', 'val2_2', 'val2_3') INTO t (col1, col2, col3) VALUES ('val3_1', 'val3_2', 'val3_3') . . . SELECT 1 FROM DUAL; 

Usa SQL * Loader. Ci vuole un po ‘di configurazione, ma se questo non è un singolo, ne vale la pena.

Crea tabella

 SQL> create table ldr_test (id number(10) primary key, description varchar2(20)); Table created. SQL> 

Crea CSV

 oracle-2% cat ldr_test.csv 1,Apple 2,Orange 3,Pear oracle-2% 

Crea il file di controllo del caricatore

 oracle-2% cat ldr_test.ctl load data infile 'ldr_test.csv' into table ldr_test fields terminated by "," optionally enclosed by '"' ( id, description ) oracle-2% 

Esegui il comando SQL * Loader

 oracle-2% sqlldr  control=ldr_test.ctl Password: SQL*Loader: Release 9.2.0.5.0 - Production on Wed Sep 3 12:26:46 2008 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Commit point reached - logical record count 3 

Conferma inserto

 SQL> select * from ldr_test; ID DESCRIPTION ---------- -------------------- 1 Apple 2 Orange 3 Pear SQL> 

SQL * Loader ha un sacco di opzioni e può prendere praticamente qualsiasi file di testo come input. È anche ansible allineare i dati nel file di controllo, se lo si desidera.

Ecco una pagina con qualche dettaglio in più -> SQL * Loader

Ogni volta che ho bisogno di fare questo costruisco un semplice blocco PL / SQL con una procedura locale come questa:

 declare procedure ins is (p_exch_wh_key INTEGER, p_exch_nat_key INTEGER, p_exch_date DATE, exch_rate NUMBER, p_from_curcy_cd VARCHAR2, p_to_curcy_cd VARCHAR2, p_exch_eff_date DATE, p_exch_eff_end_date DATE, p_exch_last_updated_date DATE); begin insert into tmp_dim_exch_rt (exch_wh_key, exch_nat_key, exch_date, exch_rate, from_curcy_cd, to_curcy_cd, exch_eff_date, exch_eff_end_date, exch_last_updated_date) values (p_exch_wh_key, p_exch_nat_key, p_exch_date, exch_rate, p_from_curcy_cd, p_to_curcy_cd, p_exch_eff_date, p_exch_eff_end_date, p_exch_last_updated_date); end; begin ins (1, 1, '28-AUG-2008', 109.49, 'USD', 'JPY', '28-AUG-2008', '28-AUG-2008', '28-AUG-2008'), ins (2, 1, '28-AUG-2008', .54, 'USD', 'GBP', '28-AUG-2008', '28-AUG-2008', '28-AUG-2008'), ins (3, 1, '28-AUG-2008', 1.05, 'USD', 'CAD', '28-AUG-2008', '28-AUG-2008', '28-AUG-2008'), ins (4, 1, '28-AUG-2008', .68, 'USD', 'EUR', '28-AUG-2008', '28-AUG-2008', '28-AUG-2008'), ins (5, 1, '28-AUG-2008', 1.16, 'USD', 'AUD', '28-AUG-2008', '28-AUG-2008', '28-AUG-2008'), ins (6, 1, '28-AUG-2008', 7.81, 'USD', 'HKD', '28-AUG-2008', '28-AUG-2008', '28-AUG-2008'); end; / 

Se hai già i valori che vuoi inserire in un’altra tabella, puoi inserire Insert da un’istruzione select.

 INSERT INTO a_table (column_a, column_b) SELECT column_a, column_b FROM b_table; 

In caso contrario, è ansible elencare un gruppo di istruzioni di inserimento a riga singola e inviare diverse query in blocco per risparmiare tempo in qualcosa che funzioni sia in Oracle che in MySQL.

La soluzione di @Espo è buona anche per Oracle e MySQL se i tuoi dati non sono già in una tabella.

È ansible utilizzare anche i cursori, sebbene sia inefficiente. Il seguente post StackOverflow discute l’uso dei cursori:

INSERISCI e AGGIORNA un record utilizzando i cursori in Oracle