The following are the cases where you can't use a sequence:
For a SELECT Statement:
- In a WHERE clause
- In a GROUP BY or ORDER BY clause
- In a DISTINCT clause
- Along with a UNION or INTERSECT or MINUS
- In a sub-query
Other areas:
- A sub-query of Update or Delete
- In a View or snapshot
- In a DEFAULT or CHECK Condition of a table definition
- Within a single SQL statement that uses CURRVAL or NEXTVAL, all referenced LONG columns, updated tables, and locked tables must be located on the same database.
使用mybatis将数据批量插入到oracle中,在映射文件中怎么写啊?
1.mybaits版本,3.1.0
2.
- <!-- 批量插入 -->
- <insert id="batchInsert" parameterType="java.util.List">
- insert into b_agent_info
- (id,hkgs,userName,custName,office,remark) values
- <foreach collection="list" item="item" index="index"
- separator=",">
- (#{item.id},#{item.hkgs},#{item.userName},#{item.custName},#{item.office},#{item.remark})
- </foreach>
- </insert>
<resultMap type="Preson" id="getPreson">
<id column="presonId" property="presonId"/>
<result column="presonName" property="presonName"/>
<result column="presonTel" property="presonTel"/>
<result column="presonEmail" property="presonEmail"/>
<result column="presonAge" property="presonAge"/>
</resultMap>
<!-- 批量插入 -->
<insert id="inserts" parameterType="java.util.List">
<!-- 这个在下面循环的时候貌似没有被循环
??????????????
-->
<selectKey keyProperty="id" resultType="int" order="BEFORE">
select seq_preson_id.nextval from dual
</selectKey>
insert into preson
<foreach collection="list" item="item" index="index"
separator="union">
select #{id},#{item.presonName},#{item.presonTel},#{item.presonEmail},#{item.presonAge} from dual
</foreach>
</insert>
<!-- 批量插入 -->
<insert id="inserts" parameterType="java.util.List">
<!-- 在此处做了修改 -->
insert into PRESON
select SEQ_PRESON_ID.NEXTVAL,A.* from(
<foreach collection="list" item="item" index="index"
separator="UNION">
SELECT
#{item.presonName},
#{item.presonTel},
#{item.presonEmail},
#{item.presonAge}
from dual
</foreach>
) A
</insert>