Merge语句

 把数据从一个表复制到另一个表，插入新数据或替换掉老数据是每一个ORACLE DBA都会经常碰到的问题。在ORACLE9i以前的年代，我们要先查找是否存在老数据，如果有用UPDATE替换，否则用INSERT语句插入，其间少不了还有一些标记变量等等，繁琐的很。现在ORACLE9i专为这种情况提供了MERGE语句，使这一工作变得异常轻松。MERGE语句的语法如下：

 

MERGE [hint] INTO [schema .] table [t_alias]

USING [schema .] { table | view | subquery } [t_alias]

ON ( condition )

WHEN MATCHED THEN merge_update_clause

WHEN NOT MATCHED THEN merge_insert_clause;

 

例如：

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) 一条语句代替了原来的一段复杂语句

 

在Oracle 10g中MERGE有如下一些改进：

 

1、UPDATE或INSERT子句是可选的

 

2、UPDATE和INSERT子句可以加WHERE子句

 

3、在ON条件中使用常量过滤谓词来insert所有的行到目标表中,不需要连接源表和目标表

 

4、UPDATE子句后面可以跟DELETE子句来去除一些不需要的行

 

首先创建示例表：

 

      Create table PRODUCTS      (
      PRODUCT_ID INTEGER,
      PRODUCT_NAME VARCHAR2(60),
      CATEGORY VARCHAR2(60)      );

      insert into PRODUCTS values (1501, 'VIVITAR35 MM', 'ELECTRNCS');
      insert into PRODUCTS values (1502, 'OLYMPUS IS50', 'ELECTRNCS');
      insert into PRODUCTS values (1600, 'PLAY GYM', 'TOYS');
      insert into PRODUCTS values (1601, 'LAMAZE', 'TOYS');
      insert into PRODUCTS values (1666, 'HARRY POTTER', 'DVD');
      commit;

      Create table NEWPRODUCTS      (
      PRODUCT_ID INTEGER,
      PRODUCT_NAME VARCHAR2(60),
      CATEGORY VARCHAR2(60)      );

      insert into NEWPRODUCTS values (1502, 'OLYMPUS CAMERA', 'ELECTRNCS');
      insert into NEWPRODUCTS values (1601, 'LAMAZE', 'TOYS');
      insert into NEWPRODUCTS values (1666, 'HARRY POTTER', 'TOYS');
      insert into NEWPRODUCTS values (1700, 'WAIT INTERFACE', 'BOOKS');
      commit;

 

1、可省略的UPDATE或INSERT子句

 

在Oracle 9i, MERGE语句要求你必须同时指定INSERT和UPDATE子句.而在Oracle 10g, 你可以省略UPDATE或INSERT子句中的一个. 下面的例子根据表NEWPRODUCTS的PRODUCT_ID字段是否匹配来updates表PRODUCTS的信息:

 

SQL> MERGE INTO products p
      2 USING newproducts np
      3 ON (p.product_id = np.product_id)
      4 WHEN MATCHED THEN
      5 UPDATE
      6 SET p.product_name = np.product_name,
      7 p.category = np.category;
      3 rows merged.      

SQL> SELECT * FROM products;
      PRODUCT_ID PRODUCT_NAME CATEGORY
      ---------- -------------------- ----------
      1501 VIVITAR 35MM ELECTRNCS
      1502 OLYMPUS CAMERA ELECTRNCS
      1600 PLAY GYM TOYS
      1601 LAMAZE TOYS
      1666 HARRY POTTER TOYS      

SQL> ROLLBACK;
      Rollback complete.

 

 

 

在上面例子中, MERGE语句影响到是产品id为1502, 1601和1666的行. 它们的产品名字和种类被更新为表newproducts中的值. 下面例子省略UPDATE子句, 把表NEWPRODUCTS中新的PRODUCT_ID插入到表PRODUCTS中, 对于在两个表中能够匹配上PRODUCT_ID的数据不作任何处理. 从这个例子你能看到PRODUCT_ID=1700的行被插入到表PRODUCTS中.

 

SQL> MERGE INTO products p
      2 USING newproducts np
      3 ON (p.product_id = np.product_id)
      4 WHEN NOT MATCHED THEN
      5 INSERT
      6 VALUES (np.product_id, np.product_name,
      7 np.category);
      1 row merged.

SQL> SELECT * FROM products;
      PRODUCT_ID PRODUCT_NAME CATEGORY
      ---------- -------------------- ----------
      1501 VIVITAR 35MM ELECTRNCS
      1502 OLYMPUS IS50 ELECTRNCS
      1600 PLAY GYM TOYS
      1601 LAMAZE TOYS
      1666 HARRY POTTER DVD
      1700 WAIT INTERFACE BOOKS

  

2、带条件的Updates和Inserts子句

 

你能够添加WHERE子句到UPDATE或INSERT子句中去, 来跳过update或insert操作对某些行的处理. 下面例子根据表NEWPRODUCTS来更新表PRODUCTS数据, 但必须字段CATEGORY也得同时匹配上:

 

