除了常见的增、删、改操作是DML(Data Manipulation Language,数据操纵语言)外还有一种操作叫做。这里展示一下MERGE强大的“组合拳”功能。
1.MERGE语法一例,后面的会用到此语句
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 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
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)【补充参考】具体语法参考官方文档:
《MERGE》
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的字段进行操作。
阅读(1068) | 评论(0) | 转发(0) |