merge into route_package_vst_time_price r using
(
select
#{item.unitId jdbcType=NUMERIC} unitId,
#{item.specDate jdbcType=DATE} specDate,
#{item.stockType jdbcType=VARCHAR} stockType,
#{item.stock jdbcType=NUMERIC} stock,
#{item.totalStock jdbcType=NUMERIC} totalStock,
#{item.oversellFlag jdbcType=VARCHAR} oversellFlag,
#{item.adultOnSaleFlag jdbcType=VARCHAR} adultOnSaleFlag,
#{item.childOnSaleFlag jdbcType=VARCHAR} childOnSaleFlag,
#{item.gapOnSaleFlag jdbcType=VARCHAR} gapOnSaleFlag,
#{item.adultSettlementPrice jdbcType=NUMERIC} adultSettlementPrice,
#{item.adultPrice jdbcType=NUMERIC} adultPrice,
#{item.childSettlementPrice jdbcType=NUMERIC} childSettlementPrice,
#{item.childPrice jdbcType=NUMERIC} childPrice,
#{item.gapSettlementPrice jdbcType=NUMERIC} gapSettlementPrice,
#{item.gapPrice jdbcType=NUMERIC} gapPrice,
#{item.aheadBookTime jdbcType=NUMERIC} aheadBookTime,
#{item.bookLimitType jdbcType=VARCHAR} bookLimitType
from dual
)tmp
on (
tmp.unitId = r.UNIT_ID
and tmp.specDate = r.SPEC_DATE
)
when matched THEN
update
r.STOCK_TYPE = tmp.stockType,
r.STOCK = tmp.stock,
r.TOTAL_STOCK = tmp.totalStock,
r.OVERSELL_FLAG = tmp.oversellFlag,
r.ADULT_ON_SALE_FLAG = tmp.adultOnSaleFlag,
r.CHILD_ON_SALE_FLAG = tmp.childOnSaleFlag,
r.GAP_ON_SALE_FLAG = tmp.gapOnSaleFlag,
r.ADULT_SETTLEMENT_PRICE = tmp.adultSettlementPrice,
r.ADULT_PRICE = tmp.adultPrice,
r.CHILD_SETTLEMENT_PRICE = tmp.childSettlementPrice,
r.CHILD_PRICE =tmp.childPrice ,
r.GAP_SETTLEMENT_PRICE = tmp.gapSettlementPrice,
r.GAP_PRICE = tmp.gapPrice,
r.AHEAD_BOOK_TIME = tmp.aheadBookTime,
r.BOOK_LIMIT_TYPE = tmp.bookLimitType,
r.UPDATE_TIME = sysdate
when not matched THEN
insert
TIME_PRICE_ID ,
UNIT_ID ,
SPEC_DATE ,
STOCK_TYPE ,
STOCK ,
TOTAL_STOCK ,
OVERSELL_FLAG ,
ADULT_ON_SALE_FLAG ,
CHILD_ON_SALE_FLAG,
GAP_ON_SALE_FLAG,
ADULT_SETTLEMENT_PRICE,
ADULT_PRICE,
CHILD_SETTLEMENT_PRICE,
CHILD_PRICE,
GAP_SETTLEMENT_PRICE,
GAP_PRICE,
AHEAD_BOOK_TIME,
BOOK_LIMIT_TYPE,
UPDATE_TIME,
CREATE_TIME
route_packagevsttimeprice_seq.nextval,
tmp.unitId,
tmp.specDate ,
tmp.stockType ,
tmp.stock ,
tmp.totalStock,
tmp.oversellFlag ,
tmp.adultOnSaleFlag ,
tmp.childOnSaleFlag ,
tmp.gapOnSaleFlag,
tmp.adultSettlementPrice ,
tmp.adultPrice,
tmp.childSettlementPrice,
tmp.childPrice ,
tmp.gapSettlementPrice ,
tmp.gapPrice,
tmp.aheadBookTime ,
tmp.bookLimitType ,
sysdate,
sysdate