Oracle:merge into增强功能

其实merge除了不能return,功能还是挺强的,希望下面的东西对大家有点帮助。

 

1、无条件的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'

    SQL> /

    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.

          SQL>

 

 

 

 

2、新增加的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)

    SQL> /

    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

          SQL>

 

 

 

 

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

 

3. 再来个toad中的例子吧.

Examples

Merging into a Table: Example The following example uses the bonuses table in the sample schema oe with a default bonus of 100. It then inserts into the bonuses table all employees who made sales, based on the sales_rep_id column of the oe.orders table. Finally, the human resources manager decides that employees with a salary of $8000 or less should receive a bonus. Those who have not made sales get a bonus of 1% of their salary. Those who already made sales get an increase in their bonus equal to 1% of their salary. The MERGE statement implements these changes in one step:

CREATE TABLE bonuses (employee_id NUMBER, bonus NUMBER DEFAULT 100);

INSERT INTO bonuses(employee_id)
   (SELECT e.employee_id FROM employees e, orders o
   WHERE e.employee_id = o.sales_rep_id
   GROUP BY e.employee_id);

SELECT * FROM bonuses;

EMPLOYEE_ID      BONUS
----------- ----------
        153        100
        154        100
        155        100
        156        100
        158        100
        159        100
        160        100
        161        100
        163        100

MERGE INTO bonuses D
   USING (SELECT employee_id, salary, department_id FROM employees
   WHERE department_id = 80) S
   ON (D.employee_id = S.employee_id)
   WHEN MATCHED THEN UPDATE SET D.bonus = D.bonus + S.salary*.01
     DELETE WHERE (S.salary > 8000)
   WHEN NOT MATCHED THEN INSERT (D.employee_id, D.bonus)
     VALUES (S.employee_id, S.salary*0.1)
     WHERE (S.salary <= 8000);

EMPLOYEE_ID      BONUS
----------- ----------
        153        180
        154        175
        155        170
        159        180
        160        175
        161        170
        179        620
        173        610
        165        680
        166        640
        164        720
        172        730
        167        620
        171        740