Oracle中多表关联批量插入批量更新与批量删除操作

--创建部门表 dept:

CREATE TABLE dept

(

  deptno NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY ,

  dname VARCHAR2(14) ,

  loc VARCHAR2(13) ,

  CREATEDTIME DATE ,

  UPDATEDTIME DATE ,

  CREATEDBY NUMBER(7,0),

  UPDATEDBY NUMBER(7,0)

) ;

--创建员工表 emp:

CREATE TABLE emp

(  empno NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY ,

   ename VARCHAR2(10),

   job VARCHAR2(15),

   mgr NUMBER(4),

   hiredate DATE ,

   sal NUMBER(7,2),

   comm NUMBER(7,2),

   deptno NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT,

   CREATEDTIME DATE ,

   UPDATEDTIME DATE ,

   CREATEDBY NUMBER(7,0),

   UPDATEDBY NUMBER(7,0)

);

--创建员工表 emp_copy:

CREATE TABLE emp_copy

(  empno NUMBER(4),

   ename VARCHAR2(10),

   job VARCHAR2(15),

   mgr NUMBER(4),

   hiredate DATE ,

   sal NUMBER(7,2),

   comm NUMBER(7,2),

   deptno NUMBER(2),

   CREATEDTIME DATE ,

   UPDATEDTIME DATE ,

   CREATEDBY NUMBER(7,0),

   UPDATEDBY NUMBER(7,0)

  );

--自定义一个序列

  create sequence emp_sequence

     increment by 1 --每次增加几个，我这里是每次增加1

     start with 1  --从1开始计数

     nomaxvalue   --不设置最大值

     nocycle     --一直累加，不循环

     nocache    --不建缓冲区

--插入dept表数据:

INSERT INTO dept VALUES (10, 'ACCOUNTING' , 'NEW YORK' ,sysdate,sysdate,123123,123123);

INSERT INTO dept VALUES (20, 'RESEARCH' , 'DALLAS' ,sysdate,sysdate,123123,123123);

INSERT INTO dept VALUES (30, 'SALES' , 'CHICAGO' ,SYSDATE,SYSDATE,123123,123123);

INSERT INTO dept VALUES (40, 'OPERATIONS' , 'BOSTON' ,sysdate,sysdate,123123,123123);

--插入emp表数据:

INSERT INTO emp VALUES (7369, 'SMITH' , 'CLERK' ,7902,to_date( '17-12-1980' , 'dd-mm-yyyy' ),800, NULL ,20,sysdate,sysdate,123123,123123);

INSERT INTO emp VALUES (7499, 'ALLEN' , 'SALESMAN' ,7698,to_date( '20-2-1981' , 'dd-mm-yyyy' ),1600,300,30,sysdate,sysdate,123123,123123);

INSERT INTO emp VALUES (7521, 'WARD' , 'SALESMAN' ,7698,to_date( '22-2-1981' , 'dd-mm-yyyy' ),1250,500,30,SYSDATE,SYSDATE,123123,123123);

INSERT INTO emp VALUES (7566, 'JONES' , 'MANAGER' ,7839,to_date( '2-4-1981' , 'dd-mm-yyyy' ),2975, NULL ,20,sysdate,sysdate,123123,123123);

INSERT INTO emp VALUES (7654, 'MARTIN' , 'SALESMAN' ,7698,to_date( '28-9-1981' , 'dd-mm-yyyy' ),1250,1400,30,SYSDATE,SYSDATE,123123,123123);

INSERT INTO emp VALUES (7698, 'BLAKE' , 'MANAGER' ,7839,to_date( '1-5-1981' , 'dd-mm-yyyy' ),2850, NULL ,30,sysdate,sysdate,123123,123123);

INSERT INTO emp VALUES (7782, 'CLARK' , 'MANAGER' ,7839,to_date( '9-6-1981' , 'dd-mm-yyyy' ),2450, NULL ,10,SYSDATE,SYSDATE,123123,123123);

INSERT INTO emp VALUES (7788, 'SCOTT' , 'ANALYST' ,7566,to_date( '19-4-87' , 'dd-mm-yyyy' ),3000, NULL ,20,SYSDATE,SYSDATE,123123,123123);

INSERT INTO emp VALUES (7839, 'KING' , 'PRESIDENT' , NULL ,to_date( '17-11-1981' , 'dd-mm-yyyy' ),5000, NULL ,10,sysdate,sysdate,123123,123123);

INSERT INTO emp VALUES (7844, 'TURNER' , 'SALESMAN' ,7698,to_date( '8-9-1981' , 'dd-mm-yyyy' ),1500,0,30,SYSDATE,SYSDATE,123123,123123);

INSERT INTO emp VALUES (7876, 'ADAMS' , 'CLERK' ,7788,to_date( '23-5-87' , 'dd-mm-yyyy' ),1100, NULL ,20,sysdate,sysdate,123123,123123);

INSERT INTO emp VALUES (7900, 'JAMES' , 'CLERK' ,7698,to_date( '3-12-1981' , 'dd-mm-yyyy' ),950, NULL ,30,sysdate,sysdate,123123,123123);

INSERT INTO emp VALUES (7902, 'FORD' , 'ANALYST' ,7566,to_date( '3-12-1981' , 'dd-mm-yyyy' ),3000, NULL ,20,SYSDATE,SYSDATE,123123,123123);

INSERT INTO emp VALUES (7934, 'MILLER' , 'CLERK' ,7782,to_date( '23-1-1982' , 'dd-mm-yyyy' ),1300, NULL ,10,sysdate,sysdate,123123,123123);

INSERT INTO emp_copy

SELECT emp_sequence.nextVal,C.* FROM (

   SELECT

   e.ename ,

   e.JOB ,

   e.mgr ,

   e.hiredate,

   e.sal ,

   e.comm ,

   e.deptno,

   SYSDATE AS CREATEDTIME,

   SYSDATE AS UPDATEDTIME,

   123124 AS CREATEDBY,

   123124 as UPDATEDBY

   FROM emp e, dept d

   WHERE e.deptno = d.deptno

   AND d.loc= 'CHICAGO'

   and e.sal>=1500

  )C;

MERGE INTO emp e

  USING

  (

   SELECT e1.job, e1.ename,e1.comm FROM emp e1, dept d1

   WHERE e1.deptno = d1.deptno

   AND d1.loc= 'CHICAGO'

   and e1.job= 'SALESMAN'

  )t

  ON (

   e.job = t.job and e.ename = t.ename

  )

  WHEN MATCHED THEN

   UPDATE

    set e.comm= t.comm+200

DELETE FROM

(

  SELECT c.*

  FROM emp c,dept d

  WHERE d.deptno = c.deptno AND d.loc = 'CHICAGO'

  and c.sal < 1500

);