Oracle中以insert values的形式同时插入多条记录（批处理操作insert、update、delete）

 

• 以insert values实现批量插入数据

由于要做测试数据插oracle数据库，想到了mysql的多个values的形式，但是sql报错。oracle并不支持这种一个insert into 多个values的方法

INSERT INTO users(name, age) VALUES('ccc', 333), ('aaa', 222), ('bbb', 111);  

但是有另一种写法，做个简单记录，跟mysql中的一个insert多个values的形式差不多。

INSERT ALL INTO tb_red VALUES(1000, 8001, '2016-10-10 10:59:59', 1, 8001, '测试用户1000', '红名单0', '男', '膜法学院', '被测')  
INTO tb_red  VALUES (1001, 8001, '2016-10-10 11:00:00', 2, 8001, '测试用户1001', '红名单1', '男', '膜法学院', '被测')  
INTO tb_red  VALUES (1002, 8001, '2016-10-10 11:00:01', 0, 8001, '测试用户1002', '红名单2', '男', '膜法学院', '被测')  
INTO tb_red  VALUES (1003, 8001, '2016-10-11 10:59:59', 1, 8001, '测试用户1003', '红名单3', '男', '膜法学院', '被测')  
INTO tb_red  VALUES (1004, 8001, '2016-10-11 11:00:00', 2, 8001, '测试用户1004', '红名单4', '男', '膜法学院', '被测')  
INTO tb_red  VALUES (1005, 8001, '2016-10-11 11:00:01', 0, 8001, '测试用户1005', '红名单5', '男', '膜法学院', '被测')  
select 1 from dual;  

结果：

 

说明：

1.第一句用的是insert all into 不是 insert into

2.最后跟的selecr 1 from dual语句中的dual表可以被替换为任何一个只要不是tb_red的表

3.和mysql的写法不一样，多个values之间不用逗号分隔，但是需要加into tablename的形式的语句在每个values前面

4.只适合于Oralce 9i以上版本

 

• mybatis中使用批量插入，需要注意如下

需求:查出给定id的记录:

<select id="getEmpsByConditionForeach" resultType="com.test.beans.Employee">  
        SELECT * FROM tb1_emplyee WHERE id IN  
        <foreach collection="list" item="item_id" separator="," open="(" close=")">  
            #{item_id}  
        </foreach>  
</select>  

 

关于foreach标签,有几个属性应该注意一下:

[java] view plain copy

1. collection：指定要遍历的集合：  
2. list类型的参数会特殊处理封装在map中，map的key就叫list  
3. item：将当前遍历出的元素赋值给指定的变量  
4. separator:每个元素之间的分隔符  
5. open：遍历出所有结果拼接一个开始的字符  
6. close:遍历出所有结果拼接一个结束的字符  
7. index:索引。遍历list的时候是index就是索引，item就是当前值  
8. 遍历map的时候index表示的就是map的key，item就是map的值  
9. #{变量名}就能取出变量的值也就是当前遍历出的元素  

 

foreach标签也可以实现实现批量插入(删除)数据:

mysql批量插入数据:

<insert id="addEmps">  
        INSERT INTO tb1_emplyee(last_name,email,gender,d_id)  
        VALUES   
        <foreach collection="emps" item="emp" separator=",">  
            (#{emp.lastName},#{emp.email},#{emp.gender},#{emp.dept.id})  
        </foreach>  
</insert>  

oracle批量插入数据:

<insert id="addEmps">  
        INSERT INTO tb1_emplyee(last_name,email,gender,d_id)  
        SELECT SEQ_OCL_EMPLYEE.nextval, A.* from (
        <foreach collection="emps" item="emp" index="index" separator="UNION ALL">  
            select  #{emp.lastName},#{emp.email},#{emp.gender},#{emp.dept.id} from dual
        </foreach>  
        ) A
</insert> 

 运行通过。在Oracle的版本中，有几点需要注意的：

        1.SQL中没有VALUES；

        2.<foreach>标签中的(selece ..... from dual)；

        3.<foreach>标签中的separator的属性为"UNION ALL"，将查询合并结果集。

        4.<foreach>标签中的separator的属性为"UNION ALL"，将查询合并结果集。

 

• 其他批量操作（转载）

oracle批量插入：

<insert id="insertBatch" parameterType="java.util.List">  
     insert into table_name(id, name,updateTime)  
      <foreach collection="list" item="item" index="index" separator="union all" >   
       (select    
     #{item.id,jdbcType=VARCHAR}, #{item.name,jdbcType=VARCHAR}, #{item.updateTime,jdbcType=TIMESTAMP}  
        from dual)  
     </foreach>  
</insert>  

 需要注意的是sql中没有values；<foreach>标签中语句 (select ..... from dual)

oracle批量更新：

<update id="updateBatch" parameterType="java.util.List">  
     begin    
         <foreach collection="list" item="item" index="index" separator=";" >  
             update table_name  
             <set>  
                 name= #{item.name}  
             </set>  
             where id = #{item.id}  
         </foreach>  
         ;end;  
</update>  

或者 

<update id="updateBatch" parameterType="java.util.List">  
   <foreach collection="list" item="item" index="index" open="begin" close="end;" separator=";">  
      update table_name  
      <set>  
         name= #{item.name}  
      </set>  
         where id = #{item.id}  
   </foreach>  
</update>  

 

oracle批量删除：

<delete id="deleteBatch" parameterType="java.util.List">  
   delete from table_name  
   <where>  
     <foreach collection="list" index="index" item="item" open="(" separator="or" close=")">       
  id=#{item.id}  
     </foreach>  
   </where>  
</delete>