Oracle 中MERGE语句的用法(转载)
http://blog.youkuaiyun.com/lichkui/article/details/4306299
MERGE INTO TD_CART_ITEM T1
USING (SELECT ? AS F_ITEM_ID,
? AS F_ITEM_TYPE,
? AS F_CHANNEL_ID,
? AS F_CUSTOMER_ID,
? AS F_QUANTITY,
case
when trim(?) is null then
'0'
else
trim(?)
end AS F_EXCHANGE_ID,
case
when trim(?) is null then
'0'
else
trim(?)
end AS F_EXCHANGE_NUM
FROM DUAL) T2
ON (T1.F_CUSTOMER_ID = T2.F_CUSTOMER_ID AND T1.F_ITEM_ID = T2.F_ITEM_ID AND T1.F_CHANNEL_ID = T2.F_CHANNEL_ID and t1.f_item_type = t2.F_ITEM_TYPE and ((t1.f_exchange_id = t2.f_exchange_id and t1.f_exchange_num = t2.F_EXCHANGE_NUM) or (t1.f_exchange_id is null and t1.f_exchange_num is null)))
WHEN MATCHED THEN
UPDATE
SET T1.F_QUANTITY = T1.F_QUANTITY + T2.F_QUANTITY
WHERE T1.F_CUSTOMER_ID = T2.F_CUSTOMER_ID
AND T1.F_ITEM_ID = T2.F_ITEM_ID
AND T1.F_CHANNEL_ID = T2.F_CHANNEL_ID
and t1.f_item_type = t2.F_ITEM_TYPE
and ((t1.f_exchange_id = t2.f_exchange_id and
t1.f_exchange_num = t2.F_EXCHANGE_NUM) or
(t1.f_exchange_id is null and t1.f_exchange_num is null))
WHEN NOT MATCHED THEN
INSERT
(F_ITEM_ID,
F_QUANTITY,
F_CUSTOMER_ID,
F_ITEM_TYPE,
F_CHANNEL_ID,
F_EXCHANGE_ID,
F_EXCHANGE_NUM)
VALUES
(T2.F_ITEM_ID,
T2.F_QUANTITY,
T2.F_CUSTOMER_ID,
T2.F_ITEM_TYPE,
T2.F_CHANNEL_ID,
T2.F_EXCHANGE_ID,
T2.F_EXCHANGE_NUM);
merge 用法