<think>我们正在讨论的是在Oracle数据库中如何使用类似于MyBatis中foreach标签的批量插入SQL写法。根据引用[3]和引用[4]的内容，Oracle数据库不支持像MySQL那样使用VALUES后接多个元组的语法，而是需要采用其他方式。
在Oracle中，常见的批量插入方式有两种：
1. 使用`UNION ALL`方式：将多个SELECT语句用UNION ALL连接，然后插入到表中。
2. 使用`INSERT ALL`语句：可以一次性插入多行到多个表（或同一个表），但每行都需要一个INTO子句。
结合MyBatis的foreach标签，我们可以这样实现：
方法一：使用UNION ALL（适用于需要序列生成主键的情况）
引用[3]中给出了一个例子，使用序列生成主键，然后通过SELECT FROM dual UNION ALL的方式构建多行数据。
示例SQL（在MyBatis的mapper文件中）：
```xml
<insert id="insertForeach" parameterType="java.util.List" useGeneratedKeys="false">
INSERT INTO EXCELIMPORTFIELDCFG (EXCELIMPORTFIELDCFGID, DESCRIPTION, LENGTH, ROWNUMBER, TABLEID, TYPE, FIELDNAME)
SELECT EXCELIMPORTFIELDCFGSEQ.NEXTVAL, t.*
FROM (
<foreach collection="fields" item="item" separator="union all">
SELECT
#{item.description,jdbcType=OTHER},
#{item.length,jdbcType=DECIMAL},
#{item.rownumber,jdbcType=DECIMAL},
#{item.tableid,jdbcType=DECIMAL},
#{item.type,jdbcType=OTHER},
#{item.fieldname,jdbcType=OTHER}
FROM dual
</foreach>
) t
</insert>
```
方法二：使用INSERT ALL（适用于不需要返回主键或者主键由其他方式生成的情况）
引用[4]中给出了一个例子，使用INSERT ALL配合多个INTO子句，最后用SELECT 1 FROM dual结束。
示例SQL（在MyBatis的mapper文件中）：
```xml
<insert id="batchInsert" parameterType="java.util.List">
INSERT ALL
<foreach collection="list" item="item" separator=" ">
INTO table_name (column1, column2, ...)
VALUES (#{item.value1}, #{item.value2}, ...)
</foreach>
SELECT 1 FROM dual
</insert>
```
注意：在INSERT ALL方法中，foreach标签的separator属性这里设置为空格，因为每个INTO子句之间不需要逗号，只需要空格分隔。最后必须加上SELECT 1 FROM dual，因为INSERT ALL语句要求后面必须有一个子查询。
但是，INSERT ALL有一个限制：它一次最多只能插入1000行。这是因为Oracle中每个INSERT ALL语句的INTO子句数量不能超过1000。如果需要插入超过1000条，需要分批执行。
另外，如果我们需要在插入时获取自增主键（Oracle中通常使用序列），在方法一中我们可以在SELECT语句中直接使用序列的NEXTVAL，这样每行都会生成一个新的序列值。而在方法二中，我们可以在每个INTO子句的VALUES中使用序列，例如：
```sql
INSERT ALL
INTO table_name (id, col1) VALUES (seq_name.NEXTVAL, 'value1')
INTO table_name (id, col1) VALUES (seq_name.NEXTVAL, 'value2')
...
SELECT 1 FROM dual
```
在MyBatis中，如果使用INSERT ALL并配合序列，可以这样写：
```xml
<insert id="batchInsert" parameterType="java.util.List">
INSERT ALL
<foreach collection="list" item="item" separator=" ">
INTO table_name (id, col1, col2)
VALUES (seq_name.NEXTVAL, #{item.col1}, #{item.col2})
</foreach>
SELECT 1 FROM dual
</insert>
```
但是，这种方法无法返回生成的主键值到Java对象中，因为每次插入多条，而MyBatis的useGeneratedKeys通常只支持单条记录返回主键。如果需要返回主键，可能需要采用其他方式，比如使用<selectKey>标签，但批量插入返回多个主键比较复杂。
总结一下，在Oracle中，使用MyBatis的foreach标签进行批量插入，推荐两种写法：
1. 使用SELECT ... FROM dual UNION ALL方式（可以配合序列生成主键，但无法直接返回主键到Java对象，除非在插入前预先分配序列值）
2. 使用INSERT ALL方式（简单直观，但有1000条限制，且同样难以返回主键）
根据具体需求选择合适的方式。
下面我们给出一个完整的示例，假设我们有一个表T_USER，包含字段ID（主键，使用序列SEQ_USER）、NAME、AGE。
方法一（UNION ALL）的MyBatis Mapper写法：
```xml
<insert id="batchInsertUsers" parameterType="java.util.List">
INSERT INTO T_USER (ID, NAME, AGE)
SELECT SEQ_USER.NEXTVAL, t.*
FROM (
<foreach collection="list" item="user" separator="union all">
SELECT
#{user.name,jdbcType=VARCHAR} AS NAME,
#{user.age,jdbcType=INTEGER} AS AGE
FROM dual
</foreach>
) t
</insert>
```
方法二（INSERT ALL）的MyBatis Mapper写法：
```xml
<insert id="batchInsertUsers" parameterType="java.util.List">
INSERT ALL
<foreach collection="list" item="user" separator=" ">
INTO T_USER (ID, NAME, AGE) VALUES (SEQ_USER.NEXTVAL, #{user.name}, #{user.age})
</foreach>
SELECT 1 FROM dual
</insert>
```
注意：在方法二中，由于每个INTO子句都会调用一次SEQ_USER.NEXTVAL，所以每个插入都会得到一个新的序列值。
另外，如果插入的数据量非常大，建议在程序中进行分批插入（比如每1000条执行一次批量插入），以避免超出数据库的限制或影响性能。
最后，关于返回主键的问题，在Oracle批量插入中，MyBatis没有直接支持返回多个主键。如果必须返回主键，可以考虑在插入前先通过查询序列获取一批主键值（例如：先获取1000个序列值，然后分配给这1000个对象），然后再插入。但这样就需要额外的数据库交互。或者，插入后通过其他方式（如按唯一字段查询）获取生成的主键。
希望以上内容能帮助你解决Oracle中批量插入的问题。</think>### Oracle 批量插入 SQL 的 `foreach` 写法详解
在 Oracle 中使用 MyBatis 的 `foreach` 标签实现批量插入，主要有两种高效写法：
---
#### 方法一：`INSERT ALL` 写法（推荐）
```xml
<insert id="batchInsert" parameterType="java.util.List">
INSERT ALL
<foreach collection="list" item="item" separator=" ">
INTO your_table (id, name, age)
VALUES (
#{item.id},
#{item.name},
#{item.age}
)
</foreach>
SELECT 1 FROM dual
</insert>
```
**关键点**：
1. `INSERT ALL` + `INTO` 子句组合语法
2. `SELECT 1 FROM dual` 是必需的结束符
3. `<foreach>` 的 `separator` 设为空格（无需逗号）
4. 支持单次最多插入 **1000 行**（Oracle限制）[^4]
---
#### 方法二：`UNION ALL` 写法（带序列主键）
```xml
<insert id="batchInsertWithSeq" parameterType="java.util.List">
INSERT INTO your_table (id, name, age)
SELECT your_seq.NEXTVAL, t.* FROM (
<foreach collection="list" item="item" separator="UNION ALL">
SELECT
#{item.name} AS name,
#{item.age} AS age
FROM dual
</foreach>
) t
</insert>
```
**关键点**：
1. 通过 `SELECT ... FROM dual UNION ALL` 构建数据集
2. 在外部查询中调用序列 `your_seq.NEXTVAL` 生成主键
3. 字段别名必须显式声明（如 `AS name`）
4. 适合需要自增主键的场景[^3]
---
### 性能优化建议
1. **分批处理**：每批插入 500-1000 条，避免超限
```java
// Java 调用示例
int batchSize = 500;
for (int i = 0; i < list.size(); i += batchSize) {
List<User> subList = list.subList(i, Math.min(i + batchSize, list.size()));
userMapper.batchInsert(subList);
}
```
2. **事务控制**：在 Service 层开启事务
```java
@Transactional
public void batchInsertUsers(List<User> users) {
userMapper.batchInsert(users);
}
```
3. **禁用约束检查**（超大数据量）
```sql
ALTER TABLE your_table DISABLE CONSTRAINT ALL;
-- 执行批量插入 --
ALTER TABLE your_table ENABLE CONSTRAINT ALL;
```
---
### 两种写法对比
| **特性** | `INSERT ALL` 写法 | `UNION ALL` 写法 |
|------------------|--------------------------------|--------------------------------|
| 语法简洁度 | ★★★★★ | ★★★☆☆ |
| 主键处理 | 需预生成主键 | 支持序列自增 |
| 单次最大行数 | 1000 行 | 无硬限制 |
| 执行效率 | 更高（单语句执行） | 中等 |
| 适用场景 | 常规批量插入 | 需要自增主键的插入 |
---
### 常见问题解决
1. **主键冲突**
- 预先生成主键值（如 UUID）
- 使用序列（如 `SELECT your_seq.NEXTVAL FROM dual`）
2. **超 1000 行限制**
- 必须分批处理
- 动态调整批次大小：
```xml
<foreach collection="list" item="item" index="index" open="INSERT ALL" close="SELECT 1 FROM dual">
<if test="index % 1000 != 0">INTO ...</if>
</foreach>
```
3. **特殊类型处理**
- 指定 JDBC 类型避免转换错误：
```xml
#{item.createTime, jdbcType=TIMESTAMP}
```
---
### 相关问题
1. 如何在 MyBatis 中返回 Oracle 批量插入的自增主键？
2. 如何优化超百万级数据的批量插入性能？
3. Oracle 的 `INSERT ALL` 和 MySQL 的 `VALUES` 批量插入有何本质区别？
4. 批量插入时如何避免触发过多触发器执行？
> 示例表结构参考：
> ```sql
> CREATE TABLE your_table (
> id NUMBER PRIMARY KEY,
> name VARCHAR2(50),
> age NUMBER
> );
> CREATE SEQUENCE your_seq START WITH 1 INCREMENT BY 1;
> ```
扫一扫
766
到【灌水乐园】发言
