oracle批量插入，自增主键出现的问题。

两种oracle批量插入的实例。

oracle自增主键批量插入会出现错误。不使用自增主键是不会报错的。

Java 程序 调用批量插入的方法：

 List<CategoryExpertGrade> categoryExpertGrades=new ArrayList<CategoryExpertGrade>();       CategoryExpertGrade c1=new CategoryExpertGrade();       CategoryExpertGrade c2=new CategoryExpertGrade();       c1.setGrade("C");       c2.setGrade("D");       c1.setExpertFk(124L);       c2.setExpertFk(124L);       c1.setCategoryFk(4171L);       c2.setCategoryFk(4172L);       categoryExpertGrades.add(c1);       categoryExpertGrades.add(c2);       int i=categoryExpertGradeMapper.insertList(categoryExpertGrades);

第一种：

mybatis  sql语句：

  <insert id="insertList"  >    insert    into CATEGORY_EXPERT_GRADE (PK, CATEGORY_FK, EXPERT_FK,    GRADE, SAVE_DATE, FLAG,    EXTEND1, EXTEND2, EXTEND3    )    <foreach collection="recordList"  item="i"  close=" " open=" " separator=" union all ">    select  Sequence_Shzj.nextval ,      '${i.categoryFk}' , '${i.expertFk}' ,      '${i.grade}' , '${i.saveDate}' , '${i.flag}' ,      '${i.extend1}' , '${i.extend2}' , '${i.extend3}'  from dual    </foreach>  </insert>

出错结果：

### SQL: insert    into CATEGORY_EXPERT_GRADE (PK, CATEGORY_FK, EXPERT_FK,     GRADE, SAVE_DATE, FLAG,     EXTEND1, EXTEND2, EXTEND3     )        select  Sequence_Shzj.nextval ,       '4171' , '124' ,       'C' , '' , '' ,       '' , '' , ''  from dual         union all       select  Sequence_Shzj.nextval ,       '4172' , '124' ,       'D' , '' , '' ,       '' , '' , ''  from dual  e: java.sql.SQLException: ORA-02287: 此处不允许序号  

第二种：

mybatis sql语句

<insert id="insertList"  >    insert ALL    <foreach collection="recordList"  item="i"  close=" " open=" " separator=" ">    into CATEGORY_EXPERT_GRADE (PK, CATEGORY_FK, EXPERT_FK,    GRADE, SAVE_DATE, FLAG,    EXTEND1, EXTEND2, EXTEND3    )    values (Sequence_Shzj.nextval, #{i.categoryFk,jdbcType=DECIMAL}, #{i.expertFk,jdbcType=DECIMAL},    #{i.grade,jdbcType=VARCHAR}, #{i.saveDate,jdbcType=DATE}, #{i.flag,jdbcType=DECIMAL},    #{i.extend1,jdbcType=VARCHAR}, #{i.extend2,jdbcType=VARCHAR}, #{i.extend3,jdbcType=VARCHAR}    )    </foreach>    SELECT 1 FROM DUAL  </insert>

错误结果：

### SQL: insert ALL              into CATEGORY_EXPERT_GRADE (PK, CATEGORY_FK, EXPERT_FK,     GRADE, SAVE_DATE, FLAG,     EXTEND1, EXTEND2, EXTEND3     )     values (Sequence_Shzj.nextval, ?, ?,     ?, ?, ?,     ?, ?, ?     )      into CATEGORY_EXPERT_GRADE (PK, CATEGORY_FK, EXPERT_FK,     GRADE, SAVE_DATE, FLAG,     EXTEND1, EXTEND2, EXTEND3     )     values (Sequence_Shzj.nextval, ?, ?,     ?, ?, ?,     ?, ?, ?     )             SELECT 1 FROM DUAL### Cause: java.sql.SQLException: ORA-00001: 违反唯一约束条件 (XIAOMI.PK_CATEGORY_EXPERT_GRADE), PK 的值是相同的。

上面的两种批量插入的方式都不能使用序列。

解决办法：

写一个函数包装一下序列：

create or replace function get_seq return number is  Result number;begin  select Sequence_Shzj.nextval into Result from dual;  return(Result);end get_seq;

sql语句中的Sequence_Shzj.nextval都替换成get_seq;