oracle10g的merge功能增强(转)
来源:百度文库 编辑:神马文学网 时间:2024/04/28 05:23:58
oracle10g的merge功能增强(转) =========================================================== 作者: zhouwf0726(http://zhouwf0726.itpub.net)发表于:2007.03.14 16:10分类: oracle开发 出处:http://zhouwf0726.itpub.net/post/9689/271773--------------------------------------------------------------- 在Oracle 10g之前,merge语句支持匹配更新和不匹配插入2种简单的用法,在10g中Oracle对merge语句做了增强,增加了条件选项和DELETE操作。下面我通过一个demo来简单介绍一下10g中merge的增强和10g前merge的用法。 参考Oracle 的SQL Reference,大家可以看到Merge Statement的语法如下:MERGE [hint] 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;下面我在windows xp 下10.2.0.1版本上做一个测试看看SQL> select * from v$version;BANNER----------------------------------------------------------------Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProdPL/SQL Release 10.2.0.1.0 - ProductionCORE 10.2.0.1.0 ProductionTNS for 32-bit Windows: Version 10.2.0.1.0 - ProductionNLSRTL Version 10.2.0.1.0 - ProductionSQL> 一、创建测试用的表SQL> create table subs(msid number(9), 2 ms_type char(1), 3 areacode number(3) 4 );表已创建。SQL> create table acct(msid number(9), 2 bill_month number(6), 3 areacode number(3), 4 fee number(8,2) default 0.00);表已创建。SQL> SQL> insert into subs values(905310001,0,531);已创建 1 行。SQL> insert into subs values(905320001,1,532);已创建 1 行。SQL> insert into subs values(905330001,2,533);已创建 1 行。SQL> commit;提交完成。SQL> 二、下面先演示一下merge的基本功能1) matched 和not matched clauses 同时使用 merge into acct a using subs b on (a.msid=b.msid) when MATCHED then update set a.areacode=b.areacode when NOT MATCHED then insert(msid,bill_month,areacode) values(b.msid,'200702',b.areacode);2) 只有not matched clause,也就是只插入不更新 merge into acct a using subs b on (a.msid=b.msid) when NOT MATCHED then insert(msid,bill_month,areacode) values(b.msid,'200702',b.areacode);3) 只有matched clause, 也就是只更新不插入 merge into acct a using subs b on (a.msid=b.msid) when MATCHED then update set a.areacode=b.areacode Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 Connected as studySQL> select * from subs; MSID MS_TYPE AREACODE---------- ------- -------- 905310001 0 531 905320001 1 532 905330001 2 533SQL> select * from acct; MSID BILL_MONTH AREACODE FEE---------- ---------- -------- ----------SQL> SQL> merge into acct a 2 using subs b on (a.msid=b.msid) 3 when MATCHED then 4 update set a.areacode=b.areacode 5 when NOT MATCHED then 6 insert(msid,bill_month,areacode) 7 values(b.msid,'200702',b.areacode);DoneSQL> select * from acct; MSID BILL_MONTH AREACODE FEE---------- ---------- -------- ---------- 905320001 200702 532 0.00 905330001 200702 533 0.00 905310001 200702 531 0.00SQL> insert into subs values(905340001,3,534);1 row insertedSQL> select * from subs; MSID MS_TYPE AREACODE---------- ------- -------- 905340001 3 534 905310001 0 531 905320001 1 532 905330001 2 533SQL> SQL> merge into acct a 2 using subs b on (a.msid=b.msid) 3 when NOT MATCHED then 4 insert(msid,bill_month,areacode) 5 values(b.msid,'200702',b.areacode);DoneSQL> select * from acct; MSID BILL_MONTH AREACODE FEE---------- ---------- -------- ---------- 905320001 200702 532 0.00 905330001 200702 533 0.00 905310001 200702 531 0.00 905340001 200702 534 0.00SQL> update subs set areacode=999;4 rows updatedSQL> select * from subs; MSID MS_TYPE AREACODE---------- ------- -------- 905340001 3 999 905310001 0 999 905320001 1 999 905330001 2 999SQL> select * from acct; MSID BILL_MONTH AREACODE FEE---------- ---------- -------- ---------- 905320001 200702 532 0.00 905330001 200702 533 0.00 905310001 200702 531 0.00 905340001 200702 534 0.00SQL> SQL> merge into acct a 2 using subs b on (a.msid=b.msid) 3 when MATCHED then 4 update set a.areacode=b.areacode;DoneSQL> select * from acct; MSID BILL_MONTH AREACODE FEE---------- ---------- -------- ---------- 905320001 200702 999 0.00 905330001 200702 999 0.00 905310001 200702 999 0.00 905340001 200702 999 0.00SQL> 三、10g中增强一:条件操作1) matched 和not matched clauses 同时使用 merge into acct a using subs b on (a.msid=b.msid) when MATCHED then update set a.areacode=b.areacode 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;2) 只有not matched clause,也就是只插入不更新 merge into acct a using subs b on (a.msid=b.msid) when NOT MATCHED then insert(msid,bill_month,areacode) values(b.msid,'200702',b.areacode) where b.ms_type=0;3) 只有matched clause, 也就是只更新不插入 merge into acct a using subs b on (a.msid=b.msid) when MATCHED then update set a.areacode=b.areacode where b.ms_type=0; Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 Connected as studySQL> select * from subs; MSID MS_TYPE AREACODE---------- ------- -------- 905310001 0 531 905320001 1 532 905330001 2 533SQL> select * from acct; MSID BILL_MONTH AREACODE FEE---------- ---------- -------- ----------SQL> SQL> merge into acct a 2 using subs b on (a.msid=b.msid) 3 when MATCHED then 4 update set a.areacode=b.areacode 5 where b.ms_type=0 6 when NOT MATCHED then 7 insert(msid,bill_month,areacode) 8 values(b.msid,'200702',b.areacode) 9 where b.ms_type=0;DoneSQL> select * from acct; MSID BILL_MONTH AREACODE FEE---------- ---------- -------- ---------- 905310001 200702 531 0.00SQL> insert into subs values(905360001,0,536);1 row insertedSQL> select * from subs; MSID MS_TYPE AREACODE---------- ------- -------- 905360001 0 536 905310001 0 531 905320001 1 532 905330001 2 533SQL> SQL> merge into acct a 2 using subs b on (a.msid=b.msid) 3 when NOT MATCHED then 4 insert(msid,bill_month,areacode) 5 values(b.msid,'200702',b.areacode) 6 where b.ms_type=0;DoneSQL> select * from acct; MSID BILL_MONTH AREACODE FEE---------- ---------- -------- ---------- 905310001 200702 531 0.00 905360001 200702 536 0.00SQL> update subs set areacode=888 where ms_type=0;2 rows updatedSQL> select * from subs; MSID MS_TYPE AREACODE---------- ------- -------- 905360001 0 888 905310001 0 888 905320001 1 532 905330001 2 533SQL> select * from acct; MSID BILL_MONTH AREACODE FEE---------- ---------- -------- ---------- 905310001 200702 531 0.00 905360001 200702 536 0.00SQL> SQL> merge into acct a 2 using subs b on (a.msid=b.msid) 3 when MATCHED then 4 update set a.areacode=b.areacode 5 where b.ms_type=0;DoneSQL> select * from acct; MSID BILL_MONTH AREACODE FEE---------- ---------- -------- ---------- 905310001 200702 888 0.00 905360001 200702 888 0.00SQL> 四、10g中增强二:删除操作An optional DELETE WHERE clause can be used to clean up after a merge operation. Only those rows which match both the ON clause and the DELETE WHERE clause are deleted. merge into acct a using subs b on (a.msid=b.msid) when MATCHED then update set a.areacode=b.areacode delete where (b.ms_type!=0); SQL> select * from subs; MSID MS_TYPE AREACODE---------- ------- -------- 905310001 0 531 905320001 1 532 905330001 2 533SQL> select * from acct; MSID MS_TYPE AREACODE---------- ------- -------- 905310001 0 531 905320001 1 532 905330001 2 533SQL> SQL> merge into acct a 2 using subs b on (a.msid=b.msid) 3 when MATCHED then 4 update set a.areacode=b.areacode 5 delete where (b.ms_type!=0);DoneSQL> select * from acct; MSID MS_TYPE AREACODE---------- ------- -------- 905310001 0 531SQL> 更为详尽的语法,请参考Oracle SQL Reference手册!
oracle10g的merge功能增强(转)
详解 Oracle 10g 中 MERGE 功能的增强
linux+oracle10g的部署
oracle10g的配置--10G
如何增强心脏功能
使用 ActiveScaffold 增强 Ruby on Rails 的功能
Piky Basket增强系统的复制和粘贴功能
城市综合竞争力的本质特征:增强综合服务功能
【转】 , , and
Oracle_安装配置_OEL5上安装oracle10g碰到的问题
增强自信的方法:转
销魂的女声-merge of equals–intoxicating (2009)
用ClipX连续复制后再粘贴,增强系统的复制功能
中华电脑书库--增强网络功能的另类招法—修改注册表--在线教程,电脑教程,电脑文摘。
推荐:总算看到一款带增强现实功能的地图软件
增强免疫功能、去火排毒、降脂减肥的快手清素菜粥
常吃豌豆,增强人体免疫功能!
常吃豌豆 增强人体免疫功能
SharePoint 2010新增功能:通知增强
巧改注册表来增强网络功能
增强脏腑功能减少鼻子过敏
增强脏腑功能减少鼻子过敏
增强脏腑功能减少鼻子过敏
解说保健功能之增强免疫力