Oracle-Merge

原文：http://www.cnblogs.com/kerrycode/archive/2011/06/23/2088540.html

Merge into 详细介绍

MERGE语句是Oracle9i新增的语法，用来合并UPDATE和INSERT语句。

通过MERGE语句，根据一张表或子查询的连接条件对另外一张表进行查询，连接条件匹配上的进行UPDATE，无法匹配的执行INSERT。

这个语法仅需要一次全表扫描就完成了全部工作，执行效率要高于INSERT＋UPDATE。 

语法：

MERGE INTO [your table-name] [rename your table here]
USING ( [write your query here] )[rename your query-sql and using just like a table]
ON ([conditional expression here] AND [...]...)
WHEN MATHED THEN [here you can execute some update sql or something else ]
WHEN NOT MATHED THEN [execute something else here ! ]

SQL例子:

merge into t_student t1
using (select id, name, sex, age from t_studeng_temp) t2
on (t1.id = t2.id)
when matched then
  update set t1.name = t2.name, t1.sex = t2.sex, t1.age = t2.age
when not matched then
  insert (id, name, sex, age) values (t2.id, t2.name, t2.sex, t2.age);

在 Oracle 10g中MERGE有一些新特性。

1. UPDATE或INSERT子句是可选的  

假如某个系统中，有个订单表，现在要求新增订单的记录都要反应到订单历史表ORDER_HISTORY中，我们可以如下写脚本

merge into order_history h
using (select order_id, --订单编号
              customer_id, --客户编号
              employee_id, --员工编号
              order_date, --订购日期；
              required_date, --预计到达日期
              shipped_date, --发货日期
              shipper, --运货商
              freight, --运费
              ship_nam, --货主姓名；
              ship_address, --货主地址
              ship_city, --货主所在城市；
              ship_region, --货主所在地区；
              ship_postalcode, --货主邮编
              ship_country --货主所在国家
         from order_dtl
        where to_char(oder_date, 'yyyy-mm-dd') = '20110530') o
on (o.order_id = h.order_id)
when not matched then
  insert
    (h.order_id,
     h.customer_id,
     h.employee_id,
     h.order_date,
     h.required_date,
     h.shipped_date,
     h.shipper,
     h.freight,
     h.ship_nam,
     h.ship_address,
     h.ship_city,
     h.ship_region,
     h.ship_postalcode,
     h.ship_country)
  values
    (o.order_id,
     o.customer_id,
     o.employee_id,
     o.order_date,
     o.required_date,
     o.shipped_date,
     o.shipper,
     o.freight,
     o.ship_nam,
     o.ship_address,
     o.ship_city,
     o.ship_region,
     o.ship_postalcode,
     o.ship_country);

从上可以看出，MATCHED 或NOT MATCHED是可选的。不必非得

WHEN MATCHED THEN UPDATE SET

.....

WHEN NOT MATCHED THEN INSERT

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

现在由于需求改变，我们仅仅需要把员工1001的订单数据同步到订单历史记录表  

merge into order_history h
using (select order_id, --订单编号
              customer_id, --客户编号
              employee_id, --员工编号
              order_date, --订购日期；
              required_date, --预计到达日期
              shipped_date, --发货日期
              shipper, --运货商
              freight, --运费
              ship_nam, --货主姓名；
              ship_address, --货主地址
              ship_city, --货主所在城市；
              ship_region, --货主所在地区；
              ship_postalcode, --货主邮编
              ship_country --货主所在国家
         from order_dtl) o
on (o.order_id = h.order_id)
when matched then
  update
     set h.customer_id     = o.customer_id,
         h.employee_id     = o.employee_id,
         h.order_date      = o.order_date,
         h.required_date   = o.required_date,
         h.shipped_date    = o.shipped_date,
         h.shipper         = o.shipper,
         h.freight         = o.freight,
         h.ship_nam        = o.ship_nam,
         h.ship_address    = o.ship_address,
         h.ship_city       = o.ship_city,
         h.ship_region     = o.ship_region,
         h.ship_postalcode = o.ship_postalcode,
         h.ship_country    = o.ship_country
   where o.employee_id = 1001
when not matched then
  insert
    (h.order_id,
     h.customer_id,
     h.employee_id,
     h.order_date,
     h.required_date,
     h.shipped_date,
     h.shipper,
     h.freight,
     h.ship_nam,
     h.ship_address,
     h.ship_city,
     h.ship_region,
     h.ship_postalcode,
     h.ship_country)
  values
    (o.order_id,
     o.customer_id,
     o.employee_id,
     o.order_date,
     o.required_date,
     o.shipped_date,
     o.shipper,
     o.freight,
     o.ship_nam,
     o.ship_address,
     o.ship_city,
     o.ship_region,
     o.ship_postalcode,
     o.ship_country) where o.employee_id = 1001

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

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