Oracle 9i数据库

Oracle 9i/10g sql MERGE详解今天在项目中遇到了一个新的知识点，特此记录。

在项目的开发中，我看到有位同志使用Oracle的MERGE 进行了“没有数据则执行添加操作，有数据则在原有数据基础上进行更新操作”。

MERGE INTO CR_FAVORITE cfUSING (select count(*) cofrom CR_FAVORITE cfwhere ER_ID = '"+userId+"'and cf.IS_VALID = 1and PANY_CODE = "+companyCode+"and cf.TIMEPOINT = to_date('"+timepoint+"', 'yyyy-MM-dd')and cf.CR_INDUSTRY_CODE = '"+industryCode+"') bON (b.co <> 0)WHEN MATCHED THENUPDATESET cf.GRADE = '" + grade + "',cf.UPDATETIME = to_date('" + updatetime + "','yyyy-MM-dd HH24:MI:SS'),cf.RATING_DETAIL = empty_clob()where ER_ID = '" + userId + "'and cf.IS_VALID = 1and PANY_CODE = " + companyCode + "and cf.TIMEPOINT = to_date('" + timepoint + "', 'yyyy-MM-dd')and cf.CR_INDUSTRY_CODE = '" + industryCode + "'WHEN NOT MATCHED THENINSERT(cf.ID,ER_ID,PANY_CODE,GRADE,cf.TIMEPOINT,cf.UPDATETIME,cf.RATING_DETAIL,cf.CR_INDUSTRY_CODE)VALUES(CR_FAVORITE_ID_SEQ.NEXTVAL,'"+userId+"',"+companyCode+",'"+grade+"',to_date('"+timepoint+"', 'yyyy-MM-dd'),to_date('"+updatetime+"', 'yyyy-MM-dd HH24:MI:SS'),empty_clob(),'"+industryCode+"')以下为从网络上搜集的几篇比较好的关于“oracle MERGE”的文章：Oracle 10g中对Merge语句的增强15 02, 2007url: /post/11835/263865在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_clauseWHEN 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 - Prod PL/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 ausing subs b on (a.msid=b.msid)when MATCHED thenupdate set a.areacode=b.areacodewhen NOT MATCHED theninsert(msid,bill_month,areacode)values(b.msid,'200702',b.areacode);2) 只有not matched clause,也就是只插入不更新merge into acct ausing subs b on (a.msid=b.msid)when NOT MATCHED theninsert(msid,bill_month,areacode)values(b.msid,'200702',b.areacode);3) 只有matched clause, 也就是只更新不插入merge into acct ausing subs b on (a.msid=b.msid)when MATCHED thenupdate set a.areacode=b.areacodeConnected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 Connected as studySQL> select * from subs;MSID MS_TYPE AREACODE---------- ------- --------905310001 0 531905320001 1 532905330001 2 533SQL> select * from acct;MSID BILL_MONTH AREACODE FEE---------- ---------- -------- ----------SQL>SQL> merge into acct a2 using subs b on (a.msid=b.msid)3 when MATCHED then4 update set a.areacode=b.areacode5 when NOT MATCHED then6 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.00905330001 200702 533 0.00905310001 200702 531 0.00SQL> insert into subs values(905340001,3,534);1 row insertedSQL> select * from subs;MSID MS_TYPE AREACODE---------- ------- --------905340001 3 534905310001 0 531905320001 1 532905330001 2 533SQL>SQL> merge into acct a2 using subs b on (a.msid=b.msid)3 when NOT MATCHED then4 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.00905330001 200702 533 0.00905310001 200702 531 0.00905340001 200702 534 0.00SQL> update subs set areacode=999;4 rows updatedSQL> select * from subs;MSID MS_TYPE AREACODE---------- ------- --------905340001 3 999905310001 0 999905320001 1 999905330001 2 999SQL> select * from acct;MSID BILL_MONTH AREACODE FEE---------- ---------- -------- ----------905320001 200702 532 0.00905330001 200702 533 0.00905310001 200702 531 0.00905340001 200702 534 0.00SQL>SQL> merge into acct a2 using subs b on (a.msid=b.msid)3 when MATCHED then4 update set a.areacode=b.areacode; DoneSQL> select * from acct;MSID BILL_MONTH AREACODE FEE---------- ---------- -------- ----------905320001 200702 999 0.00905330001 200702 999 0.00905310001 200702 999 0.00905340001 200702 999 0.00 SQL>三、10g中增强一：条件操作1) matched 和not matched clauses 同时使用merge into acct ausing subs b on (a.msid=b.msid)when MATCHED thenupdate set a.areacode=b.areacodewhere b.ms_type=0when NOT MATCHED theninsert(msid,bill_month,areacode)values(b.msid,'200702',b.areacode)where b.ms_type=0;2) 只有not matched clause,也就是只插入不更新merge into acct ausing subs b on (a.msid=b.msid)when NOT MATCHED theninsert(msid,bill_month,areacode)values(b.msid,'200702',b.areacode)where b.ms_type=0;3) 只有matched clause, 也就是只更新不插入merge into acct ausing subs b on (a.msid=b.msid)when MATCHED thenupdate set a.areacode=b.areacodewhere 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 531905330001 2 533SQL> select * from acct;MSID BILL_MONTH AREACODE FEE---------- ---------- -------- ----------SQL>SQL> merge into acct a2 using subs b on (a.msid=b.msid)3 when MATCHED then4 update set a.areacode=b.areacode5 where b.ms_type=06 when NOT MATCHED then7 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.00 SQL> insert into subs values(905360001,0,536);1 row insertedSQL> select * from subs;MSID MS_TYPE AREACODE---------- ------- --------905310001 0 531905320001 1 532905330001 2 533SQL>SQL> merge into acct a2 using subs b on (a.msid=b.msid)3 when NOT MATCHED then4 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.00905360001 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 888905310001 0 888905320001 1 532905330001 2 533MSID BILL_MONTH AREACODE FEE---------- ---------- -------- ----------905310001 200702 531 0.00905360001 200702 536 0.00SQL>SQL> merge into acct a2 using subs b on (a.msid=b.msid)3 when MATCHED then4 update set a.areacode=b.areacode5 where b.ms_type=0;DoneSQL> select * from acct;MSID BILL_MONTH AREACODE FEE---------- ---------- -------- ----------905310001 200702 888 0.00905360001 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 ausing subs b on (a.msid=b.msid)when MATCHED thenupdate set a.areacode=b.areacodedelete where (b.ms_type!=0);MSID MS_TYPE AREACODE---------- ------- --------905310001 0 531905320001 1 532905330001 2 533SQL> select * from acct;MSID MS_TYPE AREACODE---------- ------- --------905310001 0 531905320001 1 532905330001 2 533SQL>SQL> merge into acct a2 using subs b on (a.msid=b.msid)3 when MATCHED then4 update set a.areacode=b.areacode5 delete where (b.ms_type!=0);DoneSQL> select * from acct;MSID MS_TYPE AREACODE---------- ------- --------905310001 0 531SQL>更为详尽的语法，请参考Oracle SQL Reference手册！详解Oracle 10g 中MERGE 功能的增强url: /serial/523893.htmlOracle9i引入了MERGE命令,你能够在一个SQL语句中对一个表同时执行inserts和updates操作. MERGE命令从一个或多个数据源中选择行来updating或inserting到一个或多个表.在Oracle 10g中MERGE有如下一些改进：1、UPDATE或INSERT子句是可选的2、UPDATE和INSERT子句可以加WHERE子句3、在ON条件中使用常量过滤谓词来insert所有的行到目标表中,不需要连接源表和目标表4、UPDATE子句后面可以跟DELETE子句来去除一些不需要的行首先创建示例表：1、可省略的UPDATE或INSERT子句在Oracle 9i, MERGE语句要求你必须同时指定INSERT和UPDATE子句.而在Oracle 10g, 你可以省略UPDATE或INSERT子句中的一个. 下面的例子根据表NEWPRODUCTS 的PRODUCT_ID字段是否匹配来updates表PRODUCTS的信息:在上面例子中, MERGE语句影响到是产品id为1502, 1601和1666的行. 它们的产品名字和种类被更新为表newproducts中的值. 下面例子省略UPDATE子句, 把表NEWPRODUCTS中新的PRODUCT_ID插入到表PRODUCTS中, 对于在两个表中能够匹配上PRODUCT_ID的数据不作任何处理. 从这个例子你能看到PRODUCT_ID=1700的行被插入到表PRODUCTS中.2、带条件的Updates和Inserts子句你能够添加WHERE子句到UPDATE或INSERT子句中去, 来跳过update或insert 操作对某些行的处理. 下面例子根据表NEWPRODUCTS来更新表PRODUCTS数据, 但必须字段CATEGORY也得同时匹配上:在这个例子中, 产品ID为1502,1601和1666匹配ON条件但是1666的category不匹配. 因此MERGE命令只更新两行数据. 下面例子展示了在Updates和Inserts子句都使用WHERE子句:注意由于有WHERE子句INSERT没有插入所有不匹配ON条件的行到表PRODUCTS.3、无条件的Inserts你能够不用连接源表和目标表就把源表的数据插入到目标表中. 这对于你想插入所有行到目标表时是非常有用的. Oracle 10g现在支持在ON条件中使用常量过滤谓词. 举个常量过滤谓词例子ON (1=0). 下面例子从源表插入行到表PRODUCTS, 不检查这些行是否在表PRODUCTS中存在:4、新增加的DELETE子句Oracle 10g中的MERGE提供了在执行数据操作时清除行的选项. 你能够在WHEN MATCHED THEN UPDATE子句中包含DELETE子句. DELETE子句必须有一个WHERE 条件来删除匹配某些条件的行.匹配DELETE WHERE条件但不匹配ON条件的行不会被从表中删除.下面例子验证DELETE子句. 我们从表NEWPRODUCTS中合并行到表PRODUCTS 中, 但删除category为ELECTRNCS的行.产品ID为1502的行从表PRODUCTS中被删除, 因为它同时匹配ON条件和DELETE WHERE条件. 产品ID为1501的行匹配DELETE WHERE条件但不匹配ON条件, 所以它没有被删除. 产品ID为1700 的行不匹配ON条件, 所以被插入表PRODUCTS. 产品ID 为1601和1666的行匹配ON条件但不匹配DELETE WHERE条件, 所以被更新为表NEWPRODUCTS中的值.Oracle中的Merge函数(批量更新/删除)url: /induction/714430.html所有的MIS系统都存在一个同样的需求，就是对于特定的数据，在一次批量操作过程中，如果数据已经存在，则对存在的数据按照现有情况进行更新，如果不存在，则需要加入数据库。