MERGE INTO

参考资料:http://www.oracle-developer.net/content/utilities/merge_counter.sql

文档:Oracle Database SQL Reference, 10g Release 2 (10.2)---1235页

Oracle MERGE INTO的用法总结:

     Use the MERGE statement to select rows from one or more sources for update or insertion into a table or view. You can specify conditions to determine whether to  update or insert into the target table or view.
     This statement is a convenient way to combine multiple operations. It lets you avoid  multiple INSERT, UPDATE, and DELETE DML statements.
     MERGE is a deterministic statement. That is, you cannot update the same row of the target table multiple times in the same MERGE statement.

注意:MERGE操作的时候，权限没有很好的进行继承，当需要操作其他用户的表(涉及到)

       Note: Oracle Database does not implement fine-grained access control during MERGE statements. If you are using the fine-grained access control feature on the target table or tables, use equivalent INSERT and UPDATE statements instead of MERGE to avoid error  messages and to ensure correct access control。

注意:

1、ON后面的条件必须要使用括号括起来
2、MERGE语句的UPDATE不能修改用于连接的列，否则会报错，例如本例子中不可以修改U这一列
3、当在UPDATE后面，追加了删除语句，删除语句的where条件必须要用括号括起来，否则无法删除数据
例如:UPDATE SET P = 'A'
DELETE WHERE (p = 'F')-- 正确
UPDATE SET P = 'A'
DELETE WHERE p = 'F'-- 错误，编译和执行都不会报错，但是无法删除满足条件的数据

MERGE INTO TABLE_NAME AS TABLE_ALIAS
USING (TABLE | VIEW | SUB_QUERY) AS ALIAS
ON (JOIN CONDITION)
WHEN MATCHED THEN
  UPDATE
     SET COL1 = COL_VAL1, COL2 = COL2_VAL --9i 不可以有where条件，10g 可以
WHEN NOT MATCHED THEN
  INSERT
    (COLUMN_LIST) —多个列以逗号分割 / / 可以不指定列
  VALUES
    (COLUMN_VALUES); --9i 不可以有where条件，10g 可以

T1:

T2:

1、MERGE语句的使用；

SELECT * FROM t_2
TRUNCATE TABLE t_2
MERGE INTO T_2 T2
USING T_1 T1
ON (T2.U = T1.U)
WHEN MATCHED THEN
  UPDATE SET P = 'a'
WHEN NOT MATCHED THEN
  INSERT (T2.U, T2.P) VALUES (T1.U, T1.P);

2、含有DELETE语句的MERGE语句:

SELECT *
  FROM T_2 TRUNCATE TABLE T_2 MERGE
  INTO T_2 T2
 USING T_1 T1
    ON (T2.U = T1.U) 
 WHEN MATCHED THEN UPDATE SET P = 'a' 
   DELETE WHERE (P = 'F') --where条件必须要使用括号包含起来
 WHEN NOT MATCHED THEN 
   INSERT(T2.U, T2.P) VALUES(T1.U, T1.P);

在这里分享一个针对MERGE的包，它主要的功能的是统计INSERT和UPDATE的最终记录数:

CREATE OR REPLACE PACKAGE merge_counter AS

   FUNCTION insert_counter
      RETURN PLS_INTEGER;

   FUNCTION update_counter
      RETURN PLS_INTEGER;

   FUNCTION get_update_count
      RETURN PLS_INTEGER;

   FUNCTION get_update_count (
            merge_rowcount_in IN PLS_INTEGER
            ) RETURN PLS_INTEGER;

   FUNCTION get_insert_count
      RETURN PLS_INTEGER;

   FUNCTION get_insert_count (
            merge_rowcount_in in PLS_INTEGER
            ) RETURN PLS_INTEGER;

   PROCEDURE reset_counters;

