oracle merge 误区

来源：互联网发布：怎么找回淘宝店铺编辑：程序博客网时间：2024/06/18 04:06

1、如果在d1表中有一个字段有多个，并且在使用merge是使用该字段关联会产生ORA-30926的错误，如在上一节面我们使用相关联的字段是deptno，如果在d1中出现多行deptno的值一样就会报错。

--构造实验环境（延续上一节的d1、d2表）SQL> update d1 set deptno = 10 where deptno = 30;1 row updated.SQL> select * from d1;    DEPTNO DNAME          LOC---------- -------------- -------------        10 ACCOUNTING     NEW YORK        20 RESEARCH       DALLAS        10 SALES          CHICAGO        40 OPERATIONS     BOSTON

2、执行merge操作，使用d1表的记录去更新d2表的记录，查看结果

MERGE INTO d2USING d1ON (d1.deptno = d2.deptno)WHEN MATCHED THEN        UPDATE SET d2.loc = d1.loc || '...';USING d1      *ERROR at line 2:ORA-30926: unable to get a stable set of rows in the source tables

从上面得到的错误：没有稳定值。可以得出在 ON() 子句中的条件是d1.deptno = 10不具备有唯一性，所以建议在关联的列上创建主键或者创建unique index。还有一个解决办法就是将值相等的行合并成一行来处理（请注意：这样做可能会改变需求）。

3、如果将d1和d2倒过来（使用d2的记录去更新d2的记录）就不会发生ORA-30926，原因是在d2中的deptno的值是唯一。

MERGE INTO d1USING d2ON (d1.deptno = d2.deptno)WHEN MATCHED THEN        UPDATE set d1.loc = d2.loc || '...'SQL> select * from d1;    DEPTNO DNAME          LOC---------- -------------- -------------        10 ACCOUNTING     FU JIAN...        20 RESEARCH       DALLAS        10 SALES          FU JIAN...        40 OPERATIONS     BOSTON

4、delete子句的where必须在最后

--环境SQL> select * from d1;    DEPTNO DNAME          LOC---------- -------------- -------------        10 ACCOUNTING     NEW YORK        20 RESEARCH       DALLAS        30 SALES          CHICAGO        40 OPERATIONS     BOSTONSQL> select * from d2;    DEPTNO DNAME          LOC---------- -------------- -------------        10 ACCOUNTING     FU JIAN        30 SALES          CHICAGO--错误示例MERGE INTO d2USING d1ON (d1.deptno = d2.deptno)WHEN MATCHED THEN        UPDATE SET d2.loc = d1.loc || '...'        DELETE WHERE (d2.deptno = 10)        WHERE d1.deptno = 10;        WHERE d1.deptno = 10        *ERROR at line 7:ORA-00933: SQL command not properly ended--正确示例MERGE INTO d2USING d1ON (d1.deptno = d2.deptno)WHEN MATCHED THEN        UPDATE SET d2.loc = d1.loc || '...'        WHERE d1.deptno = 10        DELETE WHERE (d2.deptno = 10);

5、在delete 子句只可以伤处目标表，而不能删除源表，以上实验中我们用的都是delete where (d1.deptno)。

SQL> select * from d1;    DEPTNO DNAME          LOC---------- -------------- -------------        10 ACCOUNTING     NEW YORK        20 RESEARCH       DALLAS        30 SALES          CHICAGO        40 OPERATIONS     BOSTONSQL> select * from d2;    DEPTNO DNAME          LOC---------- -------------- -------------        10 ACCOUNTING     FU JIAN        30 SALES          CHICAGO--DELETE WHERE (d1.deptno = 10)MERGE INTO d2USING d1ON (d1.deptno = d2.deptno)WHEN MATCHED THEN        UPDATE SET d2.loc = d1.loc || '...'        DELETE WHERE (d1.deptno = 10)SQL> select * from d2;    DEPTNO DNAME          LOC---------- -------------- -------------        30 SALES          CHICAGO...SQL> rollback;Rollback complete.--使用DELETE WHERE (d2.deptno = 10)MERGE INTO d2USING d1ON (d1.deptno = d2.deptno)WHEN MATCHED THEN        UPDATE SET d2.loc = d1.loc || '...'        DELETE WHERE (d2.deptno = 10)SQL> select * from d2;    DEPTNO DNAME          LOC---------- -------------- -------------        30 SALES          CHICAGO...

6、merge当引用表中没数据的解决办法

SQL> select * from d1;no rows selectedSQL> select * from d2;    DEPTNO DNAME          LOC---------- -------------- -------------        10 ACCOUNTING     FU JIAN        30 SALES          CHICAGOMERGE INTO d2USING d1ON (d1.deptno = d2.deptno)WHEN MATCHED THEN        UPDATE SET d2.loc = 'AAAAA'WHEN NOT MATCHED THEN        INSERT VALUES(20, 'aaaaa', 'AAAAA')0 rows merged.SQL> select * from d2;    DEPTNO DNAME          LOC---------- -------------- -------------        10 ACCOUNTING     FU JIAN        30 SALES          CHICAGO

按道理说：当d1中没有找到数据时应该会执行INSERT语句的。但是，从上可以看到并没有执行INSERT语句。我们可以这样取巧的使用让他实行INSERT语句：

MERGE INTO d2USING (SELECT COUNT(*) CNT FROM d1) dON (d.cnt <> 0)WHEN MATCHED THEN        UPDATE SET d2.loc = 'AAAAA'WHEN NOT MATCHED THEN        INSERT VALUES(20, 'aaaaa', 'AAAAA')SQL> select * from d2;    DEPTNO DNAME          LOC---------- -------------- -------------        10 ACCOUNTING     FU JIAN        30 SALES          CHICAGO        20 aaaaa          AAAAA

0 0