Oracle 9i数据库.docx

Oracle 9i数据库.docx

《Oracle 9i数据库.docx》由会员分享，可在线阅读，更多相关《Oracle 9i数据库.docx（28页珍藏版）》请在冰豆网上搜索。

Oracle9i数据库

Oracle9i/10gsqlMERGE详解

今天在项目中遇到了一个新的知识点，特此记录。

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

 MERGEINTOCR_FAVORITEcf

USING（selectcount（*）co

        fromCR_FAVORITEcf

       wherecf.USER_ID='"+userId+"'

         andcf.IS_VALID=1

         andcf.COMPANY_CODE="+companyCode+"

         andcf.TIMEPOINT=to_date（'"+timepoint+"','yyyy-MM-dd'）

         andcf.CR_INDUSTRY_CODE='"+industryCode+"'）b

ON（b.co<>0）

WHENMATCHEDTHEN

 UPDATE

    SETcf.GRADE        ='"+grade+"',

        cf.UPDATETIME   =to_date（'"+updatetime+"',

                                   'yyyy-MM-ddHH24:

MI:

SS'）,

        cf.RATING_DETAIL=empty_clob（）

  wherecf.USER_ID='"+userId+"'

    andcf.IS_VALID=1

    andcf.COMPANY_CODE="+companyCode+"

    andcf.TIMEPOINT=to_date（'"+timepoint+"','yyyy-MM-dd'）

    andcf.CR_INDUSTRY_CODE='"+industryCode+"'

WHENNOTMATCHEDTHEN

 INSERT

   （cf.ID,

    cf.USER_ID,

    cf.COMPANY_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-ddHH24:

MI:

SS'）,

    empty_clob（）,

    '"+industryCode+"'）

以下为从网络上搜集的几篇比较好的关于“oracle MERGE”的文章：

Oracle10g中对Merge语句的增强1502,2007

url:

在Oracle10g之前，merge语句支持匹配更新和不匹配插入2种简单的用法，在10g中Oracle对merge语句做了增强，增加了条件选项和DELETE操作。

下面我通过一个demo来简单介绍一下10g中merge的增强和10g前merge的用法。

参考Oracle的SQLReference，大家可以看到MergeStatement的语法如下：

MERGE[hint]INTO[schema.]table[t_alias]USING[schema.] 

{table|view|subquery}[t_alias]ON（condition） 

WHENMATCHEDTHENmerge_update_clause 

WHENNOTMATCHEDTHENmerge_insert_clause;

下面我在windowsxp下10.2.0.1版本上做一个测试看看

SQL>select*fromv$version;

BANNER

----------------------------------------------------------------

OracleDatabase10gEnterpriseEditionRelease10.2.0.1.0-Prod

PL/SQLRelease10.2.0.1.0-Production

CORE10.2.0.1.0Production

TNSfor32-bitWindows:

Version10.2.0.1.0-Production

NLSRTLVersion10.2.0.1.0-Production

SQL>

一、创建测试用的表

SQL>createtablesubs（msidnumber（9）,

2ms_typechar

（1）,

3areacodenumber（3）

4）;

表已创建。

SQL>createtableacct（msidnumber（9）,

2bill_monthnumber（6）,

3areacodenumber（3）,

4feenumber（8,2）default0.00）;

表已创建。

SQL>

SQL>insertintosubsvalues（905310001,0,531）;

已创建1行。

SQL>insertintosubsvalues（905320001,1,532）;

已创建1行。

SQL>insertintosubsvalues（905330001,2,533）;

已创建1行。

SQL>commit;

提交完成。

SQL>

二、下面先演示一下merge的基本功能

1）matched和notmatchedclauses同时使用

mergeintoaccta

usingsubsbon（a.msid=b.msid）

whenMATCHEDthen

updateseta.areacode=b.areacode

whenNOTMATCHEDthen

insert（msid,bill_month,areacode）

values（b.msid,'200702',b.areacode）;

2）只有notmatchedclause,也就是只插入不更新

mergeintoaccta

usingsubsbon（a.msid=b.msid）

whenNOTMATCHEDthen

insert（msid,bill_month,areacode）

