oracle 10g merge命令的增強

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');