1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71
| set echo on
select *
from emp1
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------------------- ---------- --------- ---------------------- ------------------------- ---------------------- ---------------------- ----------------------
7369 SMITH CLERK 7902 17/12/1980 00:00:00 3676 20
7499 ALLEN SALESMAN 7698 20/02/1981 00:00:00 7351,98 300 30
7521 WARD SALESMAN 7698 22/02/1981 00:00:00 5743,72 500 30
7566 JONES MANAGER 7839 02/04/1981 00:00:00 13670,05 20
4 rows selected
select *
from emp
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------------------- ---------- --------- ---------------------- ------------------------- ---------------------- ---------------------- ----------------------
7369 SMITH CLERK 7902 17/12/1980 00:00:00 3676 20
7499 ALLEN SALESMAN 7698 20/02/1981 00:00:00 7351,98 300 30
7521 WARD SALESMAN 7698 22/02/1981 00:00:00 5743,72 500 30
7566 JONES MANAGER 7839 02/04/1981 00:00:00 13670,05 20
7654 MARTIN SALESMAN 7698 28/09/1981 00:00:00 5743,72 1400 30
7698 BLAKE MANAGER 7839 01/05/1981 00:00:00 13095,73 30
7782 CLARK MANAGER 7839 09/06/1981 00:00:00 11257,73 10
7788 SCOTT ANALYST 7566 19/04/1987 00:00:00 13784,94 20
7839 KING PRESIDENT 17/11/1981 00:00:00 22974,9 10
7844 TURNER SALESMAN 7698 08/09/1981 00:00:00 6892,49 0 30
7876 ADAMS CLERK 7788 23/05/1987 00:00:00 5054,49 20
7900 JAMES CLERK 7698 03/12/1981 00:00:00 4365,26 30
7902 FORD ANALYST 7566 03/12/1981 00:00:00 13784,94 20
7934 MILLER CLERK 7782 23/01/1982 00:00:00 5973,48 10
14 rows selected
update emp
set sal = sal * 2
14 rows updated
merge into emp1 e1
using emp e
on (e.empno = e1.empno)
when matched then
update
set e1.sal = e.sal
when not matched then
insert (e1.empno, e1.ename, e1.job, e1.mgr, e1.hiredate, e1.sal, e1.comm, e1.deptno)
values (e.empno, e.ename, e.job, e.mgr, e.hiredate, e.sal, e.comm, e.deptno)
14 rows merged
select * from emp1
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------------------- ---------- --------- ---------------------- ------------------------- ---------------------- ---------------------- ----------------------
7369 SMITH CLERK 7902 17/12/1980 00:00:00 7352 20
7499 ALLEN SALESMAN 7698 20/02/1981 00:00:00 14703,96 300 30
7521 WARD SALESMAN 7698 22/02/1981 00:00:00 11487,44 500 30
7566 JONES MANAGER 7839 02/04/1981 00:00:00 27340,1 20
7844 TURNER SALESMAN 7698 08/09/1981 00:00:00 13784,98 0 30
7839 KING PRESIDENT 17/11/1981 00:00:00 45949,8 10
7782 CLARK MANAGER 7839 09/06/1981 00:00:00 22515,46 10
7654 MARTIN SALESMAN 7698 28/09/1981 00:00:00 11487,44 1400 30
7788 SCOTT ANALYST 7566 19/04/1987 00:00:00 27569,88 20
7698 BLAKE MANAGER 7839 01/05/1981 00:00:00 26191,46 30
7934 MILLER CLERK 7782 23/01/1982 00:00:00 11946,96 10
7902 FORD ANALYST 7566 03/12/1981 00:00:00 27569,88 20
7876 ADAMS CLERK 7788 23/05/1987 00:00:00 10108,98 20
7900 JAMES CLERK 7698 03/12/1981 00:00:00 8730,52 30
14 rows selected |
Partager