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 OperationsConditional inserts and updates are now possible by using a [font=新宋体]WHERE clause on these statements.

[indent]-- 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';[/indent]DELETE ClauseAn optional [font=新宋体]DELETE WHERE clause can be used to clean up after a merge operation. Only those rows which match both the [font=新宋体]ON clause and the [font=新宋体]DELETE WHERE clause are deleted.

[indent]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');[/indent]