oracle 批量插入语句

常规sql写法

INSERT ALL
  INTO t (a,b) VALUES (1,2)
  INTO t (a,b) VALUES (3,4)
SELECT 1 FROM DUAL;

mybatis foreach 写法

<insert id="batchInsert">
    INSERT ALL
    <foreach collection="list" item="item">
        INTO A (
            ID,
            S_NUM,
            OPER_USER,
            OPER_DT,
            INSERT_DT
        ) VALUES (
            #{item.id},
            #{item.sNum},
            #{item.operUser},
            #{item.openDt},
            #{item.insertDt}
        )
    </foreach>
    SELECT 1 FROM DUAL
</insert>

使用oracle序列写法

<insert id="batchInsert" parameterType="java.util.List">
    INSERT INTO A(
        ID,
        S_NUM,
        OPER_USER,
        OPER_DT,
        INSERT_DT
    )
    SELECT 
        SEQ_A.NEXTVAL,
        t.s_num,
        t.oper_user,
        t.oper_dt,
        t.insert_dt
    FROM (
        <foreach collection="list" item="item" separator=" UNION ALL ">
            SELECT 
                #{item.sNum, jdbcType=INTEGER} AS s_num,
                #{item.operUser, jdbcType=VARCHAR} AS oper_user,
                #{item.openDt, jdbcType=DATE} AS oper_dt,
                #{item.insertDt, jdbcType=DATE} AS insert_dt
            FROM DUAL
        </foreach>
    ) t
</insert>