开始
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;
结束
本文转自健哥的数据花园博客园博客，原文链接：http://www.cnblogs.com/gaojian/archive/2012/11/13/2768138.html，如需转载请自行联系原作者
