MERGE语句是Oracle9i新增的语法,用来合并UPDATE和INSERT语句。
MERGE INTO tabel1 A --作用表A
USING (SELECT '$acctMonth$' ACCT_MONTH, --关联表
'$loginId$' LOGIN_ID,
'$kpiId$' KPI_ID
FROM DUAL) B
ON (A.ACCT_MONTH = B.ACCT_MONTH --条件
AND A.LOGIN_ID = B.LOGIN_ID
AND A.KPI_ID = B.KPI_ID)
WHEN MATCHED THEN --表A存在符合条件的记录,则执行update
UPDATE
SET A.KPI_VALUE = '$kpiValue$',
A.VALUE_REASON = '$valueReson$',
A.VALUE_BASIS = '$valueBasis$',
A.REMARK = '$remark$',
A.MDF_LOGIN_ID = '$operatorId$',
A.MDF_DATE = SYSDATE
WHEN NOT MATCHED THEN 不符合,则执行insert
INSERT
VALUES
(B.ACCT_MONTH,
B.LOGIN_ID,
B.KPI_ID,
'$kpiValue$',
'$valueReson$',
'$valueBasis$',
'$remark$',
'$operatorId$',
SYSDATE)