【原】关于Oracle Merge操作的简单用法

create table SourceTable

(

IDnumber,

Namevarchar2(20),

Property varchar2(20)

)

;

alter table SourceTable add constraint PrimarySourceTable primary key (ID);

create table DestTable

(

IDnumber,

Namevarchar2(20),

Property varchar2(20)

)

;

alter table DestTable add constraint PrimaryDestTable primary key (ID);

create table SurrogateDestTable

(

SurrogateID number,

IDnumber,

Namevarchar2(20),

Property varchar2(20)

)

;

alter table SurrogateDestTable add constraint SurrogatePrimaryDestTable primary key (SurrogateID);

Create index SurrogateDestTableIndexID on SurrogateDestTable(ID);

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;

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;

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;

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;