merge into

MERGE语句是Oracle9i后才出现的新功能，用来合并UPDATE和INSERT语句。
通过MERGE语句，根据一张表或子查询与另一张表进行关联查询，匹配则UPDATE，否则INSERT。

-- 语法：
MERGE [INTO][schema.]table [alias]
USING [schema.] { table | view | subquery } [alias]
  ON {condition}
WHEN MATCHED THEN UPDATE SET {clause}
WHEN NOT MATCHED THEN INSERT VALUES {clause}


-- 示例：
MERGE INTO BILL_PROFILE b1 USING (
 SELECT c.MSISDN
 FROM VHL_PROFILE a, ATB_PROFILE b, SIM_PROFILE c
 WHERE a.VIN = b.VIN AND b.ID = c.ATB_ID
  AND a.RECORD_STATUS = '1' AND b.RECORD_STATUS = '1' AND c.RECORD_STATUS = '1'
) b2
ON (
 b1.MSISDN = b2.MSISDN
 AND b1.RECORD_STATUS = '1' AND b1.BILL_STATUS = '1' AND to_char(b1.CREATE_TIME, 'yyyymm') = to_char(SYSDATE, 'yyyymm')
)
WHEN MATCHED THEN
  UPDATE SET b1.UPDATE_TIME = SYSDATE
WHEN NOT MATCHED THEN
  INSERT (ID, BILL_CODE, MSISDN, BILL_TYPE, BILL_SOURCE, RECORD_STATUS, BILL_STATUS, APPRAISAL_STATUS, CREATE_TIME, UPDATE_TIME)
  VALUES (sys_guid(), to_char(sysdate,'yyyymmddhh24miss')||lpad(round(dbms_random.value(1, 1000)),4,0), b2.MSISDN, '1', '4', '1', '1', '1', SYSDATE, SYSDATE);