利用merge优化

create table emp_test as select * from emp;create table dept_test as select * from dept;ALTER TABLE EMP_test ADD dname VARCHAR2(50) DEFAULT 'UFO';SQL> set linesize 200SQL> select empno,ename,deptno,dname from emp_test;       EMPNO ENAME  DEPTNO DNAME---------- ---------- ---------- --------------------------------------------------      7369 SMITH      20 UFO      7499 ALLEN      30 UFO      7521 WARD       30 UFO      7566 JONES      20 UFO      7654 MARTIN      30 UFO      7698 BLAKE      30 UFO      7782 CLARK      10 UFO      7788 SCOTT      20 UFO      7839 KING       10 UFO      7844 TURNER      30 UFO      7876 ADAMS      20 UFO     EMPNO ENAME  DEPTNO DNAME---------- ---------- ---------- --------------------------------------------------      7900 JAMES      30 UFO      7902 FORD       20 UFO      7934 MILLER      10 UFO14 rows selected.SQL> select a.empno, a.ename, a.deptno, b.dname  from emp_test a, dept_test b where a.deptno = b.deptno(+)  2    3  ;     EMPNO ENAME  DEPTNO DNAME---------- ---------- ---------- --------------      7934 MILLER      10 ACCOUNTING      7839 KING       10 ACCOUNTING      7782 CLARK      10 ACCOUNTING      7902 FORD       20 RESEARCH      7876 ADAMS      20 RESEARCH      7788 SCOTT      20 RESEARCH      7566 JONES      20 RESEARCH      7369 SMITH      20 RESEARCH      7900 JAMES      30 SALES      7844 TURNER      30 SALES      7698 BLAKE      30 SALESUSQL> UPDATE emp_test emp   SET emp.dname =       (SELECT dept.dname          FROM dept_test dept         WHERE dept.dname IN ('SALES', 'RESEARCH')           AND dept.deptno = emp.deptno);SQL>   2    3    4    5    6  14 rows updated.SQL> select empno,ename,deptno,dname from emp_test;  SQL>      EMPNO ENAME  DEPTNO DNAME---------- ---------- ---------- --------------------------------------------------      7369 SMITH      20 RESEARCH      7499 ALLEN      30 SALES      7521 WARD       30 SALES      7566 JONES      20 RESEARCH      7654 MARTIN      30 SALES      7698 BLAKE      30 SALES      7782 CLARK      10      7788 SCOTT      20 RESEARCH      7839 KING       10      7844 TURNER      30 SALES      7876 ADAMS      20 RESEARCH     EMPNO ENAME  DEPTNO DNAME---------- ---------- ---------- --------------------------------------------------      7900 JAMES      30 SALES      7902 FORD       20 RESEARCH      7934 MILLER      1014 rows selected.SQL> select a.empno, a.ename, a.deptno, b.dname  from emp_test a, dept_test b where a.deptno = b.deptno(+)  2    3  ;     EMPNO ENAME  DEPTNO DNAME---------- ---------- ---------- --------------      7934 MILLER      10 ACCOUNTING      7839 KING       10 ACCOUNTING      7782 CLARK      10 ACCOUNTING      7902 FORD       20 RESEARCH      7876 ADAMS      20 RESEARCH      7788 SCOTT      20 RESEARCH      7566 JONES      20 RESEARCH      7369 SMITH      20 RESEARCH      7900 JAMES      30 SALES      7844 TURNER      30 SALES      7698 BLAKE      30 SALES     EMPNO ENAME  DEPTNO DNAME---------- ---------- ---------- --------------      7654 MARTIN      30 SALES      7521 WARD       30 SALES      7499 ALLEN      30 SALES14 rows selected.SQL> UPDATE emp_test emp   SET emp.dname = (SELECT dept.dname                      FROM dept_test dept                     WHERE dept.dname IN ('SALES', 'RESEARCH')                       AND dept.deptno = emp.deptno) WHERE EXISTS (SELECT dept.dname          FROM dept_test dept         WHERE dept.dname IN ('SALES', 'RESEARCH')           AND dept.deptno = emp.deptno);  2    3    4    5    6    7    8    9  11 rows updated.SQL> select empno,ename,deptno,dname from emp_test;      EMPNO ENAME  DEPTNO DNAME---------- ---------- ---------- --------------------------------------------------      7369 SMITH      20 RESEARCH      7499 ALLEN      30 SALES      7521 WARD       30 SALES      7566 JONES      20 RESEARCH      7654 MARTIN      30 SALES      7698 BLAKE      30 SALES      7782 CLARK      10 UFO      7788 SCOTT      20 RESEARCH      7839 KING       10 UFO      7844 TURNER      30 SALES      7876 ADAMS      20 RESEARCH     EMPNO ENAME  DEPTNO DNAME---------- ---------- ---------- --------------------------------------------------      7900 JAMES      30 SALES      7902 FORD       20 RESEARCH      7934 MILLER      10 UFO14 rows selected.     EMPNO ENAME  DEPTNO DNAME---------- ---------- ---------- --------------      7654 MARTIN      30 SALES      7521 WARD       30 SALES      7499 ALLEN      30 SALES14 rows selected.SQL> SELECT dept.dname          FROM dept_test dept,emp_test emp         WHERE dept.dname IN ('SALES', 'RESEARCH')           AND dept.deptno = emp.deptno           and emp.deptno=10  2    3    4    5  ;no rows selected那么deptno=10对应的记录,找不到的部门会设置为空改为merge：SQL> merge into emp_test empusing (SELECT dept.dname,dept.deptno         FROM dept_test dept        WHERE dept.dname IN ('SALES', 'RESEARCH')) dept on (dept.deptno = emp.deptno)WHEN MATCHED THEN  update set emp.dname = dept.dname  2    3    4    5    6    7  ;11 rows merged.SQL>  select empno,ename,deptno,dname from emp_test;     EMPNO ENAME  DEPTNO DNAME---------- ---------- ---------- --------------------------------------------------      7369 SMITH      20 RESEARCH      7499 ALLEN      30 SALES      7521 WARD       30 SALES      7566 JONES      20 RESEARCH      7654 MARTIN      30 SALES      7698 BLAKE      30 SALES      7782 CLARK      10 UFO      7788 SCOTT      20 RESEARCH      7839 KING       10 UFO      7844 TURNER      30 SALES      7876 ADAMS      20 RESEARCH     EMPNO ENAME  DEPTNO DNAME---------- ---------- ---------- --------------------------------------------------      7900 JAMES      30 SALES      7902 FORD       20 RESEARCH      7934 MILLER      10 UFO14 rows selected.