【MERGE】展示MERGE的强大魅力

2019-12-31 17:19:50

除了常见的增、删、改操作是DML（Data Manipulation Language，数据操纵语言）外还有一种操作叫做MERGE。这里展示一下MERGE强大的“组合拳”功能。

1.MERGE语法一例，后面的实验会用到此SQL语句
MERGE INTO t_merge a
     USING (SELECT   '007' code, 'Andy' name FROM DUAL) b
        ON (a.code = b.code)
WHEN MATCHED
THEN
   UPDATE SET a.name = b.name
WHEN NOT MATCHED
THEN
   INSERT (code, name) VALUES (b.code, b.NAME)
/

2.激动人心的实验现在开始，请各位“童鞋”注意
1）创建测试表，并初始化测试数据
sec@ora10g> create table t_merge (code varchar2(10), name varchar2(20));

sec@ora10g> insert into t_merge values ('006','Michael');
sec@ora10g> insert into t_merge values ('006','Wendy');
sec@ora10g> insert into t_merge values ('008','Peter');
sec@ora10g> insert into t_merge values ('008','Tina');

sec@ora10g> select * from t_merge;

CODE       NAME
---------- --------------------
006        Michael
006        Wendy
008        Peter
008        Tina

2）测试种情况：找不到匹配则插入
sec@ora10g> MERGE INTO   t_merge a
2       USING   (    SELECT   '007' code, 'Andy' name FROM DUAL) b
3          ON   (a.code = b.code)
4 WHEN MATCHED
5 THEN
6     UPDATE SET a.name = b.name
7 WHEN NOT MATCHED
8 THEN
9     INSERT              (code, name)
10         VALUES   (b.code, b.NAME)
11 /

1 row merged.

sec@ora10g> select * from t_merge;

CODE       NAME
---------- --------------------
006        Michael
006        Wendy
008        Peter
008        Tina
007        Andy

3）测试第二种情况：找到记录则更新
sec@ora10g> delete from t_merge where code = '007';
sec@ora10g> insert into t_merge values ('007','Hou');
sec@ora10g> insert into t_merge values ('007','Secooler');

sec@ora10g> select * from t_merge;

CODE       NAME
---------- --------------------
006        Michael
006        Wendy
008        Peter
008        Tina
007        Hou
007        Secooler

6 rows selected.

sec@ora10g> MERGE INTO   t_merge a
2       USING   (    SELECT   '007' code, 'Andy' name FROM DUAL) b
3          ON   (a.code = b.code)
4 WHEN MATCHED
5 THEN
6     UPDATE SET a.name = b.name
7 WHEN NOT MATCHED
8 THEN
9     INSERT              (code, name)
10         VALUES   (b.code, b.NAME)
11 /

2 rows merged.

sec@ora10g> select * from t_merge;

CODE       NAME
---------- --------------------
006        Michael
006        Wendy
008        Peter
008        Tina
007        Andy
007        Andy

6 rows selected.

4）插入与更新并举
sec@ora10g> insert into t_merge values ('006','Michael');
sec@ora10g> insert into t_merge values ('007','Hou');
sec@ora10g> insert into t_merge values ('007','Secooler');
sec@ora10g> insert into t_merge values ('008','Tina');
sec@ora10g> select * From t_merge order by 1;

CODE       NAME
---------- --------------------
006        Michael
007        Hou
007        Secooler
008        Tina

sec@ora10g> create table t_using as select * from t_merge where 1=0;
sec@ora10g> insert into t_using values ('007', 'Andy');
sec@ora10g> insert into t_using values ('009', 'John');
sec@ora10g> insert into t_using values ('010', 'Anna');
sec@ora10g> commit;

sec@ora10g> select * from t_using order by 1;

CODE       NAME
---------- --------------------
007        Andy
009        John
010        Anna

sec@ora10g> MERGE INTO   t_merge a
2       USING   (    SELECT   code, name FROM t_using) b
3          ON   (a.code = b.code)
4 WHEN MATCHED
5 THEN
6     UPDATE SET a.name = b.name
7 WHEN NOT MATCHED
8 THEN
9     INSERT              (code, name)
10         VALUES   (b.code, b.NAME)
11 /

4 rows merged.

sec@ora10g> select * From t_merge order by 1;

CODE       NAME
---------- --------------------
006        Michael
007        Andy
007        Andy
008        Tina
009        John
010        Anna

6 rows selected.

可以得到结论：t_using中的数据已经merge到t_merge表中。

5）【补充参考】具体语法参考Oracle官方文档：
《MERGE》
http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_9016.htm

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

3.小结
MERGE操作特点
MERGE效果：判断数据是否存在，如果存在就更新，否则插入新数据行；
MERGE适用案例：对维度表进行数据装载；对参数表进行修改；
MERGE限制条件：仅能使用select子句，不能直接用变量和常量。但是可以构造成select的字段进行操作。

Good luck.

secooler
11.10.23

-- The End --

分享好友

分享这个小栈给你的朋友们，一起进步吧。