使用merge into时需要注意的问题

9i中merge的出现，使得可以更加方便的处理数据。它的出现，代替了以前繁琐的insert、捕获dup_value_on_index异常、update操作。

比如，假设需要对一个表中的数据实现：“数据存在则更新；不存在则插入”的功能，9i之前只能先删除，再插入，否则会有主键冲突出现；或者直接进行insert，然后捕获dup_value_on_index异常，在异常处理中进行update。

 

而其实，这些操作可以由一个merge代替。

 

merge的功能就是根据指定条件查询数据，若存在，则更新；若不存在，则插入。

 

MERGE语句的语法如下：

MERGE [hint] INTO [schema .] table [t_alias] USING [schema .] { table | view | subquery } [t_alias]

ON ( condition )

WHEN MATCHED THEN merge_update_clause

WHEN NOT MATCHED THEN merge_insert_clause;

 

我们要讲的是使用merge时要注意：作为查询条件的列（在"condition"中）是不可以被更新的（在merge_update_clause中不可更新）。

下面是一个小测试：

 

--1.版本及环境

SQL> select * from v$version;

 

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE    9.2.0.1.0       Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production

SQL> desc test1
 名称                                      是否为空? 类型
 ----------------------------------------- -------- -------------
 A                                                  VARCHAR2(10)

 

SQL> desc test2
 名称                                      是否为空? 类型
 ----------------------------------------- -------- -------------
 A                                                  VARCHAR2(10)
 B                                                  VARCHAR2(3)

 

SQL> select * from test1;

 

A
----------
1
2
3
4
5

 

SQL> select * from test2;

 

A          B
---------- ---
1          11
9          99

 

--2.使用merge更新test1.a（set a = y.b），而test1.a在查询条件中（x.a=y.a）。此时出错，不过错误信息不明确。

SQL> merge into test1 x using test2 y
  2  on ( x.a = y.a )
  3  when matched then update set a = y.b
  4  when not matched then insert values(y.a);
on ( x.a = y.a )
     *
ERROR 位于第 2 行:
ORA-00904: "X"."A": 无效的标识符

 

--3.我们在test1中再增加一列b，更新列b，而不是列a：此时可以成功。

SQL> alter table test1 add b varchar2(10);

 

表已更改。

 

SQL> merge into test1 x using test2 y
  2  on ( x.a = y.a )
  3  when matched then update set b = y.b
  4  when not matched then insert values(y.a, y.b);

 

2 行已合并。

 

--4.我们看看更新后的test1：插入了一条数据，并更新了一条数据。

SQL> select * from test1;

 

A          B
---------- ----------
1          11
2
3
4
5
9          99

 

已选择6行。

 

--5.而且是可以回滚的。

SQL> rollback;

 

回退已完成。

 

SQL> select * from test1;

A          B
---------- ----------
1
2
3
4
5

 

在10.2.0.1中，也是不能更新的，不过提示发生了变化：明确地指出这样是非法的。

SQL> select * from v$version;

 

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

 

--建立和9i中一样的环境

略

 

SQL> merge into test1 x using test2 y
  2  on ( x.a = y.a )
  3  when matched then update set a = y.b
  4  when not matched then insert values(y.a);
on ( x.a = y.a )
     *
ERROR 位于第 2 行:
ORA-38104: 无法更新 ON 子句中引用的列: "X"."A"

 

我们看一下oracle对这个错误的解释：

ORA-38104:	Columns referenced in the ON Clause cannot be updated: string
Cause:	LHS of UPDATE SET contains the columns referenced in the ON Clause
Action:	none

 

由此可知，在on条件中的列是不可以更新的。只是9i中没有给出明确的提示，10g中给出了明确的提示。

其实也很容易理解，on条件中的列是用来检索两张表的，若可以被修改，那检索条件就是在运行期间可以动态改变的了。而这是oracle所不允许的：oracle在发出一个查询请求时，结果集已经确定的了。

 

转载于:https://www.cnblogs.com/lynnwang/p/4372559.html