关于Oracle Merge操作的简单用法

Oracle9i新增的Merge是Update和Insert的功能的合集，能够根据相关匹配条件分别进行Update和Insert操作，一次扫描即可完成两个任务，提高了系统的性能。

[@more@]

1. 创建源数据表

create table SourceTable ( ID number, Name varchar2(20), Property varchar2(20) ) ; alter table SourceTable add constraint PrimarySourceTable primary key (ID);

2. 创建完全覆盖目标表

create table DestTable ( ID number, Name varchar2(20), Property varchar2(20) ) ; alter table DestTable add constraint PrimaryDestTable primary key (ID);

3. 创建全历史记录表

create table SurrogateDestTable ( SurrogateID number, ID number, Name varchar2(20), Property varchar2(20) ) ; alter table SurrogateDestTable add constraint SurrogatePrimaryDestTable primary key (SurrogateID); Create index SurrogateDestTableIndexID on SurrogateDestTable(ID);

4. 创建各个序列

create sequence SourceTableSeq minvalue 1 maxvalue 10000 start with 1 increment by 1; create sequence DestTableSeq minvalue 1 maxvalue 100000 start with 1 increment by 1;

5. 插入三条测试记录

INSERT INTO SourceTable VALUES(SourceTableSeq.nextval,'A','AA'); INSERT INTO SourceTable VALUES(SourceTableSeq.nextval,'B','BB'); INSERT INTO SourceTable VALUES(SourceTableSeq.nextval,'C','CC'); COMMIT;

6. 进行覆盖表merge测试，根据ID进行比较，匹配则更新，不匹配则插入

MERGE INTO DestTable D USING (SELECT ID,Name,Property FROM SourceTable) S ON (D.ID = S.ID) WHEN MATCHED THEN UPDATE SET D.Name = S.Name,D.Property=S.Property WHEN NOT MATCHED THEN INSERT (D.ID,D.Name,D.Property) VALUES (S.ID,S.Name,S.Property); COMMIT;

7. 进行全历史记录merge测试，比较ID和Name,如果一致则更新Property,如果不一致则插入；当然也可以全字段比较，取消Matched部分

MERGE INTO SurrogateDestTable D USING (SELECT ID,Name,Property FROM SourceTable) S ON (D.ID = S.ID AND D.Name=S.Name) WHEN MATCHED THEN UPDATE SET D.Property=S.Property WHEN NOT MATCHED THEN INSERT (D.SURROGATEID,D.ID,D.Name,D.Property) VALUES (DestTableSeq.NextVal,S.ID,S.Name,S.Property); COMMIT;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/8128313/viewspace-977918/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/8128313/viewspace-977918/