<!-- 批量添加人员信息 -->
<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>