在批处理任务中，可能需要对目标表进行大量 INSERT 或 UPDATE。如果需求是记录不存在就 INSERT，存在就 UPDATE，此时可以使用 MERGE 语句。

    MERGE 语句的语法简单描述如下：

    1. MERGE INTO table_name alias1 
    2. USING (table|view|sub_query) alias2
    3. ON (join condition) 
    4. WHEN MATCHED THEN 
    5.     UPDATE table_name 
    6.     SET col1    = col_val1 , 
    7.          col2       = col2_val 
    8. WHEN NOT MATCHED THEN 
    9.     INSERT (column_list) VALUES (column_values);

    使用示例如下：

    1. obclient> CREATE TABLE t_merge(
    2.     id      number NOT NULL PRIMARY KEY,
    3.     name    varchar2(50)    NOT NULL,
    4.     value   number NOT NULL,
    5.     gmt_create  date NOT NULL DEFAULT sysdate,
    6.     gmt_modified    date NOT NULL DEFAULT sysdate 
    7. );
    8. Query OK, 0 rows affected (0.06 sec)
    10. obclient> INSERT INTO t_merge(id,name,value) values(1,'CN',1),(2,'US',2);
    11. Query OK, 2 rows affected (0.01 sec)
    12. Records: 2  Duplicates: 0  Warnings: 0
    14. obclient> MERGE INTO t_merge t2
    15.  USING (SELECT id,name,value,gmt_create FROM t_insert ) t1
    16.  ON (t2.id=t1.id)
    17.  WHEN MATCHED THEN
    18.      UPDATE SET t2.name=t1.name, t2.value=t1.value, t2.gmt_modified=sysdate
    19.  WHEN NOT MATCHED THEN
    20.      INSERT (t2.id,t2.name,t2.value) values(t1.id, t1.name, t1.value)
    21.  ;
    22. Query OK, 3 rows affected (0.01 sec)
    24. obclient> select * from t_merge;  
    25. +----+------+-------+---------------------+---------------------+
    26. | ID | NAME | VALUE | GMT_CREATE          | GMT_MODIFIED        |
    27. +----+------+-------+---------------------+---------------------+
    28. |  1 | CN   | 10001 | 2020-04-02 17:51:59 | 2020-04-02 17:52:52 |
    29. |  2 | US   | 10002 | 2020-04-02 17:51:59 | 2020-04-02 17:52:52 |
    30. |  3 | EN   | 10003 | 2020-04-02 17:52:52 | 2020-04-02 17:52:52 |
    31. +----+------+-------+---------------------+---------------------+
    32. 3 rows in set (0.01 sec)