思路為采用Oracle?中insert語句的高級用法：INSERT ALL ，批量插入數據：
INSERT ALL舉例：
1、建測試表
CREATE?TABLE?EDW_INT(
AGMT_NO?VARCHAR2(40?BYTE)?NOT?NULL, AGMT_SUB_NO?VARCHAR2(4?BYTE)?NOT?NULL, NEED_REPAY_INT?NUMBER(22,2), CURR_PERIOD?NUMBER(4)?NOT?NULL);
CREATE?TABLE?EDW_INT_1
(
AGMT_NO?VARCHAR2(40?BYTE)?NOT?NULL, AGMT_SUB_NO?VARCHAR2(4?BYTE)?NOT?NULL, NEED_REPAY_INT?NUMBER(22,2), CURR_PERIOD?NUMBER(4)?NOT?NULL);
CREATE?TABLE?EDW_INT_2
(
AGMT_NO?VARCHAR2(40?BYTE)?NOT?NULL, AGMT_SUB_NO?VARCHAR2(4?BYTE)?NOT?NULL, NEED_REPAY_INT?NUMBER(22,2), CURR_PERIOD?NUMBER(4)?NOT?NULL);
2.插入測試數據
INSERT?INTO?EDW_INT? (AGMT_NO,?AGMT_SUB_NO,?NEED_REPAY_INT,?CURR_PERIOD)?VALUES?
('20003874',?'2104',?3126.5,?7);?INSERT?INTO?EDW_INT?
(AGMT_NO,?AGMT_SUB_NO,?NEED_REPAY_INT,?CURR_PERIOD)?VALUES?
('20003874',?'2104',?3290.76,?6);?INSERT?INTO?EDW_INT?
(AGMT_NO,?AGMT_SUB_NO,?NEED_REPAY_INT,?CURR_PERIOD)?VALUES?
('20003874',?'2104',?3454.06,?5);?INSERT?INTO?EDW_INT?
(AGMT_NO,?AGMT_SUB_NO,?NEED_REPAY_INT,?CURR_PERIOD)?VALUES?
('20003874',?'2104',?3616.41,?4);?INSERT?INTO?EDW_INT?
(AGMT_NO,?AGMT_SUB_NO,?NEED_REPAY_INT,?CURR_PERIOD)?VALUES?
('20017143',?'2104',?2350.86,?0);?INSERT?INTO?EDW_INT?
(AGMT_NO,?AGMT_SUB_NO,?NEED_REPAY_INT,?CURR_PERIOD)?VALUES?
('20017143',?'2104',?3566.55,?0);?INSERT?INTO?EDW_INT?
(AGMT_NO,?AGMT_SUB_NO,?NEED_REPAY_INT,?CURR_PERIOD)?VALUES?
('20018273',?'2104',?1639.46,?0);?INSERT?INTO?EDW_INT?
(AGMT_NO,?AGMT_SUB_NO,?NEED_REPAY_INT,?CURR_PERIOD)?VALUES?
('20018273',?'2104',?2080.49,?0);?COMMIT;
3.insert all-不帶條件
insert?all?into?edw_int_1(agmt_no,agmt_sub_no,need_repay_int,curr_period)?
values(agmt_no,agmt_sub_no,need_repay_int,curr_period)?
into?edw_int_2(agmt_no,agmt_sub_no,curr_period)?
values(agmt_no,'1234',curr_period)?
select?agmt_no,agmt_sub_no,need_repay_int,curr_period?from?edw_int;?
commit;
結合該問題具體分析：
mybatis 的和dao相關的sql寫法如下：
<insert?id="batchSave">INSERT?ALL
<foreach?collection="list"?item="item">
INTO?FSP_BUSINESS_INTEREST_REPORT
(
ID,
BUSINESS_DAY,
LOAN_NO,
CIF_NO,
CIF_NAME,
LOAN_TYPE,
REPAY_WAY,
TERM_TYPE,
VOUCH_WAY,
CHANNEL_TYPE,
LOAN_BEGIN_DATE,
LOAN_END_DATE,
RATE_BEGIN_DATE,
RATE_END_DATE,
AMT,
RATE_TYPE,
RATE,
RATE_AMT,
PAY_TYPE,
REPORT_OUT_FLAG,
REPORT_OUT_DATE,
REPORT_OUT_AMT,
CREATOR,
CREATE_TIME,
UPDATOR,
UPDATE_TIME
)?VALUES?(
getSeqByName('SEQ_FSP_BUS_INT_REPORT'),
#{businessDay,?jdbcType=VARCHAR},
#{loanNo,?jdbcType=VARCHAR},
#{cifNo,?jdbcType=VARCHAR},
#{cifName,?jdbcType=VARCHAR},
#{loanType,?jdbcType=VARCHAR},
#{repayWay,?jdbcType=VARCHAR},
#{termType,?jdbcType=VARCHAR},
#{vouchWay,?jdbcType=VARCHAR},
#{channelType,?jdbcType=VARCHAR},
#{loanBeginDate,?jdbcType=DATE},
#{loanEndDate,?jdbcType=DATE},
#{rateBeginDate,?jdbcType=DATE},
#{rateEndDate,?jdbcType=DATE},
#{amt,?jdbcType=NUMERIC},
#{rateType,?jdbcType=VARCHAR},
#{rate,?jdbcType=NUMERIC},
#{rateAmt,?jdbcType=NUMERIC},
#{payType,?jdbcType=VARCHAR},
#{reportOutFlag,?jdbcType=VARCHAR},
#{reportOutDate,?jdbcType=DATE},
#{reportOutAmt,?jdbcType=NUMERIC},
#{creator,?jdbcType=VARCHAR},
#{createTime,?jdbcType=DATE},
#{updator,?jdbcType=VARCHAR},
#{updateTime,?jdbcType=DATE}
)
</foreach>
SELECT?1?FROM?DUAL
</insert>
2.dao中接口如下定義：
void?batchSave(@Param("list")?List<BusinessInterestReport>?list);