ORACLE 10g merge into usage, oraclemerge

ORACLE 10g merge into usage, oraclemerge
Before Oracle 10 Gb, the merge Statement supports two simple usage methods: matching and updating and inserting non-matching. In 10 Gb, Oracle enhanced the merge statement and added the condition options and DELETE operations. Next I will use a demo to briefly introduce the merge enhancement in 10g and the usage of merge before 10g.
　
For more information, see Oracle SQL Reference. I will test it in the following environment.

Create subs and acct tables

create table subs(       msid     number(9),       ms_type  char(1),       areacode number(3));create table acct(       msid       number(9),       bill_month number(6),       areacode   number(3),       fee        number(8,2) default 0.00);

Insert data

insert into subs values(905310001,0,531);insert into subs values(905320001,1,532);insert into subs values(905330001,2,533);commit

Syntax

-- Syntax: merge [into [schema.] table [t_alias] using [schema.] {table | view | subquery} [t_alias] on (condition) when matched then merge_update_clause when not matched then merge_insert_clause;

Test

--- Matched: update not matched: The inserted two can be executed synchronously or only if one condition is merge into acct a using subs B on (. msid = B. msid) when matched then update set. areacode = 22 when not matched then insert (msid, bill_month, areacode) values (B. msid, '000000', B. areacode); commit

Enhanced conditional query operations

merge into acct a   using subs b      on (a.msid = b.msid)         when matched then            update set a.areacode = 22 where b.ms_type = 0         when not matched then            insert (msid, bill_month, areacode) values (b.msid, '200702', b.areacode) where b.ms_type = 0;commit

Enhanced delete operations

merge into acct a   using subs b      on (a.msid = b.msid)         when matched then            update set a.areacode = 22            delete where (b.ms_type != 0);commit