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

  最近接到一个新的接口需求，开发一个保存数据的接口，需要完成如下功能：有一个表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用法解析