values（b.msid,'200702',b.areacode）;

3）只有matchedclause,也就是只更新不插入

mergeintoaccta

usingsubsbon（a.msid=b.msid）

whenMATCHEDthen

updateseta.areacode=b.areacode

ConnectedtoOracleDatabase10gEnterpriseEditionRelease10.2.0.1.0

Connectedasstudy

SQL>select*fromsubs;

MSIDMS_TYPEAREACODE

-------------------------

9053100010531

9053200011532

9053300012533

SQL>select*fromacct;

MSIDBILL_MONTHAREACODEFEE

--------------------------------------

SQL>

SQL>mergeintoaccta

2usingsubsbon（a.msid=b.msid）

3whenMATCHEDthen

4updateseta.areacode=b.areacode

5whenNOTMATCHEDthen

6insert（msid,bill_month,areacode）

7values（b.msid,'200702',b.areacode）;

Done

SQL>select*fromacct;

MSIDBILL_MONTHAREACODEFEE

--------------------------------------

9053200012007025320.00

9053300012007025330.00

9053100012007025310.00

SQL>insertintosubsvalues（905340001,3,534）;

1rowinserted

SQL>select*fromsubs;

MSIDMS_TYPEAREACODE

-------------------------

9053400013534

9053100010531

9053200011532

9053300012533

SQL>

SQL>mergeintoaccta

2usingsubsbon（a.msid=b.msid）

3whenNOTMATCHEDthen

4insert（msid,bill_month,areacode）

5values（b.msid,'200702',b.areacode）;

Done

SQL>select*fromacct;

MSIDBILL_MONTHAREACODEFEE

--------------------------------------

9053200012007025320.00

9053300012007025330.00

9053100012007025310.00

9053400012007025340.00

SQL>updatesubssetareacode=999;

4rowsupdated

SQL>select*fromsubs;

MSIDMS_TYPEAREACODE

-------------------------

9053400013999

9053100010999

9053200011999

9053300012999

SQL>select*fromacct;

MSIDBILL_MONTHAREACODEFEE

--------------------------------------

9053200012007025320.00

9053300012007025330.00

9053100012007025310.00

9053400012007025340.00

SQL>

SQL>mergeintoaccta

2usingsubsbon（a.msid=b.msid）

3whenMATCHEDthen

4updateseta.areacode=b.areacode;

Done

SQL>select*fromacct;

MSIDBILL_MONTHAREACODEFEE

--------------------------------------

9053200012007029990.00

9053300012007029990.00

9053100012007029990.00

9053400012007029990.00

SQL>

三、10g中增强一：

条件操作

1）matched和notmatchedclauses同时使用

mergeintoaccta

usingsubsbon（a.msid=b.msid）

whenMATCHEDthen

updateseta.areacode=b.areacode

whereb.ms_type=0

whenNOTMATCHEDthen

insert（msid,bill_month,areacode）

values（b.msid,'200702',b.areacode）

whereb.ms_type=0;

2）只有notmatchedclause,也就是只插入不更新

mergeintoaccta

usingsubsbon（a.msid=b.msid）

whenNOTMATCHEDthen

insert（msid,bill_month,areacode）

values（b.msid,'200702',b.areacode）

whereb.ms_type=0;

3）只有matchedclause,也就是只更新不插入

mergeintoaccta

usingsubsbon（a.msid=b.msid）

whenMATCHEDthen

updateseta.areacode=b.areacode

whereb.ms_type=0;

ConnectedtoOracleDatabase10gEnterpriseEditionRelease10.2.0.1.0

Connectedasstudy

SQL>select*fromsubs;

MSIDMS_TYPEAREACODE

-------------------------

9053100010531

9053200011532

9053300012533

SQL>select*fromacct;

MSIDBILL_MONTHAREACODEFEE

--------------------------------------

SQL>

SQL>mergeintoaccta

2usingsubsbon（a.msid=b.msid）

3whenMATCHEDthen

4updateseta.areacode=b.areacode

5whereb.ms_type=0

6whenNOTMATCHEDthen

7insert（msid,bill_month,areacode）

8values（b.msid,'200702',b.areacode）

