1.Oracle batch operation

My watch structure is

1.1 batch insertion

1.1.1 primary key does not use sequence

insert into students(sid,sname,ssal,ssex)
select 9,'Zhang San',3000,'male' from dual
union all select 10,'Li Si',4000,'male' from dual
union all select 11,'Xiaohong',5000,'female' from dual

The primary key is specified directly by specific values, such as 9, 10 and 11 above. It is applicable to the case that the primary key id is a random number. In projects, we sometimes let the primary key id grow by itself, so we need to use the sequence at this time.

1.1.2 sequence self growth of primary key usage

My sequence name: SEQ Ou stu

insert into students(sid,sname,ssal,ssex)
select seq_stu.nextval,s.* from
(
select 'Zhang three 2',4000,'female' from dual
union all select 'Lee four 2',5000,'female' from dual
) s

This method is similar to the above method of not using sequence. First, you do not specify the primary key column in the sub query, find out several pieces of data (to be inserted), and then combine the primary key and those pieces of data together, which is equivalent to selecting seq_stu.nextval, s. * from s has two pieces of data, so the sequence will also produce two values.

2.mybatis performs batch operation on Oracle

In fact, it uses the dynamic sql function of mybatis to splice the batch operations of Oracle.

The table I use here is the backup of emp table under the scott database that comes with Oracle. The copy? emp table.

2.1 batch insertion

2.1.1 primary key does not use sequence

  <!--Batch insertion-->
  <insert id="insertBatch" parameterType="com.caihao.oracledemo1.entity.CopyEmp">
    INSERT INTO copy_emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
    <foreach collection="copyEmpList" item="copyEmp" separator="UNION ALL">
      SELECT #{copyEmp.empno},#{copyEmp.ename},#{copyEmp.job},#{copyEmp.mgr},
        #{copyEmp.hiredate},#{copyEmp.sal},#{copyEmp.comm},#{copyEmp.deptno}
      FROM dual
    </foreach>
  </insert>

2.1.2 primary key usage sequence

  <!--Bulk insert, using sequence-->
  <insert id="insertBatchWithSequence" parameterType="com.caihao.oracledemo1.entity.CopyEmp">
    INSERT INTO copy_emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
    SELECT seq_stu.nextval,s.* FROM
    (
    <foreach collection="copyEmpList" item="copyEmp" separator="UNION ALL">
		SELECT #{copyEmp.ename},#{copyEmp.job},#{copyEmp.mgr},
        #{copyEmp.hiredate},#{copyEmp.sal},#{copyEmp.comm},#{copyEmp.deptno}
        FROM dual
    </foreach>
    ) s
  </insert>

As for updates, deletions and queries, they are similar to mysql, so they are not recorded.