MERGE 表，匹配时不执行任何操作

我有一张桌子DOMAINS在 2 个不同的列模式中ID, NAME,CODE,DESCRIPTION.

For any NAME存在于新模式中，它应该使用现有的ID没有任何合并；对于那些新的NAME记录，它应该插入ID来自旧模式。

MERGE INTO domains a
     USING ( SELECT id, name, code, description 
               FROM <Old Schema 6.1>.domains@db_mig_61_to_74 ) b
        ON ( a.name = b.name )
      WHEN MATCHED **<do nothing>**
      WHEN NOT MATCHED THEN INSERT( a.id, a.name, a.code, a.description ) 
                            VALUES( b.id, b.name, b.code, b.description );

我该如何解释这部分do nothing在上面的查询中？

对于您的情况，无需使用该部分：

WHEN MATCHED THEN UPDATE ...

( using WHEN MATCHED THEN UPDATE SET a.id = a.id被接受（Oracle 不会投掷）但没有任何影响，因此，这种用法是多余的，因为您不想为匹配的情况更改任何内容。 )

如果您想更改，请添加

WHEN MATCHED THEN UPDATE SET a.id = b.id

before WHEN NOT MATCHED THEN INSERT...

( 例如Oracle支持WHEN MATCHED THEN UPDATE句法。参考the Demo below )

针对当前案例继续执行以下操作：

SQL> CREATE TABLE domains( 
                           id          INT, 
                           name        VARCHAR2(50), 
                           code        VARCHAR2(50), 
                           description VARCHAR2(50)
                         );

SQL> INSERT INTO domains VALUES(1,'Domain A','D.A.','This is Domain A');

SQL> MERGE INTO domains a USING 
     (SELECT 2 id, 'Domain A' name, 'D.A.' code, 'This is Domain A' description 
        FROM domains) b
          ON ( a.name = b.name )
        WHEN NOT MATCHED THEN INSERT( a.id, a.name, a.code, a.description ) 
                              VALUES( b.id, b.name, b.code, b.description );

SQL> SELECT * FROM domains;

ID  NAME        CODE    DESCRIPTION
--  --------   -----  ----------------
1   Domain A    D.A.  This is Domain A

SQL> DELETE domains;

SQL> INSERT INTO domains VALUES(1,'Domain A','D.A.','This is Domain A');
-- we're deleting and inserting the same row again

SQL> MERGE INTO domains a USING       
 (SELECT 2 id, 'Domain B' name, 'D.B.' code, 'This is Domain B' description 
    FROM domains) b
      ON ( a.name = b.name )
    WHEN NOT MATCHED THEN INSERT( a.id, a.name, a.code, a.description ) 
                          VALUES( b.id, b.name, b.code, b.description );


SQL> SELECT * FROM domains;

ID  NAME        CODE    DESCRIPTION
--  --------   -----  ----------------
1   Domain A    D.A.  This is Domain A
2   Domain B    D.B.  This is Domain B

Demo

Btw，后面跟着的部分USING关键字不必是 asubquery, but a table or a view。对当前案例进行评估：

MERGE INTO domains ds       --> "ds" : "domains" source
     USING v_domains dt     --> "dt" : "domains" target
        ON ( ds.name = dt.name )
      WHEN NOT MATCHED THEN INSERT( ds.id, ds.name, ds.code, ds.description ) 
                            VALUES( dt.id, dt.name, dt.code, dt.description )

创建后可以像上面的语句一样使用v_domains浏览

CREATE OR REPLACE VIEW v_domains AS
SELECT 2 id, 'Domain A' name, 'D.A.' code, 'This is Domain A' description 
  FROM domains