oracle merge into 用法详解

来源：互联网发布：js对象转换成json 编辑：程序博客网时间：2024/05/15 00:17

1.MERGE INTO 的用途
MERGE INTO 是Oracle 9i以后才出现的新的功能。那这个功能是什么呢？
简单来说，就是：“有则更新，无则插入”
从这句话里，应该可以理解到，merge into 操作一个对象'A'的时候，要有另外一个结果集做为源数据 'B'.
‘merge into’ 将B中的数据与A中的数据按照一定条件'C'进行对比，如果 A中数据满足C条件，则进行update操作，如果不满足条件 'C'，则进行insert操作。（请注意这种对应关系）

2、语法结构

       MERGE [INTO] [schema.]table [alias]
   USING {[schema.]table|views|query} [alias]
       ON {condition}
       WHEN MATCHED THEN UPDATE SET {clause}
       WHEN NOT MATCHED THEN INSERT VALUES {clause}

       我们可以用于单条数据的处理，也可以用于数据的批处理。对于merge into来说，那都是张飞吃豆芽儿，小菜一碟儿。而且效率要比单独执行update+insert 操作效率要高。
    但是请注意，using语句中的结果集 B不可以与merge into 的对象A相同，否则，会因为结果集A,B恒等。

当 on() 进行等值判断时，只可以进行update操作，不能进行insert 操作，当 on() 进行不等值判断时，只可以进行insert操作，不能进行update操作。

         可能这样说还不是很清楚。下面我们实际操作演示，就会理解清楚了。

3.   测试 MERGE INTO
--------------------------------------------------------------------------------------------------------------
-------------------------------------        创建测试表--TEST1    ----------------------------------

SQL>         CREATE TABLE TEST1(
      2          USERID        NUMBER,
      3          ID                  NUMBER);

Table created
--------------------------------------------------------------------------------------------------------------
----------------------------------          填充测试数据              -------------------------------

SQL> begin
2 for i in 200..230 loop
3 insert into test1 values(i,(i+100)/10);
4 end loop;
5 end;
6 /

PL/SQL procedure successfully completed

SQL> select * from test1;

    USERID         ID
---------- ----------
       200         30
       201       30.1
       202       30.2
       203       30.3
       204       30.4
       205       30.5
       206       30.6
       207       30.7
       208       30.8
       209       30.9
       210         31
       211       31.1
       212       31.2
       213       31.3
       214       31.4
       215       31.5
       216       31.6
       217       31.7
       218       31.8
       219       31.9

    USERID         ID
---------- ----------
       220         32
       221       32.1
       222       32.2
       223       32.3
       224       32.4
       225       32.5
       226       32.6
       227       32.7
       228       32.8
       229       32.9
       230         33

31 rows selected
--------------------------------------------------------------------------------------------------------------
-----------------------------    创建测试表--TEST2 、TEST3      --------------------------------

SQL> create table test2
2 as
3 select userid,trunc(id,-1) id2 from test1 where length(id)=4;

Table created

SQL> select * from test2;

    USERID        ID2
---------- ----------
       201         30
       202         30
       203         30
       204         30
       205         30
       206         30
       207         30
       208         30
       209         30
       211         30
       212         30
       213         30
       214         30
       215         30
       216         30
       217         30
       218         30
       219         30
       221         30
       222         30

    USERID        ID2
---------- ----------
       223         30
       224         30
       225         30
       226         30
       227         30
       228         30
       229         30

27 rows selected

SQL> create table test3
2 as
3 select userid,id id3 from test1 where length(id) <> 4;

Table created

SQL> select * from test3;

    USERID        ID3
---------- ----------
       200         30
       210         31
       220         32
       230         33
--------------------------------------------------------------------------------------------------------------
-----------------------------------    测试MERGE INTO    -----------------------------------

SQL> MERGE INTO test3 t
2 USING (SELECT userid,id2 FROM test2 WHERE id2=30) tw
3 ON (t.userid = tw.userid)
4 WHEN MATCHED THEN UPDATE SET t.id3=tw.id2
5 WHEN NOT MATCHED THEN INSERT VALUES(tw.userid,tw.id2);

Done

SQL> SELECT * FROM test3;

    USERID        ID3
---------- ----------
       200         30
       210         31
       220         32
       230         33
       202         30
       201         30
       204         30
       203         30
       228         30
       217         30
       209         30
       221         30
       219         30
       227         30
       205         30
       212         30
       225         30
       208         30
       222         30
       226         30

    USERID        ID3
---------- ----------
       215         30
       218         30
       214         30
       216         30
       211         30
       224         30
       213         30
       223         30
       206         30
       207         30
       229         30

31 rows selected

SQL>

--测试完成，我们看到，test2表中的数据全部加到test3表中。

PS:

on(condition_clause)这里的条件句中，可以使用=，>,< 等比较运算符。

------------------------------------- The End -------------------------------------------

0 0