Oracle数据库-批量操作_oracle批量插入和回退

一. 批量新增

1.采用insert into批量插入

例如：条件中，tempId与itemId为一对多的关系，itemId是存放在数组中的，需循环遍历，循环之间要用union all 连接

insert into item_temp(item,temp)
 <foreach collection="itemArray" item="item" index="index" separator="union all">
    select #{
   item.itemId},#{
   tempId} from dual
</foreach>
1
2
3
4
5
6

<insert id="insertLabel" parameterType="LabelVO">
        insert all
        <foreach collection="idList" item="item" index="index">
            into R_Label(id,userNum,userId,pid,caseId,userName)
            values(#{
   item.id},#{
   item.userNum},#{
   userId}
            <if test="pid != null and pid != ''">
                ,#{
   pid}
            </if>
            <if test="pid == null or pid == ''">
                ,#{
   item.pid}
            </if>
            <if test="caseId != null and caseId != ''">
                ,#{
   caseId}
            </if>
            <if test="caseId == null">
                ,''
            </if>
            ,#{
   userName})
        </foreach>
        select 1 from dual
    
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27