目錄
- 創(chuàng)建示例表
- 無條件的 INSERT ALL 語句
- 有條件的 INSERT ALL 語句
- 有條件的 INSERT FIRST 語句
- 多表插入語句的限制
大家好！我是只談技術(shù)不剪發(fā)的 Tony 老師。
ETL（提取、轉(zhuǎn)換、加載）是指從源系統(tǒng)中提取數(shù)據(jù)并將其放入數(shù)據(jù)倉庫的過程。Oracle 數(shù)據(jù)庫為 ETL 流程提供了豐富的功能，今天我們就給大家介紹一下 Oracle 多表插入語句，也就是INSERT ALL 語句。
創(chuàng)建示例表
我們首先創(chuàng)建一個(gè)源數(shù)據(jù)表和三個(gè)目標(biāo)表：
CREATE TABLE src_table(
id INTEGER NOT NULL PRIMARY KEY,
name VARCHAR2(10) NOT NULL
);
INSERT INTO src_table VALUES (1, '張三');
INSERT INTO src_table VALUES (2, '李四');
INSERT INTO src_table VALUES (3, '王五');
CREATE TABLE tgt_t1 AS
SELECT * FROM src_table WHERE 1=0;
CREATE TABLE tgt_t2 AS
SELECT * FROM src_table WHERE 1=0;
CREATE TABLE tgt_t3 AS
SELECT * FROM src_table WHERE 1=0;
無條件的 INSERT ALL 語句
INSERT ALL 語句可以用于將多行輸入插入一個(gè)或者多個(gè)表中，因此也被稱為多表插入語句。第一種形式的 INSERT ALL 語句是無條件的插入語句，源數(shù)據(jù)中的每一行數(shù)據(jù)都會(huì)被插入到每個(gè)目標(biāo)表中。例如：
INSERT ALL
INTO tgt_t1(id, name) VALUES(id, name)
INTO tgt_t2(id, name) VALUES(id, name)
INTO tgt_t3(id, name) VALUES(id, name)
SELECT * FROM src_table;
SELECT * FROM tgt_t1;
ID|NAME |
--|------|
1|張三 |
2|李四 |
3|王五 |
SELECT * FROM tgt_t2;
ID|NAME |
--|------|
1|張三 |
2|李四 |
3|王五 |
SELECT * FROM tgt_t3;
ID|NAME |
--|------|
1|張三 |
2|李四 |
3|王五 |
執(zhí)行以上多表插入語句之后，三個(gè)目標(biāo)表中都生成了 3 條記錄。
我們也可以多次插入相同的表，實(shí)現(xiàn)一個(gè)插入語句插入多行數(shù)據(jù)的效果。例如：
TRUNCATE TABLE tgt_t1;
INSERT ALL
INTO tgt_t1(id, name) VALUES(4, '趙六')
INTO tgt_t1(id, name) VALUES(5, '孫七')
INTO tgt_t1(id, name) VALUES(6, '周八')
SELECT 1 FROM dual;
SELECT * FROM tgt_t1;
ID|NAME |
--|------|
4|趙六 |
5|孫七 |
6|周八 |
在以上插入語句中，tgt_t1 出現(xiàn)了三次，最終在該表中插入了 3 條記錄。這種語法和其他數(shù)據(jù)庫中的以下多行插入語句效果相同：
-- MySQL、SQL Server、PostgreSQL以及SQLite
INSERT INTO tgt_t1(id, name)
VALUES(4, '趙六'), (5, '孫七'), (6, '周八');
另外，這種無條件的 INSERT ALL 語句還可以實(shí)現(xiàn)列轉(zhuǎn)行（PIVOT）的功能。例如：
CREATE TABLE src_pivot(
id INTEGER NOT NULL PRIMARY KEY,
name1 VARCHAR2(10) NOT NULL,
name2 VARCHAR2(10) NOT NULL,
name3 VARCHAR2(10) NOT NULL
);
INSERT INTO src_pivot VALUES (1, '張三', '李四', '王五');
TRUNCATE TABLE tgt_t1;
INSERT ALL
INTO tgt_t1(id, name) VALUES(id, name1)
INTO tgt_t1(id, name) VALUES(id, name2)
INTO tgt_t1(id, name) VALUES(id, name3)
SELECT * FROM src_pivot;
SELECT * FROM tgt_t1;
ID|NAME |
--|------|
1|張三 |
1|李四 |
1|王五 |
src_pivot 表中包含了 3 個(gè)名字字段，我們通過 INSERT ALL 語句將其轉(zhuǎn)換 3 行記錄。
有條件的 INSERT ALL 語句
第一種形式的 INSERT ALL 語句是有條件的插入語句，可以將滿足不同條件的數(shù)據(jù)插入不同的表中。例如：
TRUNCATE TABLE tgt_t1;
TRUNCATE TABLE tgt_t2;
TRUNCATE TABLE tgt_t3;
INSERT ALL
WHEN id = 1 THEN
INTO tgt_t1(id, name) VALUES(id, name)
WHEN id BETWEEN 1 AND 2 THEN
INTO tgt_t2(id, name) VALUES(id, name)
ELSE
INTO tgt_t3(id, name) VALUES(id, name)
SELECT * FROM src_table;
SELECT * FROM tgt_t1;
ID|NAME |
--|------|
1|張三 |
SELECT * FROM tgt_t2;
ID|NAME |
--|------|
1|張三 |
2|李四 |
SELECT * FROM tgt_t3;
ID|NAME |
--|------|
3|王五 |
tgt_t1 中插入了 1 條數(shù)據(jù)，因?yàn)?id 小于等于 1 的記錄只有 1 個(gè)。tgt_t2 中插入了 2 條數(shù)據(jù)，包括 id 等于 1 的記錄。也就是說，前面的 WHEN 子句不會(huì)影響后續(xù)的條件判斷，每個(gè)條件都會(huì)單獨(dú)進(jìn)行判斷。tgt_t3 中插入了 1 條數(shù)據(jù)，ELSE 分支只會(huì)插入不滿足前面所有條件的數(shù)據(jù)。
📝有條件的多表插入語句最多支持 127 個(gè) WHEN 子句。
有條件的 INSERT FIRST 語句
有條件的 INSERT FIRST 的原理和 CASE 表達(dá)式類似，只會(huì)執(zhí)行第一個(gè)滿足條件的插入語句，然后繼續(xù)處理源數(shù)據(jù)中的其他記錄。例如：
TRUNCATE TABLE tgt_t1;
TRUNCATE TABLE tgt_t2;
TRUNCATE TABLE tgt_t3;
INSERT FIRST
WHEN id = 1 THEN
INTO tgt_t1(id, name) VALUES(id, name)
WHEN id BETWEEN 1 AND 2 THEN
INTO tgt_t2(id, name) VALUES(id, name)
ELSE
INTO tgt_t3(id, name) VALUES(id, name)
SELECT * FROM src_table;
SELECT * FROM tgt_t1;
ID|NAME |
--|------|
1|張三 |
SELECT * FROM tgt_t2;
ID|NAME |
--|------|
2|李四 |
SELECT * FROM tgt_t3;
ID|NAME |
--|------|
3|王五 |
以上語句和上一個(gè)示例的差別在于源數(shù)據(jù)中的每個(gè)記錄只會(huì)插入一次，tgt_t2 中不會(huì)插入 id 等于 1 的數(shù)據(jù)。
多表插入語句的限制
Oracle 多表插入語句存在以下限制：
- 多表插入只能針對表執(zhí)行插入操作，不支持視圖或者物化視圖。
- 多表插入語句不能通過 DB Link 針對遠(yuǎn)程表執(zhí)行插入操作。
- 多表插入語句不能通針對嵌套表執(zhí)行插入操作。
- 所有 INSERT INTO 子句中的字段總數(shù)量不能超過 999 個(gè)。
- 多表插入語句中不能使用序列。多表插入語句被看作是單個(gè)語句，因此只會(huì)產(chǎn)生一個(gè)序列值并且用于所有的數(shù)據(jù)行，這樣會(huì)導(dǎo)致數(shù)據(jù)問題。
- 多表插入語句不能和執(zhí)行計(jì)劃穩(wěn)定性功能一起使用。
- 如果任何目標(biāo)并使用了 PARALLEL 提示，整個(gè)語句都會(huì)被并行化處理。如果沒有目標(biāo)表使用 PARALLEL 提示，只有定義了 PARALLEL 屬性的目標(biāo)表才會(huì)被并行化處理。
- 如果多表插入語句中的任何表是索引組織表，或者定義了位圖索引，都不會(huì)進(jìn)行并行化處理。
到此這篇關(guān)于Oracle 數(shù)據(jù)倉庫 ETL 技術(shù)之多表插入語句的示例詳解的文章就介紹到這了,更多相關(guān)Oracle 多表插入內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
您可能感興趣的文章:- Oracle中多表關(guān)聯(lián)批量插入批量更新與批量刪除操作
- Oracle數(shù)據(jù)倉庫的分層管理器解決方案開發(fā)者網(wǎng)絡(luò)Oracle
- Oracle數(shù)據(jù)庫恢復(fù)教程之resetlogs操作