END merge_counter;
/
CREATE OR REPLACE PACKAGE BODY merge_counter AS

   g_update_counter PLS_INTEGER NOT NULL := 0;
   g_insert_counter PLS_INTEGER NOT NULL := 0;

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

   FUNCTION insert_counter
      RETURN PLS_INTEGER IS
   BEGIN
      g_insert_counter := g_insert_counter + 1;
      RETURN 0;
   END insert_counter;

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

   FUNCTION update_counter
      RETURN PLS_INTEGER IS
   BEGIN
      g_update_counter := g_update_counter + 1;
      RETURN 0;
   END update_counter;

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

   FUNCTION get_update_count
      RETURN PLS_INTEGER is
   BEGIN
      RETURN g_update_counter;
   END get_update_count;

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

   FUNCTION get_update_count (
            merge_rowcount_in IN PLS_INTEGER
            ) RETURN PLS_INTEGER IS
   BEGIN
      RETURN NVL( merge_rowcount_in - g_insert_counter, 0 );
   END get_update_count;

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

   FUNCTION get_insert_count
      RETURN PLS_INTEGER IS
   BEGIN
      RETURN g_insert_counter;
   END get_insert_count;

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

   FUNCTION get_insert_count (
            merge_rowcount_in IN PLS_INTEGER
            ) RETURN PLS_INTEGER IS
   BEGIN
      RETURN NVL( merge_rowcount_in - g_update_counter, 0 );
   END get_insert_count;

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

   PROCEDURE reset_counters IS
   BEGIN
      g_update_counter := 0;
      g_insert_counter := 0;
   END reset_counters;

END merge_counter;
/

      在这里需要说明下DEOCDE这个函数的高级用法，因为MERGE_COUNTER.INSERT_COUNTER实现了计数的功能，
但是，他的返回值最终都是0,所以任何情况下都会使用S.PRIMARY_KEY的值,这是在这里的一个高级用法。

下面是上面的包的使用方法:

1、

