【存储过程】Merge Into语句实现Insert/Update在Oracle中的应用

浅浅的花香味﹌ 2022-05-30 12:37 232阅读 0赞

最近接到一个新的接口需求，开发一个保存数据的接口，需要完成如下功能：有一个表T，有两个字段a(主键),b，我们想在表T中做Insert/Update,如果存在，则更新T中b的值，如果不存在，则插入一条记录。  
在Microsoft的SQL语法中，很简单的一句判断就可以了，SQL Server中的语法如下：

> if exists(select 1 from T where T.a=’1001’ ) update T set T.b=2 Where T.a=’1001’ else insert into T(a,b) values(‘1001’,2);

但是因为现场的数据库是Oracle，所以不能用上面的语法，那么在Oracle中我们如何实现这种功能呢？首选方案就是使用存储过程。Oracle 9i之后有一条Merge into 的语句可以同时进行Insert和Update。  
**merge into语法：**

MERGE INTO [target-table] A 
        USING [source-table sql] B 
            ON([conditional expression] and [...]...)  
    WHEN MATCHED THEN  
        [UPDATE sql]  
    WHEN NOT MATCHED THEN  
        [INSERT sql]

将merge into用于以上的表逻辑：

MERGE INTO T t1
        USING (SELECT 'xxx' FROM T WHERE t.a='1001') t2
            ON(t1.a = t2.a)
    WHEN MATCHED THEN
        UPDATE SET t1.b=2 WHERE t1.a='1001'
    WHEN NOT MATCHED THEN
        INSERT (t1.a,t1.b) VALUES('1001','2')

以上语句实现的功能用中文来描述就是：  
在t2中查出来的数据，每一条都要跟t1进行ON(join condition)的比较。如果匹配，就进行更新，否则进行插入操作。  
所以，我们可以看出，在一个同时存在insert和update的merge的语句中，总共insert/update的记录数就是USING语句中t2的记录数。

**附录(本次需求用到的存储过程实例)：**

create or replace procedure PRC_SAVE_USER_OPTIONS(MOBILENO in varchar2,
                                                   LANGUAGES in varchar2) is
    begin
      MERGE INTO tbl_v8_mobile_language tv
      USING (select nvl(max(t.MOBILE_NO), 'AAAAAAAAAAAAA') as MOBILE_NO
               from tbl_v8_mobile_language t
              where t.MOBILE_NO = MOBILENO) tv2
      ON (tv.MOBILE_NO = tv2.MOBILE_NO)
      WHEN MATCHED THEN
        UPDATE SET tv.LANGUAGE = LANGUAGES
      WHEN NOT MATCHED THEN
        INSERT (tv.mobile_no, tv.language) values (MOBILENO, LANGUAGES);
      commit;
    end;

**引申：**  
上面我们说过“在一个同时存在insert和update的merge的语句中，总共insert/update的记录数就是USING语句中t2的记录数。”我们可以根据这个记录数的不同进行不同的操作。  
**1、只操作一张表的时候：**  
当USING语句中查出来的值只有一条时就只能进行insert/update操作,有多条时就可以既能insert又能update。  
如：  
1)当USING中的查询语句为SELECT ‘xxx’ FROM T t WHERE t.a=’1001’ (a为主键)，此时t2中只有一条数据，只能进行insert/update操作。  
2)当USING中的查询语句为SELECT ‘xxx’ FROM T，查出T表中的所有数据，此时t2中的数据是表T的所有数据，这就是全表操作了，既能insert又能update。  
**2、操作两张表的时候：**  
这种方法与操作一张表类似，只不过在操作一张表的时候t1、t2代表的都是一张表内的数据，而操作两张表的时候t1、t1代表的是两张表的数据。具体应用请参照这篇文章：[oracle中merge into用法解析][oracle_merge into]

[oracle_merge into]: http://blog.csdn.net/jeryjeryjery/article/details/70047022