ANBOB™

今天 有网友在说9i merge用法的问题，我小测了一下,感觉区别还是蛮大的

C:\>sqlplus anbob/anbob@192.168.3.156/anbob

SQL*Plus: Release 11.1.0.6.0 - Production on 星期五 6月 17 09:48:44 2011

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

连接到:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

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> create table tsouce(id int,type int,name varchar2(20));

表已创建。

SQL> begin
2  for i in 1..20 loop
3  insert into tsouce values(i,i*2,'anbob'||i);
4  end loop;
5  end;
6  /

PL/SQL 过程已成功完成。

SQL> create table ttarget(id int,type int,name varchar2(20),remark varchar2(111));

表已创建。

SQL> insert into ttarget values(1,2,'anbob1','i inserted');

已创建 1 行。

SQL> select * from tsouce where rownum<10;

ID       TYPE NAME
---------- ---------- --------------------
1          2 anbob1
2          4 anbob2
3          6 anbob3
4          8 anbob4
5         10 anbob5
6         12 anbob6
7         14 anbob7
8         16 anbob8
9         18 anbob9

已选择9行。

SQL> insert into ttarget values(2,4,'anbob99','i inserted');

已创建 1 行。

SQL> commit;

提交完成。

SQL> select * from target
2  ;
select * from target
*
第 1 行出现错误:
ORA-00942: 表或视图不存在

SQL> select * from ttarget
2  ;

ID       TYPE NAME
---------- ---------- --------------------
REMARK
--------------------------------------------------------------------------------
1          2 anbob1
i inserted

2          4 anbob99
i inserted

SQL> col remark for a20
SQL> run
1  select * from ttarget
2*

ID       TYPE NAME                 REMARK
---------- ---------- -------------------- --------------------
1          2 anbob1               i inserted
2          4 anbob99              i inserted

SQL> MERGE INTO ttarget t
2     USING (SELECT ID, TYPE, NAME
3              FROM tsouce) s
4     ON (t.ID = s.ID AND t.TYPE = s.TYPE)
5     WHEN MATCHED THEN
6        UPDATE
7           SET t.NAME = s.NAME
8     WHEN NOT MATCHED THEN
9        INSERT (id,type,name)
10        VALUES (s.ID, s.TYPE, s.NAME);

20 行已合并。

SQL> select * from ttarget;

ID       TYPE NAME                 REMARK
---------- ---------- -------------------- --------------------
1          2 anbob1               i inserted
2          4 anbob2               i inserted
4          8 anbob4
7         14 anbob7
19         38 anbob19
17         34 anbob17
16         32 anbob16
15         30 anbob15
14         28 anbob14
20         40 anbob20
18         36 anbob18

ID       TYPE NAME                 REMARK
---------- ---------- -------------------- --------------------
8         16 anbob8
10         20 anbob10
13         26 anbob13
5         10 anbob5
12         24 anbob12
6         12 anbob6
3          6 anbob3
11         22 anbob11
9         18 anbob9

已选择20行。

SQL>
SQL> MERGE INTO ttarget t
2     USING (SELECT ID, TYPE, NAME
3              FROM tsouce) s
4     ON (t.ID = s.ID AND t.TYPE = s.TYPE)
5     WHEN MATCHED THEN
6        UPDATE
7           SET t.NAME = s.NAME ;
SET t.NAME = s.NAME
*
第 7 行出现错误:
ORA-00905: 缺少关键字

SQL> MERGE INTO ttarget t
2     USING (SELECT ID, TYPE, NAME
3              FROM tsouce) s
4     ON (t.ID = s.ID AND t.TYPE = s.TYPE)
5     WHEN MATCHED THEN
6        UPDATE
7           SET t.NAME = s.NAME where 1=1
8     WHEN NOT MATCHED THEN
9        INSERT (id,type,name)
10        VALUES (s.ID, s.TYPE, s.NAME);
SET t.NAME = s.NAME where 1=1
*
第 7 行出现错误:
ORA-00905: 缺少关键字

note :

merge 一般用于做两表同步，发现9i还是有一定的限制，必需要用 MATCHED 和NO MATCHED，而且在update \INSERT 中不能有WHERE，可以在ON 或USING后在子查询实现，这点在以后的版本已可以实现，而且在10G中又增加了delete

官方说法

http://cn.forums.oracle.com/forums/thread.jspa?threadID=991002

http://download.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_915a.htm#2080942

打赏

微信扫一扫，打赏作者吧～