SQL> MERGE INTO products p
      2 USING newproducts np
      3 ON (p.product_id = np.product_id)
      4 WHEN MATCHED THEN
      5 UPDATE
      6 SET p.product_name = np.product_name
      7 WHERE p.category = np.category;
      2 rows merged.

SQL> SELECT * FROM products;
      PRODUCT_ID PRODUCT_NAME CATEGORY
      ---------- -------------------- ----------
      1501 VIVITAR 35MM ELECTRNCS
      1502 OLYMPUS CAMERA ELECTRNCS
      1600 PLAY GYM TOYS
      1601 LAMAZE TOYS
      1666 HARRY POTTER DVD

SQL> rollback;

 

在这个例子中, 产品ID为1502,1601和1666匹配ON条件但是1666的category不匹配. 因此MERGE命令只更新两行数据. 下面例子展示了在Updates和Inserts子句都使用WHERE子句:

 

 

SQL> MERGE INTO products p
      2 USING newproducts np
      3 ON (p.product_id = np.product_id)
      4 WHEN MATCHED THEN
      5 UPDATE
      6 SET p.product_name = np.product_name,
      7 p.category = np.category
      8 WHERE p.category = 'DVD'
      9 WHEN NOT MATCHED THEN
      10 INSERT
      11 VALUES (np.product_id, np.product_name, np.category)
      12 WHERE np.category != 'BOOKS';
      1 row merged.

SQL> SELECT * FROM products;
      PRODUCT_ID PRODUCT_NAME CATEGORY
      ---------- -------------------- ----------
      1501 VIVITAR 35MM ELECTRNCS
      1502 OLYMPUS IS50 ELECTRNCS
      1600 PLAY GYM TOYS
      1601 LAMAZE TOYS
      1666 HARRY POTTER TOYS

 

 

 

注意由于有WHERE子句INSERT没有插入所有不匹配ON条件的行到表PRODUCTS.

3、无条件的Inserts

 

你能够不用连接源表和目标表就把源表的数据插入到目标表中. 这对于你想插入所有行到目标表时是非常有用的. Oracle 10g现在支持在ON条件中使用常量过滤谓词. 举个常量过滤谓词例子ON (1=0). 下面例子从源表插入行到表PRODUCTS, 不检查这些行是否在表PRODUCTS中存在:

 

SQL> MERGE INTO products p
      2 USING newproducts np
      3 ON (1=0)
      4 WHEN NOT MATCHED THEN
      5 INSERT
      6 VALUES (np.product_id, np.product_name, np.category)
      7 WHERE np.category = 'BOOKS'；
      1 row merged.
      
SQL> SELECT * FROM products;
      PRODUCT_ID PRODUCT_NAME CATEGORY
      ---------- -------------------- ----------
      1501 VIVITAR 35MM ELECTRNCS
      1502 OLYMPUS IS50 ELECTRNCS
      1600 PLAY GYM TOYS
      1601 LAMAZE TOYS
      1666 HARRY POTTER DVD
      1700 WAIT INTERFACE BOOKS
      6 rows selected.

 

 

 

4、新增加的DELETE子句

 

Oracle 10g中的MERGE提供了在执行数据操作时清除行的选项. 你能够在WHEN MATCHED THEN UPDATE子句中包含DELETE子句. DELETE子句必须有一个WHERE条件来删除匹配某些条件的行.匹配DELETE WHERE条件但不匹配ON条件的行不会被从表中删除.

 

下面例子验证DELETE子句. 我们从表NEWPRODUCTS中合并行到表PRODUCTS中, 但删除category为ELECTRNCS的行.

 

 

SQL> MERGE INTO products p
      2 USING newproducts np
      3 ON (p.product_id = np.product_id)
      4 WHEN MATCHED THEN
      5 UPDATE
      6 SET p.product_name = np.product_name,
      7 p.category = np.category
      8 DELETE WHERE (p.category = 'ELECTRNCS')
      9 WHEN NOT MATCHED THEN 
     10 INSERT 
     11 VALUES (np.product_id, np.product_name, np.category);
      4 rows merged.
      
SQL> SELECT * FROM products;
      PRODUCT_ID PRODUCT_NAME CATEGORY
      ---------- -------------------- ----------
      1501 VIVITAR 35MM ELECTRNCS
      1600 PLAY GYM TOYS
      1601 LAMAZE TOYS
      1666 HARRY POTTER TOYS
      1700 WAIT INTERFACE BOOKS

 

产品ID为1502的行从表PRODUCTS中被删除, 因为它同时匹配ON条件和DELETE WHERE条件. 产品ID为1501的行匹配DELETE WHERE条件但不匹配ON条件, 所以它没有被删除. 产品ID为1700 的行不匹配ON条件, 所以被插入表PRODUCTS. 产品ID为1601和1666的行匹配ON条件但不匹配DELETE WHERE条件, 所以被更新为表NEWPRODUCTS中的值.

 

 

 

 

 

 

 

 ---------------------------------- =====Line===== ----------------------------------

 

设计技巧 #107 使用MERGE语句进行渐变维处理

 

 

 

 

 

November 6, 2008

 

作者：Warren Thornthwaite