(DECODE(MERGE_COUNTER.INSERT_COUNTER, 0, S.PRIMARY_KEY)
MERGE INTO TARGET_TABLE T
USING SOURCE_TABLE S
ON (S.PRIMARY_KEY = T.PRIMARY_KEY)
WHEN MATCHED THEN
  UPDATE SET T.COLUMN_NAME = S.COLUMN_NAME
WHEN NOT MATCHED THEN
  INSERT
    (T.PRIMARY_KEY, T.COLUMN_NAME)
  VALUES
    (DECODE(MERGE_COUNTER.INSERT_COUNTER, 0, S.PRIMARY_KEY), S.COLUMN_NAME);

2、

MERGE INTO TARGET_TABLE T
USING SOURCE_TABLE S
ON (S.PRIMARY_KEY = T.PRIMARY_KEY)
WHEN MATCHED THEN
  UPDATE
     SET T.COLUMN_NAME = DECODE(MERGE_COUNTER.UPDATE_COUNTER,
                                0,
                                S.COLUMN_NAME)
WHEN NOT MATCHED THEN
  INSERT
    (T.PRIMARY_KEY, T.COLUMN_NAME)
  VALUES
    (S.PRIMARY_KEY, S.COLUMN_NAME);

查询计数的结果:

SELECT MERGE_COUNTER.get_update_count,MERGE_COUNTER.get_insert_count FROM dual;

重置计数：

BEGIN 
  MERGE_COUNTER.reset_counters;
END;

--

 补充:

-- Create table
create table TEST_DEPT
(
  dept_no    VARCHAR2(100),
  sysdates   DATE,
  ids        VARCHAR2(100),
  parent_ids VARCHAR2(100)
)

使用merge into的时候，应当防止以下的错误信息：

中间表：

SELECT t.*,
       ROWID
  FROM user_info_mid t;

结果：

最终表：

select t.*,ROWID from user_info t;

执行下面的MERGE INTO子句：

MERGE INTO user_info ui
USING (SELECT uim.uname,
              uim.monty,
              uim.id
         FROM user_info_mid uim) cols
ON (ui.uname = cols.uname)
WHEN MATCHED THEN
  UPDATE SET monty = cols.monty WHERE id = ui.id
WHEN NOT MATCHED THEN
  INSERT
    (uname,
     monty,
     id)
  VALUES
    (cols.uname,
     cols.monty,
     cols.id);

下面是摘自ITPUB的一段英文解释：

PURPOSE This article is intended to introduce the MERGE SQL statement available in Oracle9i. 

SCOPE & APPLICATION This article assumes the reader has an understanding of SQL. 

RELATED DOCUMENTS Oracle 9i SQL Reference Oracle9i Merge: 

=============== 
        The MERGE statement introduced in Oracle9i allows a single SQL statement to either insert or update a table conditionally 
         by selecting rows from another table or view, or using a subquery. If the row already exists, an update is done, otherwise the row is inserted. 从另一张表、视图、或者子查询取得结果集，如果行已经存在，则可以更新。否则插入该行数据。
         MERGE is useful in data warehousing environments where source data is being loaded into a destination table  and there may already be existing rows for a particular key value. 
        The INTO clause specifies the target table to be updated or inserted into. INTO指明目标表
        The USING clause specifies the source. This can be a table, view or a subquery. USING指明数据源
        The ON clause specifies the condition to be used to determine whether an insert or update occurs. 
If now rows in the target table satisfy the condition in the ON clause, the source data is inserted. 
Any INSERT/UPDATE triggers that are defined on the source table will be executed as with standard INSERT/UPDATE. 

         MERGE can be parallelized using parallel DML rules. 
The current restrictions which apply to parallel UPDATE will apply to MERGE, 
namely that the MERGE will only be parallelized if the destination table is partitioned. 
Each partition will be INSERTED/UPDATED by only one slave. 
Restrictions on the update clause: 
- You cannot specify DEFAULT when updating a view. 
- You cannot update a column that has been referenced in the ON condition clause. 不能更新在ON条件中的列。
- You cannot update the same row of the target table multiple times in the same MERGE statement. 

  不能多次的更新一个行（多次更新就导致了上述的错误情况出现）
Prerequisites: 
- You must have INSERT and UPDATE object privileges on the target table and SELECT privilege on the source table. 

Example: 
-------- 
Suppose you have a source and a destination table. You want to add the data to the value of any existing rows and insert any new rows. 
MERGE INTO tdest d 
USING tsrc s 
ON (s.srckey = d.destkey) 
WHEN MATCHED THEN 
UPDATE SET d.destdata = d.destdata + s.srcdata 
WHEN NOT MATCHED THEN 
INSERT (destkey,destdata) VALUES (srckey,srcdata) 

     Multiple inserts into the destination of the same key value from the source table will be allowed. 
      However, there is a restriction that multiple updates to the same row in the destination table is not allowed. 多次更新一行是不被允许的
The following error will be returned if this is attempted: 
ORA-30926: unable to get a stable set of rows in the source tables 
Cause: A stable set of rows could not be got because of large dml activity or a non-deterministic where clause. 不能获得一个稳定的行
Action: Remove any non-deterministic where clauses and reissue the dml. 
It is possible, though, to aggregate the results in an inline view in the USING clause. 
Examples: --------- 
MERGE INTO tdest d 
USING (SELECT srckey,sum(srcdata) sumsrc FROM tsrc GROUP BY srckey) s 
ON (d.destkey=s.srckey) 
WHEN MATCHED THEN 
UPDATE SET d.destdata=s.sumsrc + d.destdata 
WHEN NOT MATCHED THEN 
INSERT (destkey,destdata) VALUES (srckey,sumsrc); 

MERGE INTO account a 
USING (SELECT id, sum(balance) sum_balance FROM transaction GROUP BY id) t 
ON (a.id=t.id) 
WHEN MATCHED THEN 
UPDATE SET a.balance = a.balance + t.sum_balance 
WHEN NOT MATCHED THEN 
INSERT (a.id,a.balance) VALUES (t.id,t.sum_balance); 

If you attempt to update a column that is also referenced in the ON condition clause, 
you will receive an ORA-904 error: 
ORA-00904: invalid column name 
Cause: The column name entered is either missing or invalid. 
Action: Enter a valid column name. 
This is documented in [BUG:2124282]. References: =========== [BUG:2124282] ORA-904 USING MERGE STATEMENT

产生该问题的原因是。在using的数据集中的数据不唯一造成的。

那么解决该问题的唯一办法就是在中间表中的数据进行筛选，去除掉重复的记录，

下面是提取结果集中最晚一条的（类似与EXCEL导入，假如第一行和第十行的数据发生重复，那么需要以第十行的数据为准）

1、使用分析函数

SELECT *
  FROM (SELECT id,
               uim.uname,
               row_number() over(PARTITION BY uname ORDER BY rownum DESC) rn
          FROM user_info_mid uim)
 WHERE rn = 1

2、使用分组函数（需要使用-32位进行截取，因为默认的id的长度为32）

SELECT MAX(id),
       substr(MAX(id), -32),
       uname
  FROM (SELECT rownum || uim.id AS id,
               uim.uname,
               row_number() over(PARTITION BY uname ORDER BY rownum DESC) rn
          FROM user_info_mid uim)
 GROUP BY uname

--

-- Create table
create table DEPT_DEMOS
(
  deptno NUMBER(2),
  dname  VARCHAR2(14),
  loc    VARCHAR2(13),
  types  VARCHAR2(13)
)

过程:

CREATE OR REPLACE PROCEDURE Test_Insert(i_Parent_Ids VARCHAR2) IS
  v_Exist_Flag NUMBER := 0;
BEGIN

  /*  FOR Idx IN (SELECT d.Deptno, d.Dname, d.Loc FROM Dept d) LOOP
    
    --可以使用标志位的判断方法来实现功能
    SELECT COUNT(1)
      INTO v_Exist_Flag
      FROM Test_Dept t
     WHERE t.Parent_Ids = i_Parent_Ids AND T.DEPT_NO = IDX.DEPTNO;
    Dbms_Output.Put_Line(v_Exist_Flag);
    
    IF v_Exist_Flag = 0 THEN
      INSERT INTO Test_Dept
      VALUES
        (Idx.Deptno, SYSDATE, Sys_Guid(), i_Parent_Ids);
    END IF;
    
    --也可以使用MERGE INTO的用法来实现该功能:需要DULA表
    MERGE INTO Test_Dept t
    USING (SELECT Idx.Deptno AS Dept_s FROM Dual) d
    ON (t.Dept_No = d.Dept_s AND t.Parent_Ids = i_Parent_Ids)
    WHEN NOT MATCHED THEN
      INSERT
        (Dept_No, Sysdates, Ids, Parent_Ids)
      VALUES
        (Idx.Deptno, SYSDATE, Sys_Guid(), i_Parent_Ids);
  
  END LOOP;*/

  --也可以使用MERGE INTO的用法来实现该功能:需要使用子查询
  MERGE INTO Test_Dept t
  USING (SELECT * FROM Dept_Demos WHERE Types = '0') d
  ON (t.Dept_No = d.Deptno AND t.Parent_Ids = i_Parent_Ids)
  WHEN NOT MATCHED THEN
    INSERT
      (Dept_No, Sysdates, Ids, Parent_Ids)
    VALUES
      (d.Deptno, SYSDATE, Sys_Guid(), i_Parent_Ids);

  COMMIT;
  SELECT COUNT(1)
    INTO v_Exist_Flag
    FROM Test_Dept t
   WHERE t.Parent_Ids = i_Parent_Ids;
  Dbms_Output.Put_Line(v_Exist_Flag);
END;