java oracle 批量merge,mybatis各种批量执行留念（Oracle）

merge into批量执行

参数为List>

MERGE INTO T_SYS_DEPARTMENT t1

USING (

select

#{item.departmentId} AS department_id,#{item.departmentName} AS department_name,#{item.workStatus} AS

work_status,#{item.departmentLeader} AS department_leader,#{item.departmentChief} AS department_chief

from dual

) t2 ON (t1.department_id=t2.department_id)

WHEN MATCHED THEN

UPDATE SET t1.department_name=t2.department_name,t1.work_status=t2.work_status,t1.department_leader=t2.department_leader,t1.department_chief=t2.department_chief

WHEN NOT MATCHED THEN

INSERT (t1.department_id,t1.department_name,t1.work_status,t1.department_leader,t1.department_chief) VALUES

(t2.department_id,t2.department_name,t2.work_status,t2.department_leader,t2.department_chief)

参数为Map，Map中含有List的批量insert

传入参数格式大致如下：

Map map = new HashMap<>();

map.put("roleList",list);

map.put("user_id","1");

sql如下：

SELECT (nvl(max(ID),0) + 1) FROM T_USER_ROLE

INSERT INTO t_user_role

SELECT (#{roleId}+#{index}),'EBTS',#{user_id},#{item},sysdate,NULL FROM dual

批量删除

DELETE FROM T_SYS_DEPARTMENT

WHERE

#{id}