存在性的判断和MERGE语法的使用

 

很多人喜欢用这样的方法来判断是否存在记录：

select count(*) into t_count from t where condition;
if t_count> 0 then ....

这种方法的问题在于：我们需要的仅仅是是否存在，而不是得到总记录数。查询记录总数付出了不必要的性能代价。

引用：http://blog.itpub.net/post/6/9018

两种情况：
1. 如果判断是否存在记录后， 要查询记录中的某些列的信息，或者是决定要对表进行insert/update操作，典型的操作为：
a.

select count(*) into t_count from t where condition;

if t_count> 0 then

   select cols into t_cols from t where condition;

else

   otherstatement;

end;

b.

select count(*) into t_count from t where condition;

if t_count> 0 then

  update ...;

else

  insert ...;

end;

这两种操作，都可以采用直接操作，然后进行例外处理的方式，根本就不进行这个存在性判断！

改写后的a.

begin

  select cols into t_cols from t where condition;

exception

  when no_data_found then begin

    statement-block2;

  end;

  when others then begin

    raise error...

  end;

end;

改写后的b.

update t set ... where condition;

IF SQL%NOTFOUND THEN

  insert into t ...

END IF;

或者：

begin

  insert into t ...

exception

  when DUP_VAL_ON_INDEX then begin

    update t set ...

  end;

end;

这两种方法使用哪一种，取决于你认为哪种情况出现的可能更高。

 

对于b中的写法，可以使用merge语法实现：

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

下面看个具体的例子：

create table t(id number, name varchar2(10));

 

create table tt(id number, name varchar2(10));

 

insert into t(id,name)values(1,'dm');

 

insert into tt(id,name)values(1,'dddddd');

 

merge into t

using tt

on (t.id=tt.id)

when matched then update set t.id=tt.id

when not matched then  insert values(tt.id,tt.name)

ORA-38104:无法更新ON子句中引用的列:"T"."ID"

错误原因是：不允许更新关联的列。

 

merge into t

using tt

on (t.id=tt.id)

when matched then update set t.name=tt.name

when not matched then  insert values(tt.id,tt.name)

 

insert into tt(id,name)values(1,'mmmmmm');

select * from tt;

 

merge into t

using tt

on (t.id=tt.id)

when matched then update set t.name=tt.name

when not matched then  insert values(tt.id,tt.name)

ORA-30926:无法在原表中获得一组稳定的行

这个错误是由于根据条件(t.id=tt.id)连接之后得到的结果集不唯一。

 

以上错误详细信息可以参考：http://blog.itpub.net/post/468/14844

 

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

 

 

ref: http://tomszrp.itpub.net/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_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 - Prod

PL/SQL Release 10.2.0.1.0 - Production

CORE    10.2.0.1.0      Production

TNS for 32-bit Windows: Version 10.2.0.1.0 - Production

NLSRTL Version 10.2.0.1.0 - Production

 

SQL>

一、创建测试用的表

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 study

 

SQL> select * from subs;

 

      MSID MS_TYPE AREACODE

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

 905310001 0            531

 905320001 1            532

 905330001 2            533

 

SQL> 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);

 

Done

 

SQL> select * from acct;

 

      MSID BILL_MONTH AREACODE        FEE

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

 905320001     200702      532       0.00

 905330001     200702      533       0.00

 905310001     200702      531       0.00

 

SQL> insert into subs values(905340001,3,534);

 

1 row inserted

 

SQL> select * from subs;

 

      MSID MS_TYPE AREACODE

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

 905340001 3            534

 905310001 0            531

 905320001 1            532

 905330001 2            533

 

SQL>

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);

 

Done

 

SQL> 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.00

 

SQL> update subs set areacode=999;

 

4 rows updated

 

SQL> select * from subs;

 

      MSID MS_TYPE AREACODE

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

 905340001 3            999

 905310001 0            999

 905320001 1            999

 905330001 2            999

 

SQL> 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.00

 

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;

 

Done

 

SQL> 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.00

 

SQL>

　

三、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 study

 

SQL> select * from subs;

 

      MSID MS_TYPE AREACODE

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

 905310001 0            531

 905320001 1            532

 905330001 2            533

 

SQL> 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;

 

Done

 

SQL> select * from acct;

 

      MSID BILL_MONTH AREACODE        FEE

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

 905310001     200702      531       0.00

 

SQL> insert into subs values(905360001,0,536);

 

1 row inserted

 

SQL> select * from subs;

 

      MSID MS_TYPE AREACODE

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

 905360001 0            536

 905310001 0            531

 905320001 1            532

 905330001 2            533

 

SQL>

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;

 

Done

 

SQL> select * from acct;

 

      MSID BILL_MONTH AREACODE        FEE

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

 905310001     200702      531       0.00

 905360001     200702      536       0.00

 

SQL> update subs set areacode=888 where ms_type=0;

 

2 rows updated

 

SQL> select * from subs;

 

      MSID MS_TYPE AREACODE

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

 905360001 0            888

 905310001 0            888

 905320001 1            532

 905330001 2            533

 

SQL> select * from acct;

 

      MSID BILL_MONTH AREACODE        FEE

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

 905310001     200702      531       0.00

 905360001     200702      536       0.00

 

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;

 

Done

 

SQL> select * from acct;

 

      MSID BILL_MONTH AREACODE        FEE

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

 905310001     200702      888       0.00

 905360001     200702      888       0.00

 

SQL>

四、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            533

 

SQL> select * from acct;

 

      MSID MS_TYPE AREACODE

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

 905310001 0            531

 905320001 1            532

 905330001 2            533

 

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          delete where (b.ms_type!=0);

 

Done

 

SQL> select * from acct;

 

      MSID MS_TYPE AREACODE

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

 905310001 0            531

 

SQL>

 

更为详尽的语法，请参考Oracle SQL Reference手册！

 

以下是对采用merge语法做插入操作与一般的单独插入操作做性能比较测试：                    

                    

create table subs1

as

select rownum as ID ,'p'||rownum  as NAME

from dba_objects

 

create table acct(id number(9),

                   NAME VARCHAR2(10)

                     );

 

create table acct1(id number(9),

                   NAME VARCHAR2(10)

                     );

 

create table acct2(id number(9),

                   NAME VARCHAR2(10)

                     );

 

SQL> set timing on

法1：

SQL> merge into acct a

  2       using subs b on (a.id=b.id)

  3     when NOT MATCHED then

  4          insert(id,NAME)

  5          values(b.ID,b.NAME);

Done

 

Executed in 7.172 seconds

 

法2：

SQL> insert into acct2 b

  2  select * from subs a

  3  where not exists(select 1

  4                  from acct2 b

  5                  where a.id=b.id);

50376 rows inserted

 

Executed in 0.125 seconds

 

法3：

SQL> BEGIN

  2  FOR R IN (select * from subs a

  3            where not exists(select 1

  4                            from acct1 b

  5                            where a.id=b.id)

  6           ) LOOP

  7  INSERT INTO acct1 values(R.ID,R.NAME);

  8  END LOOP;

  9  END;

 10  /

PL/SQL procedure successfully completed

 

Executed in 5.312 seconds

分析：法2的插入性能是最快的，因为此种插入是批量操作，不会记录日志

      法1的插入与法3比较，说明merge语法对于只做插入或者只做更新时并不一定是性能最好的。