9whereb.ms_type=0;

Done

SQL>select*fromacct;

MSIDBILL_MONTHAREACODEFEE

--------------------------------------

9053100012007025310.00

SQL>insertintosubsvalues（905360001,0,536）;

1rowinserted

SQL>select*fromsubs;

MSIDMS_TYPEAREACODE

-------------------------

9053600010536

9053100010531

9053200011532

9053300012533

SQL>

SQL>mergeintoaccta

2usingsubsbon（a.msid=b.msid）

3whenNOTMATCHEDthen

4insert（msid,bill_month,areacode）

5values（b.msid,'200702',b.areacode）

6whereb.ms_type=0;

Done

SQL>select*fromacct;

MSIDBILL_MONTHAREACODEFEE

--------------------------------------

9053100012007025310.00

9053600012007025360.00

SQL>updatesubssetareacode=888wherems_type=0;

2rowsupdated

SQL>select*fromsubs;

MSIDMS_TYPEAREACODE

-------------------------

9053600010888

9053100010888

9053200011532

9053300012533

SQL>select*fromacct;

MSIDBILL_MONTHAREACODEFEE

--------------------------------------

9053100012007025310.00

9053600012007025360.00

SQL>

SQL>mergeintoaccta

2usingsubsbon（a.msid=b.msid）

3whenMATCHEDthen

4updateseta.areacode=b.areacode

5whereb.ms_type=0;

Done

SQL>select*fromacct;

MSIDBILL_MONTHAREACODEFEE

--------------------------------------

9053100012007028880.00

9053600012007028880.00

SQL>

四、10g中增强二：

删除操作

AnoptionalDELETEWHEREclausecanbeusedtocleanupaftera

mergeoperation.OnlythoserowswhichmatchboththeONclause

andtheDELETEWHEREclausearedeleted.

mergeintoaccta

usingsubsbon（a.msid=b.msid）

whenMATCHEDthen

updateseta.areacode=b.areacode

deletewhere（b.ms_type!

=0）;

SQL>select*fromsubs;

MSIDMS_TYPEAREACODE

-------------------------

9053100010531

9053200011532

9053300012533

SQL>select*fromacct;

MSIDMS_TYPEAREACODE

-------------------------

9053100010531

9053200011532

9053300012533

SQL>

SQL>mergeintoaccta

2usingsubsbon（a.msid=b.msid）

3whenMATCHEDthen

4updateseta.areacode=b.areacode

5deletewhere（b.ms_type!

=0）;

Done

SQL>select*fromacct;

MSIDMS_TYPEAREACODE

-------------------------

9053100010531

SQL>

更为详尽的语法，请参考OracleSQLReference手册！

详解Oracle10g中MERGE功能的增强

url:

Oracle9i引入了MERGE命令,你能够在一个SQL语句中对一个表同时执行inserts和updates操作.MERGE命令从一个或多个数据源中选择行来updating或inserting到一个或多个表.在Oracle 10g中MERGE有如下一些改进：

1、UPDATE或INSERT子句是可选的

2、UPDATE和INSERT子句可以加WHERE子句

3、在ON条件中使用常量过滤谓词来insert所有的行到目标表中,不需要连接源表和目标表

4、UPDATE子句后面可以跟DELETE子句来去除一些不需要的行

首先创建示例表：

createtablePRODUCTS

（

PRODUCT_IDINTEGER,

PRODUCT_NAMEVARCHAR2（60）,

CATEGORYVARCHAR2（60）

）;

insertintoPRODUCTSvalues（1501,'VIVITAR35MM','ELECTRNCS'）;

insertintoPRODUCTSvalues（1502,'OLYMPUSIS50','ELECTRNCS'）;

insertintoPRODUCTSvalues（1600,'PLAYGYM','TOYS'）;

insertintoPRODUCTSvalues（1601,'LAMAZE','TOYS'）;

insertintoPRODUCTSvalues（1666,'HARRYPOTTER','DVD'）;

commit;

createtableNEWPRODUCTS

（

PRODUCT_IDINTEGER,

PRODUCT_NAMEVARCHAR2（60）,

CATEGORYVARCHAR2（60）

）;

insert