Oracle的 merge 实验

开始

PL/SQL 的培训例子有问题。我的验证如下：

SQL> select empno, ename from emp_cpy;        
        
     EMPNO ENAME        
---------- ----------        
      7900 gaoxx        
      7902 jianxx        
        
SQL>         

SQL> select empno, ename from emp;

     EMPNO ENAME
---------- ----------
      7369 SMITH
      7499 ALLEN
      7521 WARD
      7566 JONES
      7654 MARTIN
      7698 BLAKE
      7782 CLARK
      7788 SCOTT
      7839 KING
      7844 TURNER
      7876 ADAMS

     EMPNO ENAME
---------- ----------
      7900 JAMES
      7902 FORD
      7934 MILLER

14 rows selected.

SQL> 

SQL> select empno, ename from emp;

     EMPNO ENAME
---------- ----------
      7369 SMITH
      7499 ALLEN
      7521 WARD
      7566 JONES
      7654 MARTIN
      7698 BLAKE
      7782 CLARK
      7788 SCOTT
      7839 KING
      7844 TURNER
      7876 ADAMS

     EMPNO ENAME
---------- ----------
      7900 JAMES
      7902 FORD
      7934 MILLER

14 rows selected.

SQL> 

我的merge 语句：

set serveroutput on;        
        
  DECLARE        
    v_empno emp.empno%TYPE := 7900;        
  BEGIN        
    MERGE INTO emp_cpy c        
       USING emp e        
       ON (e.empno = v_empno)        
    WHEN MATCHED THEN        
       UPDATE SET        
         c.empno = e.empno, c.ename=e.ename,        
         c.job = e.job, c.mgr = e.mgr,        
         c.hiredate = e.hiredate, c.sal=e.sal,        
         c.comm=e.comm,c.deptno=e.deptno        
    WHEN NOT MATCHED THEN        
       INSERT VALUES(e.empno,e.ename,e.job,e.mgr,e.hiredate,        
       e.sal,e.comm,e.deptno);          
            
  END; 

执行后：

发现并没有运行 update ，而只是 insert 了。这是因为 on 的条件里根本没有提及 emp_cpy 表的任何字段：

SQL> select empno,ename from emp_cpy;        
        
     EMPNO ENAME        
---------- ----------        
      7369 SMITH        
      7499 ALLEN        
      7521 WARD        
      7566 JONES        
      7654 MARTIN        
      7698 BLAKE        
      7782 CLARK        
      7788 SCOTT        
      7839 KING        
      7844 TURNER        
      7876 ADAMS        
        
     EMPNO ENAME        
---------- ----------        
      7900 JAMES        
      7902 FORD        
      7934 MILLER        
      7900 gaoxx        
      7902 jianxx        
        
16 rows selected.        

正确的作法应当是：

BEGIN

MERGE INTO emp_cpy c
       USING emp e
       ON (e.empno = c.empno) 
WHEN MATCHED THEN
       UPDATE SET
       c.ename=e.ename 
WHEN NOT MATCHED THEN 
    INSERT VALUES(
      e.empno,e.ename,
      e.job,e.mgr,
      e.hiredate,e.sal,e.comm,e.deptno);
END;

 在PL/SQL中，比较完整的是：

set serveroutput on;        
        
  DECLARE        
    v_empno emp.empno%TYPE := 7900;        
  BEGIN        
    MERGE INTO emp_cpy c        
       USING emp e        
       ON (e.empno = v_empno and e.empno=v_empno)        
    WHEN MATCHED THEN        
       UPDATE SET        
         c.empno = e.empno, c.ename=e.ename,        
         c.job = e.job, c.mgr = e.mgr,        
         c.hiredate = e.hiredate, c.sal=e.sal,        
         c.comm=e.comm,c.deptno=e.deptno        
    WHEN NOT MATCHED THEN        
       INSERT VALUES(e.empno,e.ename,e.job,e.mgr,e.hiredate,        
       e.sal,e.comm,e.deptno);          
          
  END; 

结束