oracle merge into 实例

ispsh

　　Merge into 详细介绍
　　MERGE语句是 oracle 9i 新增的语法，用来合并 UPDATE 和 INSERT 语句。
　　通过MERGE语句，根据一张表或子查询的连接条件对另外一张表进行查询，
　　连接条件匹配上的进行UPDATE，无法匹配的执行INSERT。
　　这个语法仅需要一次全表扫描就完成了全部工作，执行效率要高于INSERT＋UPDATE。
　　
　　drop table t;

　　CREATE TABLE T AS SELECT ROWNUM>　　drop table t1;
　　CREATE TABLE T1 AS

　　SELECT ROWNUM>　　MERGE INTO T1 USING T
　　ON (T.OWNER = T1.OWNER AND T.OBJECT_NAME = T1.TABLE_NAME AND T.OBJECT_TYPE = T1.OBJECT_TYPE)
　　WHEN MATCHED THEN UPDATE SET T1.ID = T.ID
　　WHEN NOT MATCHED THEN INSERT VALUES (T.ID, T.OWNER, T.OBJECT_NAME, T.OBJECT_TYPE);
　　--insert后面不写表示插入全部列
　　
　　MERGE INTO T1 USING T
　　ON (T.OWNER = T1.OWNER AND T.OBJECT_NAME = T1.TABLE_NAME)
　　WHEN MATCHED THEN UPDATE SET T1.ID = T.ID
　　WHEN NOT MATCHED THEN INSERT VALUES (T.ID, T.OWNER, T.OBJECT_NAME, T.OBJECT_TYPE);
　　--常见错误，连接条件不能获得稳定的行，可以使用下面的用子查询
　　MERGE INTO T1

　　USING (SELECT OWNER, OBJECT_NAME, MAX(ID)>　　ON (T.OWNER = T1.OWNER AND T.OBJECT_NAME = T1.TABLE_NAME)
　　WHEN MATCHED THEN UPDATE SET T1.ID = T.ID
　　WHEN NOT MATCHED THEN INSERT VALUES (T.ID, T.OWNER, T.OBJECT_NAME);

　　SELECT>　　MINUS
　　SELECT * FROM T1;
　　drop table subs;
　　create table subs(
　　msid number(9),
　　ms_type char(1),
　　areacode number(3)
　　);
　　drop table acct;
　　create table acct(
　　msid number(9),
　　bill_month number(6),
　　areacode number(3),
　　fee number(8,2) default 0.00
　　);
　　insert into subs values(905310001,0,531);
　　insert into subs values(905320001,1,532);
　　insert into subs values(905330001,2,533);
　　commit;
　　merge into acct a --操作的表
　　using subs b on (a.msid=b.msid) --使用原始数据来源的表，并且制定条件，条件必须有括号
　　when matched then
　　update set a.areacode=b.areacode
　　--当匹配的时候，执行update操作，和直接update的语法不一样，不需要制定表名
　　when not matched then
　　--当不匹配的时候，执行insert操作，也不需要制定表名，若指定字段插入，
　　--则在insert后用括号标明，不指定是全部插入
　　insert(msid,bill_month,areacode) values(b.msid,'200702',b.areacode);
　　--另外，MERGE语句的UPDATE不能修改用于连接的列，否则会报错
　　select * from acct;
　　select * from subs;
　　--10g新特性，单个操作
　　merge into acct a
　　using subs b on(a.msid=b.msid)
　　when not matched then
　　--只有单个not matched的时候，只做插入，不做更新，只有单个matched的时候，只做更新操作
　　     insert(a.msid,a.bill_month,a.areacode) values(b.msid,'200702',b.areacode);
　　     update acct set areacode=800 where msid=905320001;
　　     delete from acct where areacode=533 or areacode=531;
　　     insert into acct values(905320001,'200702',800,0.00);
　　--删除重复行
　　delete from subs b where b.rowid