Oracle学习笔记—mergo into的使用方法

常见报错

ora-30926 无法在源表中获得一组稳定的行
原因：merge into 表1 和 using 表2的数据不是一对一的关系。
解决方法： 对表添加限制条件，保证表1和表2是一对一的关系

无法更新on中的字段
原因：将on中的限制条件放到update/insert之后的where条件里

##############################################################

我们操作数据库的时候，有时候会遇到insertOrUpdate这种需求。
如果数据库中存在数据就update，如果不存在就insert。
以前的时候，需要额外select查询一下，如果有数据就update，如果没有数据就insert。
而现在Orcale数据库都提供了 MERGE 方法来处理这种需求。
MERGE 命令使用一条语句从一个或者多个数据源中完成对表的更新和插入数据。

示例

     MERGE INTO EPRK_ACCINTO_LX_B A
     USING (SELECT H.PK_ACCINTO_LX_B, B.BALANCE
              FROM EPRK_ACCINTO_LX_B H,
                   (SELECT C.PK_PERSON,
                           SUM(C.PSN_INTRST + C.CORP_INTRST + C.CORP_V_INTRST) BALANCE
                      FROM EPRK_HST_INTRST C
                     WHERE C.IF_ACCINTO != 1
                       AND C.INTRST_YEAR_MONTH >= '"+start_date+"'
                       AND C.INTRST_YEAR_MONTH <= '"+end_date+"'
                     GROUP BY C.PK_PERSON) B
             WHERE H.PK_PERSON = B.PK_PERSON
               AND H.PK_ACCINTO_LX = '"+pk_accinto_lx+"') F
     ON (A.PK_ACCINTO_LX_B = F.PK_ACCINTO_LX_B)
     WHEN MATCHED THEN
       UPDATE
          SET A.ACCINTO_INSTR = F.BALANCE,
              A.ACCINTO_MONEY = A.ACCINTO_MONEY + F.BALANCE

前段时间，因为涉及到一张表的大数据操作，要同时进行增删改，我跟师傅想了很多优化办法，结果都不尽人意。刚开始用的就是原始算法，先更新现有记录，再插入满足要求的其他记录，最后再删除多余记录，但是少量数据还可以，10W条数据就不行了，.最后改为merge into操作，再做了一些优化，将50W条数据的单线程耗时缩短到三十多秒，特此，将merge into的用法总结如下：

　　有一个表T，有两个字段a、b，我们想在表T中做Insert/Update，如果条件满足，则更新T中b的值，否则在T中插入一条记录。在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中，要实现相同的功能，要用到Merge into来实现（Oracle 9i引入的功能），其语法如下：

MERGE 语法：

MERGE INTO table_name alias1 
USING (table|view|sub_query) alias2
ON (join condition) 
WHEN MATCHED THEN 
    UPDATE table_name 
    SET col1 = col_val1, 
           col2 = col_val2 
WHEN NOT MATCHED THEN 
    INSERT (column_list) VALUES (column_values);

on中只写两个表的关联条件，不加查询数据范围的限制条件。

　严格意义上讲，”在一个同时存在Insert和Update语法的Merge语句中，总共Insert/Update的记录数，就是Using语句中alias2的记录数”。所以，要实现上面的功能，可以这样写：

MERGE INTO T T1
USING (SELECT '1001' AS a,2 AS b FROM dual) T2
ON ( T1.a=T2.a)
WHEN MATCHED THEN
    UPDATE SET T1.b = T2.b
WHEN NOT MATCHED THEN 
    INSERT (a,b) VALUES(T2.a,T2.b);

注意事项：

Merge Into的原理是，从using 搜出来的结果逐条与on条件匹配，然后决定是update还是Insert。 当USING后面的sql没有查询到数据的时候，Merge Into语句是不会执行update和Insert操作的。

所以要想让Merge Into正常运行，要保证USING 后面的SELECT有数据，个人喜欢使用DUAL表作为USING后的表，方便自己控制。