DML语句-MERGE

根据一个表的数据组织另一个表的数据，一般是对merge的目标表插入新数据或替换掉老数据。
Oracle 10g中MERGE有如下一些改进：
1、UPDATE或INSERT子句是可选的
2、UPDATE和INSERT子句可以加WHERE子句
3、ON条件使用常量过滤谓词来insert所有的行到目标表中,不需要连接源表和目标表
4、UPDATE子句后面可以跟DELETE子句来去除一些不需要的行

1.MERGE

示例：

首先创建表：
SQL>create table PRODUCTS
    (
    PRODUCT_ID INTEGER,
    PRODUCT_NAME VARCHAR2(30),
    CATEGORY VARCHAR2(30)
    );

insert into PRODUCTS values (1501, 'VIVITAR 35MM', '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;

SQL>create table NEWPRODUCTS
    (
    PRODUCT_ID INTEGER,
    PRODUCT_NAME VARCHAR2(30),
    CATEGORY VARCHAR2(30)
    );

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;

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
SQL> select * from newproducts;
PRODUCT_ID PRODUCT_NAME                   CATEGORY
---------- ------------------------------ ------------------------------
      1502 OLYMPUS CAMERA                 ELECTRNCS
      1601 LAMAZE                         	TOYS
      1666 HARRY POTTER                   	TOYS
      1700 WAIT INTERFACE                 	BOOKS

下面我们从表NEWPRODUCTS中合并行到表PRODUCTS中, 但删除category为ELECTRNCS的行.
SQL>MERGE INTO products p
USING newproducts np
ON (p.product_id = np.product_id)
WHEN MATCHED THEN
UPDATE
SET p.product_name = np.product_name,p.category = np.category
DELETE WHERE (p.category = 'ELECTRNCS')
WHEN NOT MATCHED THEN
INSERT
VALUES (np.product_id, np.product_name, np.category);

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
为什么1502不在了，但1501还在？ 因为1502是matched,先被update,然后被delete, 而1501是not matched. 
注意几点：
1）例子里有update,delete和insert。它们是否操作是取决于on子句的，两个表如果符合on条件就是匹配，不符合就是不匹配。
2）匹配了就更新，不匹配则插入。10g后加入了delete语句，这个语句必须在匹配条件下出现。它是一种补充。
3）你必须对操作的表有对象权限
4）ON子句里的字段不能被update子句更新

2. WITH语句

可以使用一个关键字WITH， 为一个子查询块（subquery block）起一个别名。然后在后面的查询中引用该子查询块的别名。
好处：
1）使用with语句，可以避免在select语句中重复书写相同的语句块。
2）with语句将该子句中的语句块执行一次并存储到用户的临时表空间中。
3）使用with语句可以避免重复解析，提高查询效率。

示例：这个with语句完成三个动作
建立一个dept_costs，保存每个部门的工资总和，
建立一个avg_cost，根据dept_costs求出所有部门总工资的平均值，
最后显示出部门总工资值小于部门总工资平均值的那些部门的信息(dname)。

WITH
dept_costs AS (
SELECT d.dname, SUM(e.sal) AS dept_total
FROM emp e, dept d
WHERE e.deptno = d.deptno
GROUP BY d.dname ),  
avg_cost AS (SELECT SUM(dept_total)/COUNT(*) AS dept_avg FROM dept_costs)
SELECT * FROM dept_costs
WHERE dept_total <
(SELECT dept_avg FROM avg_cost)   
ORDER BY dname
/
DNAME          DEPT_TOTAL
-------------- ----------
ACCOUNTING         8750
SALES                	9400、

可以分三个部分来看:
第一AS建立dept_costs，保存每个部门的工资总和。
第二个AS建立avg_cost，根据第一个AS dept_costs求出所有部门总工资的平均值（两个with子程序用逗号分开，第二个使用了第一个别名）。
最后是查询主体，SELECT * FROM... 调用了前两个with下的别名（子查询），显示部门总工资值小于部门总工资平均值的那些部门的信息。

1）with语句中只能有select子句，没有DML子句（注意和merge的区别）。
2）一般将主查询放在最后描述，因为查询主体中要引用的with别名需要在之前定义过。