如何通过UNION ALL将Oracle多行INSERT子查询改写成长尾词？

本文共计856个文字，预计阅读时间需要4分钟。

完全符合规范，且是Oracle官方推荐的批量插入方式之一。只需包含所有SELECT语句的列数、数据类型（或隐式转换）和顺序，以及INSERT ... SELECT后面接UNION ALL即可正常执行。注意不要使用UNION（会导致去重且可能重开事务，从而出错），必须使用UNION ALL。

INSERT INTO ... SELECT ... UNION ALL 的基本写法

核心结构是把多个SELECT结果集用UNION ALL拼接后，整体作为子查询供INSERT使用。常见错误包括列对不齐、字符串长度超限、日期格式不匹配。

示例（向orders表插入3条测试数据）：

INSERT INTO orders (order_id, customer_name, amount, order_date) SELECT 1001, 'Alice', 299.99, DATE '2024-01-15' FROM DUAL UNION ALL SELECT 1002, 'Bob', 150.50, DATE '2024-01-16' FROM DUAL UNION ALL SELECT 1003, 'Charlie', 88.00, DATE '2024-01-17' FROM DUAL;

• FROM DUAL在每条SELECT中都必须显式写出，漏掉会报ORA-00928: missing SELECT keyword
• 所有SELECT的列数必须严格一致；比如第一行选4列，后面也得是4列，不能有的多有的少
• 字符型字段如果来自字面量（如'Alice'），Oracle按实际长度推断，但若目标列定义为VARCHAR2(10)而你写了'ThisNameIsTooLong'，会直接报ORA-12899

UNION ALL子查询 vs VALUES子句（Oracle 21c+）

Oracle 21c起支持标准SQL的VALUES语法，写起来更紧凑：

INSERT INTO orders (order_id, customer_name, amount, order_date) VALUES (1001, 'Alice', 299.99, DATE '2024-01-15'), (1002, 'Bob', 150.50, DATE '2024-01-16'), (1003, 'Charlie', 88.00, DATE '2024-01-17');

但老版本（如11g/12c/19c）不识别这种写法，会报ORA-00928。所以跨版本兼容时，UNION ALL + SELECT ... FROM DUAL仍是首选。

• 用UNION ALL方式，每条记录都要走一次SELECT ... FROM DUAL，逻辑上略冗余但稳定
• 若子查询里有复杂计算或关联其他表，UNION ALL依然适用；而VALUES只接受字面量或简单表达式
• 性能上二者差异极小，不用刻意优化，优先保证可读性和兼容性

容易被忽略的约束与事务细节

多行插入不是“原子批量”，而是整体当作一条SQL语句执行：要么全部成功，要么全部失败回滚。但如果你在UNION ALL中混入了违反主键、非空或检查约束的数据，Oracle会在运行时报错并中断整个语句，不会部分插入。

• 如果目标表有NOT NULL列，而某条SELECT返回NULL（比如SELECT NULL FROM DUAL），就会触发ORA-01400
• 涉及序列（SEQUENCE.NEXTVAL）时，每个SELECT都会独立取一次值，不会复用；例如SELECT seq.nextval FROM DUAL出现三次，序列会跳3个号
• 没有显式COMMIT时，该语句仍受当前事务控制；别误以为“多行插入”会自动提交

真正麻烦的是嵌套太深或字段类型推导模糊——比如SELECT 1 FROM DUAL UNION ALL SELECT SYSDATE FROM DUAL会因数字和日期无法隐式转换而报错，这种类型冲突往往在开发后期才暴露。