译者：Daniel Zhen

 

很多ETL工具都提供了处理渐变维度的功能。也有些情况下，当此类工具不能满足需求时，ETL开发者将直接面对数据库，验证更新或变化了的行，并恰当的使用INSERT和UPDATE命令处理之。在“深入数据仓库生命周期”课程上，我已经演示了使用INSERT和UPDATE语句的代码。几个月后，我的朋友Stuart Ozer告诉我，使用SQL Server 2008中的MERGE语句在代码执行方面有更好的效率。他所引用的是MSSQLTips.com上Chad Boyd的Blog，这给了我一些如何实现的启示。MERGE是INSERT，UPDATE和DELETE的组合，它有效的降低了语句的复杂度。

本例处理的是简单的客户维度，它具有两个属性：first name和last name。我们将把first name作为类型1处理，把last name看作类型二处理。记住，所谓类型1是指维度属性的旧值覆写；类型二是通过增加新值让跟踪历史纪录更具效率。

 

步骤 1: 覆写类型1中的变化值

我曾尝试在整个例子中只使用一次MERGE语句，但该函数属于确定性函数，每次只能执行一次update语句，所以我在下例中分别使用了多个MERGE进行类型1更新。因为类型1定义为更新，所以也可以使用update语句直接处理。

 

MERGE INTO dbo.Customer_Master AS CM

USING Customer_Source AS CS

ON (CM.Source_Cust_ID = CS.Source_Cust_ID)

WHEN MATCHED AND --根据类型1更新所有已存在的行

CM.First_Name <> CS.First_Name

THEN UPDATE SET CM.First_Name = CS.First_Name

 

以上简版的MERGE语句，通过关联业务键，更新所有主表和原表中First_Name不一致的行，实现了Customer_Source表和Customer_Master维度的归并。

 

步骤 2: 处理类型2中的变化值

现在我们将使用另一个MERGE语句来处理类型2中的变化值。这是件比较棘手的事，因为在跟踪类型2变化值是会有很多的步骤。执行我们代码将需要：

 

1. 在截止时间前，适当并有效地插入新客户数据行。

2. 通过设置恰当的终止时间和设置current_row flag = ‘n’，标识类型2中维度属性变化的行。

3. 通过设置恰当的终止时间和设置current_row flag = ‘y’，插入类型2的变化行。

 

这样做会导致太多的步骤需要MERGE处理的问题。幸运的是，MERGE可以流化输出到下一个过程。我们将使用这一功能，使用SELECT从MERGE的结果中选择行并插入到Customer_Master表中，最终完成类型2变化行的插入。听上去，这是一种复杂的并容易出问题的方法，但是它的好处在在于可以一次性找到类型2中变化了的行，并可以多次使用。

代码以INSERT和SELECT语句开始，用来在MERGE语句执行后处理变化行插入。之所以把它们放在前面，是因为MERGE是包含在INSERT嵌套中的。代码中包含很多对于当前日期的引用，代码中假设变化自昨天起有效（getdate()-1），即前天（getdate()-2）的数据可以被标识为退化。最后，我列出了代码，并根据行号进行说明：

 

1 INSERT INTO Customer_Master

2 SELECT Source_Cust_ID, First_Name, Last_Name, Eff_Date, End_Date, Current_Flag

3 FROM

4 ( MERGE Customer_Master CM

5 USING Customer_Source CS

6 ON (CM.Source_Cust_ID = CS.Source_Cust_ID)

7 WHEN NOT MATCHED THEN

8 INSERT VALUES (CS.Source_Cust_ID, CS.First_Name, CS.Last_Name,

convert(char(10), getdate()-1, 101), '12/31/2199', 'y')

9 WHEN MATCHED AND CM.Current_Flag = 'y'

10 AND (CM.Last_Name <> CS.Last_Name ) THEN

11 UPDATE SET CM.Current_Flag = 'n', CM.End_date = convert(char(10), getdate()-

2, 101)

12 OUTPUT $Action Action_Out, CS.Source_Cust_ID, CS.First_Name, CS.Last_Name,

convert(char(10), getdate()-1, 101) Eff_Date, '12/31/2199' End_Date, 'y'Current_Flag

13 ) AS MERGE_OUT

14 WHERE MERGE_OUT.Action_Out = 'UPDATE';

 

代码注释

Ø  1-3行执行典型的INSERT语句. 将用来在最后插入类型2的变化行。

Ø  第4行是MERGE语句的开始，直到第13行。MERGE语句中有OUTPUT标识符，它将流化MERGE的结果，并由函数调用。该语法定义了一般表表达式，这本质上是一个FROM标识符中的临时表，被称作MERGE_OUT。

Ø  4-6行执行MERGE，装载Customer_Source数据进入Customer_Master维度表。

Ø  第7行说明如果无法匹配业务键，我们必须有一个新的客户数据加入，因此第8行执行了插入操作。你可以通过参数化有效日期取代假设的昨天的日期。

Ø  9-10行定义了业务键可以进行匹配的行的子集，特别是，Customer_Master表中已有数据和类型font-size: 10p