Oracle中的Merge函数（批量更新删除）Oracle中的Merge函数(批量更新/删除)
摘⾄⽹上的⼏个例⼦
⼀、
*****************************************************************
Merger into是oracle从9i开始增加的⼀个语句，从merge的字⾯上的意思：合并，兼并不难理解merge在oracle中的含义，merge在oracle所起的作⽤是：如果你从以组值中有选择的更新和插⼊到到⼀张表，具体来说是：如果该表中已经匹配了这组值的某些条件，那么可以使⽤这组值的部分数据来更新这个表的，如果该表中⽆法匹配了这组值的某些条件，那么可以使⽤这组值的数据来为这个表新增⼀条数据。⽆论你在使⽤任何DBMS，你总是难以避免的将会遇到上⾯提到的这种需求，如果你不使⽤merge语句，你将会不得不在程序中增加⼤段的代码，或者是在oracle⽤很长的代码来实现。好在现在我们有了merge，可以帮我们省下很多时间。
好了废话少说：
Merge 的基本语法是这样的
Merge into table[alias]
Using table or sql query [alias]
On condition
When matched then
Update set ….
When not matched then
Insert values…
以上是merge的基本语法，其中alias是为表或者查询写的别名
如果你看着空洞的语法觉得头很痛，看下⾯的例⼦吧
⾸先我们创建两个表
create table test1(id int,name varchar(20));
create table test2(id int,name varchar(20))
然后随意插⼊⼏⾏数据
insert into test1 values(1,’hi’);
insert into test1 values(2,’hello’);
insert into test2 values(2,’你好’);
insert into test2 values(3,’morning’);
下⾯我们要使⽤了merge了，将test2中的数据有选择地转移或者更新到test1中
如果你运⾏了下下⾯的merge语句，你将会的到⼀个错误，这是因为oracle规定在
merge语句中不能更新作为连接的列，也就是on后⾯的那些列
merge into test1 t1
using test2 t2
on (t1.id = t2.id)
when matched then
update set t1.id = t2.id
when not matched then
insert values(t2.id,t2.name);
所以将会得到如下的错误，虽然这个错误翻译的并不怎么样，甚⾄带有明显的误导
on (t1.id = t2.id)
*
ERROR 位于第 3 ⾏:
ORA-00904: “T1″.”ID”: ⽆效的标识符
好了，知错就改，我们再来运⾏下⾯的merge语句。
merge into test1 t1
using test2 t2
on (t1.id = t2.id)
when matched then
update set t1.name = t2.name
when not matched then
insert values(t2.id,t2.name)
成功执⾏了，我们来验证⼀下。
select * from test1;
ID NAME
—— —————-
1 hi
2 你好
3 morning
⾄此，你已经掌握了merge语句中的⼤部分。但我们还要提醒⼀些特殊情况。
如果我们再向test2中增加⼀条语句
insert into test2 values(2,’早’)
再执⾏我们以已经成功执⾏过的merge语句，将会遇到下⾯的错误
SQL> merge into test1 t1
2 using test2 t2
3 on (t1.id = t2.id)
4 when matched then
5 update set t1.name = t2.name
6 when not matched then
7 insert values(t2.id,t2.name)
8 ;
using test2 t2
*
ERROR 位于第 2 ⾏:
ORA-30926: ⽆法在源表中获得⼀组稳定的⾏
这是因为当执⾏到t1.id = t2.id =2时，test2表中对应了两条记录，⽆法进⾏更新或者插⼊。所以就出错了。所以你应该明⽩oracle中的merge语句应该保证on中的条件的唯⼀性，
另外⼀点需要说明的是using关键字后⾯可以接表，当然也可以接其他的select语句做出来的⼀个类视图，oracle中的这种结构，我们在前⾯已经介绍多次，在此不作介绍。
⼆、
*****************************************************************
Merge Into 语句代替Insert/Update在Oracle中的应⽤实战收藏
动机：
想在Oracle中⽤⼀条SQL语句直接进⾏Insert/Update的操作。
说明：
在进⾏SQL语句编写时，我们经常会遇到⼤量的同时进⾏Insert/Update的语句，
也就是说当存在记录时，就更新(Update)，不存在数据时，就插⼊(Insert)。
实战：
接下来我们有⼀个任务，有⼀个表T，有两个字段a,b，我们想在表T中做
Insert/Update，如果存在，则更新T中b的值，如果不存在，则插⼊⼀条记录。在
Microsoft的SQL语法中，很简单的⼀句判断就可以了，SQL Server中的语法如下
：
if exists(select 1 from T where T.a='1001' ) update T set T.b=2 Where
T.a='1001' else insert into T(a,b) values('1001',2);
以上语句表明当T表中如果存在a='1001' 的记录的话，就把b的值设为2，否则就
Insert⼀条a='100'，b=2的记录到T中。
但是接下来在Oracle中就遇到⿇烦了，记得在Oracle 9i之后就有⼀条Merge into
的语句可以同时进⾏Insert 和Update的吗，Merge的语法如下：
MERGE INTO table_name alias1
USING (table|view|sub_query) alias2
ON (join condition)
WHEN MATCHED THEN
UPDATE table_name
SET col1 = col_val1,
col2 = col2_val
WHEN NOT MATCHED THEN
INSERT (column_list) VALUES (column_values);
上⾯的语法⼤家应该都容易懂吧，那我们按照以上的逻辑再写⼀次。
MERGE INTO T T1
USING (SELECT a,b FROM T WHERE t.a='1001') T2
ON ( T1.a=T2.a)
WHEN MATCHED THEN
UPDATE SET T1.b = 2
WHEN NOT MATCHED THEN
INSERT (a,b) VALUES('1001',2);
以上的语句貌似很对是吧，实际上，该语句只能进⾏更新，⽽⽆法进⾏Insert，
错误在哪⾥呢？
其实在Oracle中Merge语句原先是⽤来进⾏整表的更新⽤的，也就是ETL⼯具⽐较
常⽤的语法，重点是在Using上。
⽤中⽂来解释Merge语法，就是：
在alias2中Select出来的数据，每⼀条都跟alias1进⾏ ON (join condition)的⽐较，如果匹配，就进⾏更新的操作(Update)，如果不匹配，就进⾏插⼊操作(Insert)。
因此，严格意义上讲，”在⼀个同时存在Insert和Update语法的Merge语句中，总共Insert/Update 的记录数，就是Using语句中alias2的记录数。”
以上这句话也就很好的解释了在上⾯写的语句为何只能进⾏Update，⽽不能进⾏Insert了，因为都Select不到数据，如何能进⾏Insert呢:)
接下来要改成正确的语句就容易多了，如下：
MERGE INTO T T1
USING (SELECT '1001' AS a,2 AS b FROM dual) T2
ON ( T1.a=T2.a)
WHEN MATCHED THEN
UPDATE SET T1.b = T2.b
WHEN NOT MATCHED THEN
INSERT (a,b) VALUES(T2.a,T2.b);
查询结果，OK!
注意：
如果不懂Merge语句的原理，Merge语句是⼀条⽐较危险的语句，特别是在您只想
更新⼀条记录的时候，因为不经意间，你可能就把整表的数据都Update了⼀
遍.....汗!!!
我曾经犯过的⼀个错误如下所⽰，⼤家看出来是什么问题了吗？
MERGE INTO T T1
USING (SELECT Count(*) cnt FROM T WHERE T.a='1001') T2
ON (T2t>0)
WHEN MATCHED THEN
UPDATE SET T1.b = T2.b
WHEN NOT MATCHED THEN
INSERT (a,b) VALUES(T2.a,T2.b);
三、
*********************************************************
所有的MIS系统都存在⼀个同样的需求，就是对于特定的数据，在⼀次批量操作过程中，如果数据已经存在，则对存在的数据按照现有情况进⾏更新，如果不存在，则需要加⼊数据库。这时，我们就可以考虑采⽤ Oracle 的 MERGE 函数，其具体⽤法如下：
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_r here] AND [...]...)
WHEN
sql中update什么意思MATHED
THEN
[here you can execute some update sql or something else ]
WHEN
NOT MATHED
THEN
[execute something else here ! ]
下⾯我再进⾏详细的说明：
上述代码格式中的加粗字体表⽰为 Oracle 关键字，[]以及其中的⽂字均是说明，在实际使⽤中不应有 [ words ] 出现。要注意（）[圆括号]也是程序的组成部分。
为了能够使问题与实际问题更加贴切，不妨假设我们现在要给计算机系某个班的学⽣批量录⼊学⽣成绩。但是，录⼊时，如果学⽣的成绩已经存在时，⽼师只想对成绩进⾏修改，⽽如果成绩不存在则直
接添加到库中。我们就⽼师的这些需求来构造⼀个执⾏语句。
DEFINE TABLE :
SCORE : using for save the students' score informations
STUDENTS : the base information of students
DEFINE COLUMNS :
STUNO : the students' ID in the University
STUNAME : students' name
COURSENAME : course name
COURSESCORE : the study-results of the reference course
CLASSNAME : where the students study in
STUGRADE : the students grade
TERMNAME : the term which the reference course studied
NOW BEAGIN TO WRITE DOWN THE STATEMENT HERE BLOW THIS LINE !
MERGE INTO SCORE S
USING
(
SELECT A.*,B.*,? MYSCORE FROM SCORE A,STUDENT B
WHERE
A.CLASSNO=? AND A.GRADE=?
AND A.TERMNAME=? AND A.COURSENAME=?
A.STUNO=
B.STUNO(+)
)
X
ON
(S.STUNO=X.STUNO)
WHEN
MATHED
THEN
UPDATE SET COURSESCORE=X.MYSCORE
WHEN
NOT MATHED
THEN
INSERT
(
STUNO,STUNAME,COURSENAME,COURSESCORE,
CLASSNAME,STUGRADE,TERMNAME
)
VALUES
(
X.STUNO,X.STUNAME,X.COURSENAME,X.MYSCORE,
X.CLASSNAME,X.STUGRADE,X.TERMNAME
);
注意到 MERGE 语句在最后的“；”（分号），这仅仅带到 MERGE 为⼀条完整的SQL 语句这时，如果你需要在你的 Java 程序中使⽤上述⽅法执⾏相应操作，则仅需要将其放⼊⼀个 for 循环中即可。由于是批量更新数据，因此，如果你不想对中间出现异常的数据进⾏提交，导致数据的不完整，则可以考
虑使⽤ Java 的事务回滚机制。具体⽰例代码如下：
public yourMethod(statement,...){
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论