merge into 语句写法主要功能是对两个表进行关联,如果存在相等值就更新,

不存在就插入

先看9i写法:

create table test (id number,name varchar2(20));

create table paul (id number,name varchar2(50));

insert into test values(10,'abcd');
insert into paul values(5,'def');
insert into test values(1,'right');
insert into paul values(1,'false');

SQL> select * from paul;

        ID NAME
---------- --------------------------------------------------
         5 def
         1 false
SQL> select * from test;

        ID NAME
---------- --------------------
        10 abcd
         1 right

SQL>
SQL> MERGE INTO test a
2    USING paul b
3      ON (a.id = b.id)
4    WHEN MATCHED THEN
5      UPDATE SET a.name = b.name
6    WHEN NOT MATCHED THEN
7      INSERT (id, name)
8      VALUES (b.id, b.name)
9 ;

Done

SQL> select * from test;

        ID NAME
---------- --------------------
        10 abcd
         1 false
         5 def

SQL>
SQL> MERGE INTO test a
2    USING paul b
3      ON (a.id = b.id)
4   WHEN NOT MATCHED THEN
5      INSERT (id, name)
6      VALUES (b.id, b.name);

MERGE INTO test a
USING paul b
    ON (a.id = b.id)
WHEN NOT MATCHED THEN
    INSERT (id, name)
    VALUES (b.id, b.name)

ORA-00905: missing keyword

SQL>
SQL> MERGE INTO test a
2    USING paul b
3      ON (a.id = b.id)
4    WHEN MATCHED THEN
5      UPDATE SET a.name = b.name;

MERGE INTO test a
USING paul b
    ON (a.id = b.id)
WHEN MATCHED THEN
    UPDATE SET a.name = b.name

ORA-00905: missing keyword

可以看出在9i中对单一的进行insert 或update 分开是不行的

10g 上就可以了:

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as SYS

SQL>
SQL> create table test (id number,name varchar2(20));

Table created
SQL> create table paul (id number,name varchar2(50));

Table created
SQL> insert into test values(10,'abcd');

1 row inserted
SQL> insert into paul values(5,'def');

1 row inserted
SQL> insert into test values(1,'right');

1 row inserted
SQL> insert into paul values(1,'false');

1 row inserted

SQL> commit;

Commit complete

SQL> select * from paul;

        ID NAME
---------- --------------------------------------------------
         5 def
         1 false

SQL> select * from test;

        ID NAME
---------- --------------------
        10 abcd
         1 right

SQL>
SQL> MERGE INTO test a
2    USING paul b
3      ON (a.id = b.id)
4    WHEN MATCHED THEN
5      UPDATE SET a.name = b.name
6    WHEN NOT MATCHED THEN
7      INSERT (id, name)
8      VALUES (b.id, b.name)
9 ;

Done

SQL> select * from test;

        ID NAME
---------- --------------------
        10 abcd
         1 false
         5 def

SQL> rollback;

Rollback complete

SQL>

单独的insert
SQL> MERGE INTO test a
2    USING paul b
3      ON (a.id = b.id)
4   WHEN NOT MATCHED THEN
5      INSERT (id, name)
6      VALUES (b.id, b.name);

Done

SQL> select * from test;

        ID NAME
---------- --------------------
        10 abcd
         1 right
         5 def

SQL> rollback;

Rollback complete
单独的update
SQL>
SQL> MERGE INTO test a
2    USING paul b
3      ON (a.id = b.id)
4    WHEN MATCHED THEN
5      UPDATE SET a.name = b.name;

Done

SQL> select * from test;

        ID NAME
---------- --------------------
        10 abcd
         1 false

还可以增加where 条件和delete条件

Conditional Operations

Conditional inserts and updates are now possible by using a WHERE clause on these statements.

-- Both clauses present.
MERGE INTO test1 a
  USING all_objects b
    ON (a.object_id = b.object_id)
  WHEN MATCHED THEN
    UPDATE SET a.status = b.status
    WHERE  b.status != 'VALID'
  WHEN NOT MATCHED THEN
    INSERT (object_id, status)
    VALUES (b.object_id, b.status)
    WHERE  b.status != 'VALID';

-- No matched clause, insert only.
MERGE INTO test1 a
  USING all_objects b
    ON (a.object_id = b.object_id)
  WHEN NOT MATCHED THEN
    INSERT (object_id, status)
    VALUES (b.object_id, b.status)
    WHERE  b.status != 'VALID';

-- No not-matched clause, update only.
MERGE INTO test1 a
  USING all_objects b
    ON (a.object_id = b.object_id)
  WHEN MATCHED THEN
    UPDATE SET a.status = b.status
    WHERE  b.status != 'VALID';

DELETE Clause

An optional DELETE WHERE clause can be used to clean up after a merge operation. Only those rows which match both the ON clause and the DELETE WHERE clause are deleted.

MERGE INTO test1 a
  USING all_objects b
    ON (a.object_id = b.object_id)
  WHEN MATCHED THEN
    UPDATE SET a.status = b.status
    WHERE  b.status != 'VALID'
  DELETE WHERE (b.status = 'VALID');

oracle 10g merge命令的增强

Conditional Operations

DELETE Clause