Oracle9i新增的Merge是Update和Insert的功能的合集，能夠根據相關匹配條件分別進行Update和Insert操作，一次掃描即可完成兩個任務，提高了系統的效能。

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;

【原】關於Oracle Merge操作的簡單用法

相關文章