<think>我们讨论在Oracle中使用MERGE语句进行批量更新，并获取更新行数的方法。
由于用户要求不使用OutputParameter，我们可以通过以下方式在Service层实现：
方案一：使用MERGE语句并利用SQL%ROWCOUNT返回总更新行数
在Oracle中，可以在PL/SQL块中执行MERGE，然后通过SQL%ROWCOUNT获取更新的行数，并将其赋值给一个OUT参数。
但是，用户要求不用OutputParameter，所以我们考虑在Service层通过其他方式获取更新行数。
替代方案：在MERGE语句执行后，使用RETURNING子句（但注意，MERGE语句本身不支持RETURNING子句，所以此路不通）
因此，我们可能需要使用另一种思路：在同一个事务中，在MERGE之前记录需要更新的记录的当前状态，然后执行MERGE，再通过查询对比来得到更新行数。但这种方法效率较低，且在高并发下可能不准确。
所以，我们回到使用PL/SQL块，但避免在Java中使用OutputParameter对象，而是通过MyBatis的Map参数来接收OUT参数。
具体实现如下：
1. Mapper接口定义：
```java
void mergeUpdate(@Param("list") List<YourEntity> list, @Param("updateCount") Map<String, Integer> outputMap);
```
2. Mapper XML:
```xml
<update id="mergeUpdate">
DECLARE
v_count NUMBER;
BEGIN
MERGE INTO your_table t
USING (
<foreach collection="list" item="item" index="index" separator="union all">
SELECT #{item.id} AS id, #{item.value} AS value FROM dual
</foreach>
) s
ON (t.id = s.id)
WHEN MATCHED THEN
UPDATE SET t.value = s.value
;
v_count := SQL%ROWCOUNT;
:updateCount := v_count; -- 将更新行数赋值给输出参数
END;
</update>
```
但是，在Oracle PL/SQL中，我们需要将输出参数绑定为OUT参数。在MyBatis中，我们可以使用`Map`来传递输出参数。
3. Service层：
```java
@Service
public class MergeUpdateService {
@Autowired
private YourMapper yourMapper;
@Transactional
public int mergeUpdate(List<YourEntity> list) {
Map<String, Integer> outputMap = new HashMap<>();
outputMap.put("updateCount", 0); // 初始化，用于接收OUT参数的值
yourMapper.mergeUpdate(list, outputMap);
return outputMap.get("updateCount");
}
}
```
然而，用户明确要求不使用OutputParameter（即不使用特定的输出参数对象），我们这里使用的是Map，但本质上还是通过OUT参数传递。如果用户希望完全避免任何形式的输出参数，那么我们可以考虑以下方案：
方案二：在MERGE语句后立即执行一个查询获取更新行数（利用临时表或全局临时表）
步骤：
1. 创建一个全局临时表（或者使用事务级临时表）来存储更新的ID。
2. 在MERGE语句中使用LOG ERRORS子句记录更新行？但MERGE不支持直接记录更新行到表。
另一种思路：使用DBMS_SQL包返回行数？但这样更复杂。
考虑到复杂性和性能，通常推荐使用SQL%ROWCOUNT的方式，并通过OUT参数返回。
但是，如果用户坚持不使用任何形式的输出参数，我们可以通过函数包装：
方案三：将MERGE操作封装到函数中，函数返回更新行数
```sql
CREATE OR REPLACE FUNCTION merge_update_func(p_list IN your_table_list_type) RETURN NUMBER AS
v_count NUMBER;
BEGIN
MERGE INTO your_table t
USING TABLE(p_list) s
ON (t.id = s.id)
WHEN MATCHED THEN
UPDATE SET t.value = s.value;
v_count := SQL%ROWCOUNT;
RETURN v_count;
END;
```
然后在MyBatis中调用此函数：
```xml
<select id="mergeUpdate" resultType="int">
SELECT merge_update_func(#{list}) FROM dual
</select>
```
在Service层直接调用这个select，就可以得到更新行数。
但是，这需要创建数据库函数和自定义数组类型（因为要传递集合），可能不是所有项目都允许。
鉴于上述情况，我们回到最初：如果用户只是不想用特定的OutputParameter类，那么使用Map接收OUT参数是可以接受的。
因此，我们推荐使用方案一（通过Map接收OUT参数）作为主要方案。
如果用户不允许使用任何OUT参数（包括Map），那么我们可以考虑方案三（函数）或者方案四（在Service层用JDBC调用函数）。
方案四：使用JDBC调用函数
```java
@Service
public class MergeUpdateService {
@Autowired
private DataSource dataSource;
@Transactional
public int mergeUpdate(List<YourEntity> list) throws SQLException {
try (Connection conn = dataSource.getConnection();
CallableStatement cs = conn.prepareCall("{ ? = call merge_update_func(?) }")) {
// 注册返回参数
cs.registerOutParameter(1, Types.INTEGER);
// 创建数组描述符（需要先定义数组类型）
// 假设已经创建了数据库类型：CREATE TYPE your_table_type AS OBJECT (id NUMBER, value VARCHAR2(100));
// 以及数组类型：CREATE TYPE your_table_array AS TABLE OF your_table_type;
ArrayDescriptor desc = ArrayDescriptor.createDescriptor("YOUR_TABLE_ARRAY", conn);
Object[] objects = list.stream().map(e -> new Object[]{e.getId(), e.getValue()}).toArray();
Array array = new ARRAY(desc, conn, objects);
cs.setArray(2, array);
cs.execute();
return cs.getInt(1);
}
}
}
```
这个方案需要数据库预先创建类型，且代码较为复杂。
综上，根据用户需求“不用OutputParameter”，我们理解为不使用MyBatis的`@Param`配合特定的OutputParameter类（如示例中的OutputParameter），但使用Map应该是可以接受的。因此，我们采用方案一。
下面给出完整的方案一代码：
Mapper接口：
```java
public interface YourMapper {
void mergeUpdate(@Param("list") List<YourEntity> list, @Param("updateCount") Map<String, Integer> outputMap);
}
```
Mapper XML（注意：在PL/SQL块中绑定输出参数）：
```xml
<update id="mergeUpdate">
BEGIN
MERGE INTO your_table t
USING (
SELECT * FROM (
<foreach collection="list" item="item" index="index" separator="union all">
SELECT #{item.id} AS id, #{item.value} AS value FROM dual
</foreach>
)
) s
ON (t.id = s.id)
WHEN MATCHED THEN
UPDATE SET t.value = s.value
;
#{updateCount, mode=OUT, jdbcType=INTEGER} := SQL%ROWCOUNT;
END;
</update>
```
注意：在MyBatis中，使用`#{}`引用参数时，指定mode=OUT，这样MyBatis会将其作为输出参数处理。
但是，在XML中直接写`#{updateCount, mode=OUT, jdbcType=INTEGER} := ...`这样的语法是无效的，因为MyBatis的表达式不支持赋值操作。正确的方式是使用PL/SQL变量和绑定：
修正XML：
```xml
<update id="mergeUpdate">
DECLARE
v_count NUMBER;
BEGIN
MERGE INTO your_table t
USING (
SELECT * FROM (
<foreach collection="list" item="item" index="index" separator="union all">
SELECT #{item.id} AS id, #{item.value} AS value FROM dual
</foreach>
)
) s
ON (t.id = s.id)
WHEN MATCHED THEN
UPDATE SET t.value = s.value
;
v_count := SQL%ROWCOUNT;
:updateCount := v_count; -- 注意这里使用冒号绑定变量
END;
</update>
```
但是，在MyBatis中，我们无法直接在PL/SQL块中通过`:updateCount`绑定到Map的键。正确的方式是使用`#`符号，并且指定参数模式为OUT。
正确做法（使用MyBatis的`parameterMap`或直接使用`@Param`指定）：
```xml
<update id="mergeUpdate" statementType="CALLABLE">
{ call
DECLARE
v_count NUMBER;
BEGIN
MERGE INTO your_table t
USING (
SELECT * FROM (
<foreach collection="list" item="item" index="index" separator="union all">
SELECT #{item.id} AS id, #{item.value} AS value FROM dual
</foreach>
)
) s
ON (t.id = s.id)
WHEN MATCHED THEN
UPDATE SET t.value = s.value
;
v_count := SQL%ROWCOUNT;
#{updateCount, mode=OUT, jdbcType=INTEGER} := v_count;
END
}
</update>
```
然而，MyBatis不支持在匿名块中直接使用`#`符号作为输出参数绑定。因此，我们可能需要将整个块包装在一个存储过程中，或者使用函数（如方案三）。这又回到了复杂性的问题。
鉴于MyBatis对PL/SQL块中的输出参数绑定的支持有限，我们可能需要使用`CallableStatement`，即通过Mapper接口使用`@Options`指定`statementType=StatementType.CALLABLE`，然后在XML中使用`{call ...}`包裹PL/SQL块，并在参数中指定OUT参数。
最终Mapper XML调整：
```xml
<update id="mergeUpdate" statementType="CALLABLE">
{ call
declare
v_count number;
begin
merge into your_table t
using (
select * from (
<foreach collection="list" item="item" separator=" union all ">
select #{item.id} as id, #{item.value} as value from dual
</foreach>
)
) s on (t.id = s.id)
when matched then
update set t.value = s.value
;
v_count := sql%rowcount;
#{updateCount, mode=OUT, jdbcType=INTEGER} := v_count;
end
}
</update>
```
但是，在MyBatis中，匿名块中绑定输出参数要求参数在块外部声明，所以这种内嵌的匿名块绑定可能仍然不行。
因此，我们可能需要将整个操作放到存储过程中，然后在MyBatis中调用存储过程。或者，我们接受使用函数方案（方案三）或者JDBC方案（方案四）。
考虑到简单性，我们推荐使用存储过程：
步骤：
1. 创建存储过程：
```sql
CREATE OR REPLACE PROCEDURE merge_update_proc (
p_list IN your_table_array, -- 需要先定义数组类型
p_update_count OUT NUMBER
) AS
BEGIN
MERGE INTO your_table t
USING TABLE(p_list) s
ON (t.id = s.id)
WHEN MATCHED THEN
UPDATE SET t.value = s.value;
p_update_count := SQL%ROWCOUNT;
END;
```
2. 在MyBatis中调用存储过程：
```xml
<update id="mergeUpdate" statementType="CALLABLE">
{ call merge_update_proc(
#{list, mode=IN, typeHandler=yourArrayTypeHandler},
#{updateCount, mode=OUT, jdbcType=INTEGER}
) }
</update>
```
3. 需要自定义TypeHandler来处理数组类型。
这又引入了存储过程和自定义类型，增加了数据库依赖。
鉴于以上复杂性，如果项目允许，我们推荐使用最初的批处理方案（通过MyBatis批处理模式执行多个单条更新，然后统计更新行数）或者使用函数方案。
由于用户要求使用MERGE，且希望得到更新行数，同时不使用OutputParameter（我们理解为避免使用特定对象），我们使用Map作为输出参数容器，并采用存储过程方式。但是，如果项目不允许创建存储过程，那么我们将面临困难。
因此，我们提供一种折中方案：在Service层使用JdbcTemplate调用存储过程（如果允许创建存储过程）或者使用函数。
如果项目不允许创建数据库对象，那么只能放弃MERGE，改用其他批量更新方式（如批处理更新）。
总结：根据用户的具体环境和约束，选择最合适的方案。如果环境允许，创建存储过程并使用Map接收OUT参数是可行的。
由于问题中没有明确说明数据库对象创建的限制，我们给出一个使用存储过程和MyBatis调用的完整示例：
步骤1：创建数组类型和存储过程（在数据库中执行）
```sql
CREATE OR REPLACE TYPE your_table_record AS OBJECT (
id NUMBER,
value VARCHAR2(100)
);
/
CREATE OR REPLACE TYPE your_table_array AS TABLE OF your_table_record;
/
CREATE OR REPLACE PROCEDURE merge_update_proc (
p_list IN your_table_array,
p_update_count OUT NUMBER
) AS
BEGIN
MERGE INTO your_table t
USING (SELECT * FROM TABLE(p_list)) s
ON (t.id = s.id)
WHEN MATCHED THEN
UPDATE SET t.value = s.value;
p_update_count := SQL%ROWCOUNT;
END;
/
```
步骤2：配置MyBatis调用存储过程
Mapper接口：
```java
void mergeUpdate(@Param("list") List<YourEntity> list, @Param("updateCount") Map<String, Integer> outputMap);
```
Mapper XML:
```xml
<update id="mergeUpdate" statementType="CALLABLE">
{ call merge_update_proc(
#{list, typeHandler=com.example.YourArrayTypeHandler, jdbcType=ARRAY},
#{updateCount, mode=OUT, jdbcType=INTEGER}
) }
</update>
```
需要自定义TypeHandler（YourArrayTypeHandler）将List<YourEntity>转换为Oracle的ARRAY。
步骤3：自定义TypeHandler
```java
public class YourArrayTypeHandler extends BaseTypeHandler<List<YourEntity>> {
@Override
public void setNonNullParameter(PreparedStatement ps, int i, List<YourEntity> parameter, JdbcType jdbcType) throws SQLException {
Connection conn = ps.getConnection();
// 创建对象数组
Object[] objects = new Object[parameter.size()];
for (int idx = 0; idx < parameter.size(); idx++) {
YourEntity entity = parameter.get(idx);
// 创建STRUCT对象 for each record
// 注意：这里需要创建STRUCT数组，或者使用OracleConnection的createARRAY
StructDescriptor sd = StructDescriptor.createDescriptor("YOUR_TABLE_RECORD", conn);
Object[] attr = new Object[]{entity.getId(), entity.getValue()};
objects[idx] = new STRUCT(sd, conn, attr);
}
Array array = new ARRAY(ArrayDescriptor.createDescriptor("YOUR_TABLE_ARRAY", conn), conn, objects);
ps.setArray(i, array);
}
// ... 其他方法不用实现，因为不会从结果集中读取这个类型
}
```
步骤4：Service层
```java
@Service
public class MergeUpdateService {
@Autowired
private YourMapper yourMapper;
public int mergeUpdate(List<YourEntity> list) {
Map<String, Integer> outputMap = new HashMap<>();
outputMap.put("updateCount", 0);
yourMapper.mergeUpdate(list, outputMap);
return outputMap.get("updateCount");
}
}
```
这个方案较为复杂，需要创建数据库类型和存储过程，以及自定义TypeHandler。
如果用户的环境不允许创建数据库对象，那么建议重新考虑使用批处理更新（多条单update）并统计更新行数，或者使用动态SQL的foreach更新（但注意Oracle的IN列表限制）。
鉴于问题复杂，我们提供一种在Service层通过JDBC直接调用存储过程的方法（避免MyBatis的复杂性）：
```java
@Service
public class MergeUpdateService {
@Autowired
private DataSource dataSource;
@Transactional
public int mergeUpdate(List<YourEntity> list) throws SQLException {
try (Connection conn = dataSource.getConnection();
CallableStatement cs = conn.prepareCall("{ call merge_update_proc(?, ?) }")) {
// 创建对象数组
Object[] objects = new Object[list.size()];
for (int i = 0; i < list.size(); i++) {
YourEntity entity = list.get(i);
objects[i] = new Object[]{entity.getId(), entity.getValue()};
}
// 创建ARRAY
ArrayDescriptor arrayDesc = ArrayDescriptor.createDescriptor("YOUR_TABLE_ARRAY", conn);
Array array = new ARRAY(arrayDesc, conn, objects);
cs.setArray(1, array);
cs.registerOutParameter(2, Types.INTEGER);
cs.execute();
return cs.getInt(2);
}
}
}
```
这仍然需要数据库对象存在。
综上所述，如果用户的环境允许创建数据库对象，我们推荐使用存储过程方案。否则，建议放弃MERGE，采用其他批量更新方式。
由于问题要求使用MERGE，我们只能给出依赖数据库对象的方案。
注意：以上方案仅供参考，实际实施时需根据项目具体情况进行调整。
§§相关问题§§
1. 在Oracle中使用MERGE语句进行批量更新时，如何避免创建额外的数据库对象（如数组类型和存储过程）？
2. MyBatis调用Oracle存储过程时，如何传递自定义对象数组作为输入参数？
3. 使用MERGE语句时，如何优雅地处理并发更新冲突？
4. 在Spring事务中，如何确保MERGE语句的原子性和一致性？
5. 除了MERGE，Oracle还有哪些高效的批量更新方法？</think>在 Oracle 中使用 `MERGE` 语句进行批量更新操作，并在 Service 层获取更新行数的完整实现方案如下：
### 推荐方案：MERGE + SQL%ROWCOUNT
```java
@Service
public class MergeService {
@Autowired
private SqlSessionFactory sqlSessionFactory;
@Transactional
public int mergeUpdate(List<YourEntity> list) {
try (SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH)) {
YourMapper mapper = sqlSession.getMapper(YourMapper.class);
// 执行MERGE操作
mapper.mergeUpdate(list);
// 获取实际更新行数
List<BatchResult> batchResults = sqlSession.flushStatements();
int totalRows = 0;
for (BatchResult result : batchResults) {
for (int count : result.getUpdateCounts()) {
if (count >= 0) totalRows += count;
}
}
sqlSession.commit();
return totalRows;
}
}
}
```
### Mapper XML 配置 (Oracle)
```xml
<update id="mergeUpdate">
MERGE INTO your_table t
USING (
<foreach collection="list" item="item" separator="union all">
SELECT
#{item.id} AS id,
#{item.col1} AS col1,
#{item.col2} AS col2
FROM dual
</foreach>
) s
ON (t.id = s.id)
WHEN MATCHED THEN
UPDATE SET
t.col1 = s.col1,
t.col2 = s.col2
WHERE t.version = #{item.version} <!-- 乐观锁控制 -->
WHEN NOT MATCHED THEN
INSERT (id, col1, col2)
VALUES (s.id, s.col1, s.col2)
</update>
```
### 替代方案：纯 JDBC 实现
```java
@Service
public class MergeJdbcService {
@Autowired
private DataSource dataSource;
@Transactional
public int mergeWithJdbc(List<YourEntity> list) throws SQLException {
String sql = "MERGE INTO your_table t " +
"USING (SELECT ? id, ? col1, ? col2 FROM dual) s " +
"ON (t.id = s.id) " +
"WHEN MATCHED THEN UPDATE SET t.col1 = s.col1 " +
"WHEN NOT MATCHED THEN INSERT (id, col1) VALUES (s.id, s.col1)";
try (Connection conn = dataSource.getConnection();
PreparedStatement ps = conn.prepareStatement(sql)) {
for (YourEntity item : list) {
ps.setLong(1, item.getId());
ps.setString(2, item.getCol1());
ps.setString(3, item.getCol2());
ps.addBatch();
}
int[] updateCounts = ps.executeBatch();
int totalRows = 0;
for (int count : updateCounts) {
if (count == Statement.SUCCESS_NO_INFO) {
// Oracle 返回-2时需要特殊处理
totalRows += 1;
} else if (count > 0) {
totalRows += count;
}
}
return totalRows;
}
}
}
```
### 关键优化技巧
1. **分批次处理**（避免 ORA-01555）
```java
int batchSize = 500;
for (int i = 0; i < list.size(); i += batchSize) {
List<YourEntity> batch = list.subList(i, Math.min(i + batchSize, list.size()));
mergeUpdate(batch);
}
```
2. **乐观锁控制**
```xml
<!-- 在UPDATE子句中添加版本检查 -->
WHEN MATCHED THEN
UPDATE SET
t.col1 = s.col1,
t.version = t.version + 1
WHERE t.version = #{item.version}
```
3. **错误日志记录**
```sql
LOG ERRORS INTO err$_your_table REJECT LIMIT UNLIMITED
```
4. **性能优化参数**
```java
// 在JDBC连接后设置
try (Connection conn = dataSource.getConnection()) {
conn.setAutoCommit(false);
try (Statement stmt = conn.createStatement()) {
stmt.execute("ALTER SESSION ENABLE PARALLEL DML");
stmt.execute("ALTER SESSION FORCE PARALLEL DML PARALLEL 8");
}
// 执行MERGE操作
}
```
### 注意事项
1. **更新行数统计规则**：
- Oracle 的 `MERGE` 只统计实际修改的行（数据无变化不计数）
- 插入操作返回计数1
- 更新操作返回实际修改行数（可能为0）
2. **并发控制**：
```sql
WHEN MATCHED THEN
UPDATE SET ...
WHERE t.version = #{version}
AND t.last_update > SYSDATE - 5/(24*60) -- 防止并发覆盖
```
3. **索引优化**：
- 确保ON子句的列有索引
- 对大数据集创建临时表替代UNION ALL
扫一扫
1590
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
