Best way to do multi-row insert in Oracle?
我正在寻找一种在Oracle 9数据库中执行多行插入的好方法。 以下内容适用于MySQL，但在Oracle中似乎不受支持。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
| 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'); |
在Oracle中，要使用列col1，col2和col3在表t中插入多行，可以使用以下语法：
1
2
3
4
5
6
7
8
| 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; |
这适用于Oracle：
1
2
3
| 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 |
这里要记住的是使用from dual语句。
(资源)
使用SQL * Loader。它需要一点设置，但如果这不是一次性的，它是值得的。
创建表
1
2
3
| SQL> CREATE TABLE ldr_test (id NUMBER(10) PRIMARY KEY, description varchar2(20));
TABLE created.
SQL> |
创建CSV
1
2
3
4
5
| oracle-2% cat ldr_test.csv
1,Apple
2,Orange
3,Pear
oracle-2% |
创建加载程序控制文件
1
2
3
4
5
6
7
8
9
| 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% |
运行SQL * Loader命令
1
2
3
4
5
6
7
8
| oracle-2% sqlldr <username> 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 |
确认插入
1
2
3
4
5
6
7
8
9
| SQL> SELECT * FROM ldr_test;
ID DESCRIPTION
---------- --------------------
1 Apple
2 Orange
3 Pear
SQL> |
SQL * Loader有很多选项，几乎可以将任何文本文件作为输入。如果需要，您甚至可以在控制文件中内联数据。
这是一个包含更多详细信息的页面 - > SQL * Loader
每当我需要这样做时，我用这样的本地过程构建一个简单的PL / SQL块：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
| 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;
/ |
如果您已经在另一个表中插入了值，则可以从select语句中插入。
1
| INSERT INTO a_table (column_a, column_b) SELECT column_a, column_b FROM b_table; |
否则，您可以列出一堆单行插入语句并批量提交多个查询，以节省在Oracle和MySQL中工作的时间。
@ Espo的解决方案也是一个很好的解决方案，如果您的数据不在表格中，它将在Oracle和MySQL中都有效。
以下是Oracle中插入多行的非常有用的分步指南：
https://livesql.oracle.com/apex/livesql/file/content_BM1LJQ87M5CNIOKPOWPV6ZGR3.html
最后一步：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
| INSERT ALL
/* Everyone is a person, so insert all rows into people */
WHEN 1=1 THEN
INTO people (person_id, given_name, family_name, title)
VALUES (id, given_name, family_name, title)
/* Only people with an admission date are patients */
WHEN admission_date IS NOT NULL THEN
INTO patients (patient_id, last_admission_date)
VALUES (id, admission_date)
/* Only people with a hired date are staff */
WHEN hired_date IS NOT NULL THEN
INTO staff (staff_id, hired_date)
VALUES (id, hired_date)
WITH names AS (
SELECT 4 id, 'Ruth' given_name, 'Fox' family_name, 'Mrs' title,
NULL hired_date, DATE'2009-12-31' admission_date
FROM dual UNION ALL
SELECT 5 id, 'Isabelle' given_name, 'Squirrel' family_name, 'Miss' title ,
NULL hired_date, DATE'2014-01-01' admission_date
FROM dual UNION ALL
SELECT 6 id, 'Justin' given_name, 'Frog' family_name, 'Master' title,
NULL hired_date, DATE'2015-04-22' admission_date
FROM dual UNION ALL
SELECT 7 id, 'Lisa' given_name, 'Owl' family_name, 'Dr' title,
DATE'2015-01-01' hired_date, NULL admission_date
FROM dual
)
SELECT * FROM names |
如果要插入一些随机值，可以使用循环插入。
1
2
3
4
5
6
| BEGIN
FOR x IN 1 .. 1000 LOOP
INSERT INTO MULTI_INSERT_DEMO (ID, NAME)
SELECT x, 'anyName' FROM dual;
END LOOP;
END; |
也可以使用游标，尽管它效率低。
以下stackoverflow文章讨论了游标的用法：
使用oracle中的游标INSERT和UPDATE记录
|
