asp网站路径,seo网站编辑优化招聘,域名查询ip138,青岛外贸网站设计Oracle 数据库提供了一个多表插入功能#xff0c;也就是 INSERT ALL 语句。这个功能可以方便数据仓库中的 ETL 操作#xff0c;基于不同逻辑将数据插入一个或者多个不同的表中。
PostgreSQL 被称为开源领域的 Oracle#xff0c;虽然没有提供 INSERT ALL 语句#xff0c;但…Oracle 数据库提供了一个多表插入功能也就是 INSERT ALL 语句。这个功能可以方便数据仓库中的 ETL 操作基于不同逻辑将数据插入一个或者多个不同的表中。
PostgreSQL 被称为开源领域的 Oracle虽然没有提供 INSERT ALL 语句但是可以通过递归查询WITH 语句实现类似的功能。
首先创建一个源数据表和三个目标表
CREATE TABLE src_table(id INTEGER NOT NULL PRIMARY KEY,name VARCHAR(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 FALSE;CREATE TABLE tgt_t2 AS
SELECT * FROM src_table WHERE FALSE;CREATE TABLE tgt_t3 AS
SELECT * FROM src_table WHERE FALSE;无条件的多表插入语句
Oracle 中的 INSERT ALL 语句可以将数据输入插入一个或者多个表中因此也被称为多表插入语句。第一种形式的 INSERT ALL 语句是无条件的插入语句源数据中的每一行数据都会被插入到每个目标表中。
-- Oracle 语法
INSERT ALLINTO 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;执行以上多表插入语句之后三个目标表中都生成了 3 条记录。
PostgreSQL 可以使用以下 WITH 语句实现无条件的多表插入语句
WITH s AS (SELECT id, nameFROM src_table
)
,t1 AS (INSERT INTO tgt_t1(id, name)SELECT *FROM s
),
t2 AS (INSERT INTO tgt_t2(id, name)SELECT *FROM s
),
t3 AS (INSERT INTO tgt_t3(id, name)SELECT *FROM s
)
SELECT 1;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|王五 |其中s 代表了数据源t1 实现了 tgt_t1 的数据插入依此类推。最终通过一个 WITH 语句实现了三个表的插入操作。 虽然很多数据库都提供了通用表表达式WITH 语句但是目前只有 PostgreSQL 可以将 DML 语句的结果定义为一个通用表表达式。 有条件的多表插入语句
Oracle 提供的另一种形式的 INSERT ALL 语句是有条件的插入语句可以将满足不同条件的数据插入不同的表中。例如
-- Oracle 语法
INSERT ALLWHEN id 1 THENINTO tgt_t1(id, name) VALUES(id, name)WHEN id BETWEEN 1 AND 2 THENINTO tgt_t2(id, name) VALUES(id, name)ELSEINTO tgt_t3(id, name) VALUES(id, name)
SELECT * FROM src_table;以上语句在 tgt_t1 中插入了 1 条数据因为 id 小于等于 1 的记录只有 1 个。tgt_t2 中插入了 2 条数据包括 id 等于 1 的记录。也就是说前面的 WHEN 子句不会影响后续的条件判断每个条件都会单独进行判断。tgt_t3 中插入了 1 条数据ELSE 分支只会插入不满足前面所有条件的数据。
PostgreSQL 实现以上插入逻辑的方法如下
WITH s AS (SELECT id, nameFROM src_table
)
,t1 AS (INSERT INTO tgt_t1(id, name)SELECT *FROM sWHERE id 1RETURNING id
),
t2 AS (INSERT INTO tgt_t2(id, name)SELECT *FROM sWHERE id BETWEEN 1 AND 2RETURNING id
),
t3 AS (INSERT INTO tgt_t3(id, name)SELECT *FROM sWHERE id NOT IN (SELECT id FROM t1UNION ALL SELECT id FROM t2)
)
SELECT 1;t1 定义中的查询条件限制了 id 小于等于 1 的记录同时使用 RETURNING 子句返回了插入 t1 中的所有 idt2 也采用了类似的处理方式t3 通过子查询插入了不满足 t1 和 t2 插入条件的其他数据。 RETURNING 子句可以返回 DML 语句操作修改的数据它也是 PostgreSQL 专有的功能。 有条件的 INSERT FIRST 语句
Oracle 还提供了有条件的 INSERT FIRST 语句它的原理和 CASE 表达式类似只会执行第一个满足条件的插入语句然后继续处理源数据中的其他记录。例如
INSERT FIRSTWHEN id 1 THENINTO tgt_t1(id, name) VALUES(id, name)WHEN id BETWEEN 1 AND 2 THENINTO tgt_t2(id, name) VALUES(id, name)ELSEINTO tgt_t3(id, name) VALUES(id, name)
SELECT * FROM src_table;以上语句和上一个示例的差别在于源数据中的每个记录只会插入一次tgt_t2 中不会插入 id 等于 1 的数据。
PostgreSQL 模拟以上 INSERT FIRST 语句的方法如下
WITH s AS (SELECT id, nameFROM src_table
)
,t1 AS (INSERT INTO tgt_t1(id, name)SELECT *FROM sWHERE id 1RETURNING id
),
t2 AS (INSERT INTO tgt_t2(id, name)SELECT *FROM sWHERE id BETWEEN 1 AND 2AND id NOT IN (SELECT id FROM t1)RETURNING id
),
t3 AS (INSERT INTO tgt_t3(id, name)SELECT *FROM sWHERE id NOT IN (SELECT id FROM t1UNION ALL SELECT id FROM t2)
)
SELECT 1;t2 的定义中排除的 t1 中的记录t3 的定义中排除的 t1 以及 t2 中的记录以此类推。
