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: 缺少关键字

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