记录一次oracle批量插入操作

<!-- 批量添加人员信息 -->
    <insert id="addPersonList">
        begin
        <foreach collection="personList" item="item" index="index" separator=";">
            insert into T_POLICE_PERSON(id, name, birthday, cardNo, sex, departmentId, policeId, callGroup, callNo, callgpsId, telephone,
            position, mobilePhone, address, email, image, policeType, establishment, dutyType, status, sequenceId, isDelete,
            addTime, updateTime) values
            (#{item.id}||LPAD(SEQ_T_POLICE_PERSON.nextval,6,0),
            #{item.name,jdbcType=VARCHAR}, #{item.birthday,jdbcType=TIMESTAMP},
            #{item.cardNo,jdbcType=VARCHAR}, #{item.sex,jdbcType=VARCHAR},
            #{item.departmentId,jdbcType=VARCHAR}, #{item.policeId,jdbcType=VARCHAR},
            #{item.callGroup,jdbcType=VARCHAR}, #{item.callNo,jdbcType=VARCHAR},
            #{item.callgpsId,jdbcType=VARCHAR}, #{item.telephone,jdbcType=VARCHAR},
            #{item.position,jdbcType=VARCHAR}, #{item.mobilePhone,jdbcType=VARCHAR},
            #{item.address,jdbcType=VARCHAR}, #{item.email,jdbcType=VARCHAR},
            #{item.image,jdbcType=VARCHAR}, #{item.policeType,jdbcType=VARCHAR},
            #{item.establishment,jdbcType=VARCHAR}, #{item.dutyType,jdbcType=VARCHAR},
            #{item.status,jdbcType=VARCHAR}, #{item.sequenceId,jdbcType=INTEGER},
            #{item.isDelete,jdbcType=VARCHAR}, #{item.addTime,jdbcType=TIMESTAMP},
            #{item.updateTime,jdbcType=TIMESTAMP})
        </foreach>
        ;end;
    </insert>

拼接符号 || 

补全函数：左  LPAN('ABDC','补全位数'，'要不全的数值')   lpad( string, padded_length, [ pad_string ] ) 

LPAD是在字符串左边用设定的字符补满指定位数，RPAD则是右边

如

LPAD('1234',8,'0')  的结果是00001234

RPAD('1234',8,'0') 的结果则是12340000

 

 <!-- 批量添加手台信息 -->
    <insert id="addCallList">
        insert into t_police_call(id, deviceId, gpsId, frequency, departmentId, factoryName, model, registerDate,
        status, remark, addTime, updateTime)
        select SEQ_T_POLICE_CALL.nextval, pc.* FROM (
        <foreach collection="callList" item="call" index="index" separator="union all">
            (select #{call.deviceId,jdbcType=VARCHAR},
            #{call.gpsId,jdbcType=VARCHAR},
            #{call.frequency,jdbcType=VARCHAR},
            #{call.departmentId,jdbcType=VARCHAR},
            #{call.factoryName,jdbcType=VARCHAR},
            #{call.model,jdbcType=VARCHAR},
            #{call.registerDate,jdbcType=TIMESTAMP},
            #{call.status,jdbcType=VARCHAR},
            #{call.remark,jdbcType=VARCHAR},
            #{call.addTime,jdbcType=TIMESTAMP},
            #{call.updateTime,jdbcType=TIMESTAMP} FROM DUAL)
        </foreach>
        ) pc
    </insert>