oracle批量插入

发布于 2020-08-14  435 次阅读


第一种

begin
                insert into employees(id,last_name,email,gender,d_id)
                values(employees_seq.nextval,'test_001','test_001@atguigu.com','男',1);
                insert into employees(employee_id,last_name,email,gender,d_id)
                values(employees_seq.nextval,'test_002','test_002@atguigu.com','男',1);
end;

第二种

insert into employees(id,last_name,email,gender,d_id)
     select employees_seq.nextval,lastName,email,gender,d_id from(
             select 'test_a_01' lastName,'1@qq.com' email ,'男' gender,1 d_id from dual
                      union
             select 'test_a_02' lastName,'1@qq.com' email ,'男' gender,1 d_id from dual
                      union
             select 'test_a_03' lastName,'1@qq.com' email ,'男' gender,1 d_id from dual
 )  

mapper中写法

<insert id="addEmps" databaseId="oracle">
<!-- oracle第一种批量方式 -->
<!-- <foreach collection="emps" item="emp" open="begin" close="end;">
     insert into employees(employee_id,last_name,email,gender,d_id)
    values(employees_seq.nextval,#{emp.lastName},#{emp.email},#{emp.gender,#{emp.dept.id});
        </foreach> -->
        
        <!-- oracle第二种批量方式  -->
        insert into tbl_employee(
            <!-- 引用外部定义的sql -->
            <include refid="insertColumn">
                <property name="testColomn" value="abc"/>
            </include>
        )
                <foreach collection="emps" item="emp" separator="union"
                    open="select employees_seq.nextval,lastName,email,gender,d_id from("
                    close=")">
                    select #{emp.lastName} lastName,#{emp.email} email,#{emp.gender} gender,#{emp.dept.id} d_id from dual
                </foreach>
     </insert>

sql片段

 <!-- 
        抽取可重用的sql片段。方便后面引用 
        1、sql抽取:经常将要查询的列名,或者插入用的列名抽取出来方便引用
        2、include来引用已经抽取的sql:
        3、include还可以自定义一些property,sql标签内部就能使用自定义的属性
                include-property:取值的正确方式${prop},
                #{不能使用这种方式}
      -->
    <sql id="insertColumn">
            <if test="_databaseId=='oracle'">
                id,last_name,email,gender,d_id
            </if>
            <if test="_databaseId=='mysql'">
                last_name,email,gender,d_id
            </if>
      </sql>