Oracle 資料倉庫 ETL 技術之多表插入陳述句

大家好！我是只談技術不剪發的 Tony 老師，

ETL（提取、轉換、加載）是指從源系統中提取資料并將其放入資料倉庫的程序，Oracle 資料庫為 ETL 流程提供了豐富的功能，今天我們就給大家介紹一下 Oracle 多表插入陳述句，也就是 INSERT ALL 陳述句，

如果覺得文章有用，歡迎評論📝、點贊👍、推薦🎁

創建示例表

我們首先創建一個源資料表和三個目標表：

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 陳述句可以用于將多行輸入插入一個或者多個表中，因此也被稱為多表插入陳述句，第一種形式的 INSERT ALL 陳述句是無條件的插入陳述句，源資料中的每一行資料都會被插入到每個目標表中，例如：

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|王五  |

執行以上多表插入陳述句之后，三個目標表中都生成了 3 條記錄，

我們也可以多次插入相同的表，實作一個插入陳述句插入多行資料的效果，例如：

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 出現了三次，最終在該表中插入了 3 條記錄，這種語法和其他資料庫中的以下多行插入陳述句效果相同：

-- MySQL、SQL Server、PostgreSQL以及SQLite
INSERT INTO tgt_t1(id, name)
VALUES(4, '趙六'), (5, '孫七'), (6, '周八');

另外，這種無條件的 INSERT ALL 陳述句還可以實作列轉行（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 個名字欄位，我們通過 INSERT ALL 陳述句將其轉換 3 行記錄，

有條件的 INSERT ALL 陳述句

第一種形式的 INSERT ALL 陳述句是有條件的插入陳述句，可以將滿足不同條件的資料插入不同的表中，例如：

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 條資料，因為 id 小于等于 1 的記錄只有 1 個，tgt_t2 中插入了 2 條資料，包括 id 等于 1 的記錄，也就是說，前面的 WHEN 子句不會影響后續的條件判斷，每個條件都會單獨進行判斷，tgt_t3 中插入了 1 條資料，ELSE 分支只會插入不滿足前面所有條件的資料，

📝有條件的多表插入陳述句最多支持 127 個 WHEN 子句，

有條件的 INSERT FIRST 陳述句

有條件的 INSERT FIRST 的原理和 CASE 運算式類似，只會執行第一個滿足條件的插入陳述句，然后繼續處理源資料中的其他記錄，例如：

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|王五  |

以上陳述句和上一個示例的差別在于源資料中的每個記錄只會插入一次，tgt_t2 中不會插入 id 等于 1 的資料，

多表插入陳述句的限制

Oracle 多表插入陳述句存在以下限制：

• 多表插入只能針對表執行插入操作，不支持視圖或者物化視圖，
• 多表插入陳述句不能通過 DB Link 針對遠程表執行插入操作，
• 多表插入陳述句不能通針對嵌套表執行插入操作，
• 所有 INSERT INTO 子句中的欄位總數量不能超過 999 個，
• 多表插入陳述句中不能使用序列，多表插入陳述句被看作是單個陳述句，因此只會產生一個序列值并且用于所有的資料行，這樣會導致資料問題，
• 多表插入陳述句不能和執行計劃穩定性功能一起使用，
• 如果任何目標并使用了 PARALLEL 提示，整個陳述句都會被并行化處理，如果沒有目標表使用 PARALLEL 提示，只有定義了 PARALLEL 屬性的目標表才會被并行化處理，
• 如果多表插入陳述句中的任何表是索引組織表，或者定義了位圖索引，都不會進行并行化處理，