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

该文章会分为三部分

1.多表关联批量插入

2.多表关联批量更新

3.多表关联批量删除

首先要明白一点，为什么会有批量这一个概念，无非就是数据太多了，在java端把数据查出来然后在按照100-300的批次进行更新太耗性能了，而且写出来的代码会非常的臃肿，所谓好的实现是用最少的，最精简的代码实现需求，代码越少，留给自己犯错误的机会更少。

还有一个知识点就是多表关联，对于查询肯定是可以多表关联的，其实对于除了查询之外也是可以进行多表关联过滤数据的，从而达到在Oracle中查到目标数据即可更新，从而规避规避以往需要单独查一次数据然后在按照100-300的批次做插入，更新，删除的操作 。

创建必须的表和序列语句:

1. --创建部门表 dept:
2. CREATE TABLE dept
3. (
4. deptno NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,
5. dname VARCHAR2(14) ,
6. loc VARCHAR2(13) ,
7. CREATEDTIME DATE,
8. UPDATEDTIME DATE,
9. CREATEDBY NUMBER(7,0),
10. UPDATEDBY NUMBER(7,0)
11. ) ;
12.  
13. --创建员工表 emp:
14. CREATE TABLE emp
15. ( empno NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
16. ename VARCHAR2(10),
17. job VARCHAR2(15),
18. mgr NUMBER(4),
19. hiredate DATE,
20. sal NUMBER(7,2),
21. comm NUMBER(7,2),
22. deptno NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT,
23. CREATEDTIME DATE,
24. UPDATEDTIME DATE,
25. CREATEDBY NUMBER(7,0),
26. UPDATEDBY NUMBER(7,0)
27. );
28.  
29. --创建员工表 emp_copy:
30. CREATE TABLE emp_copy
31. ( empno NUMBER(4),
32. ename VARCHAR2(10),
33. job VARCHAR2(15),
34. mgr NUMBER(4),
35. hiredate DATE,
36. sal NUMBER(7,2),
37. comm NUMBER(7,2),
38. deptno NUMBER(2),
39. CREATEDTIME DATE,
40. UPDATEDTIME DATE,
41. CREATEDBY NUMBER(7,0),
42. UPDATEDBY NUMBER(7,0)
43. );
44. --自定义一个序列
45. create sequence emp_sequence
46. increment by 1 --每次增加几个，我这里是每次增加1
47. start with 1 --从1开始计数
48. nomaxvalue --不设置最大值
49. nocycle --一直累加，不循环
50. nocache --不建缓冲区
51.  
52. --插入dept表数据:
53. INSERT INTO dept VALUES(10,'ACCOUNTING','NEW YORK',sysdate,sysdate,123123,123123);
54. INSERT INTO dept VALUES (20,'RESEARCH','DALLAS',sysdate,sysdate,123123,123123);
55. INSERT INTO dept VALUES(30,'SALES','CHICAGO',SYSDATE,SYSDATE,123123,123123);
56. INSERT INTO dept VALUES(40,'OPERATIONS','BOSTON',sysdate,sysdate,123123,123123);
57.  
58. --插入emp表数据:
59. INSERT INTO emp VALUES(7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20,sysdate,sysdate,123123,123123);
60. INSERT INTO emp VALUES(7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30,sysdate,sysdate,123123,123123);
61. INSERT INTO emp VALUES(7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30,SYSDATE,SYSDATE,123123,123123);
62. INSERT INTO emp VALUES(7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20,sysdate,sysdate,123123,123123);
63. INSERT INTO emp VALUES(7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30,SYSDATE,SYSDATE,123123,123123);
64. INSERT INTO emp VALUES(7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30,sysdate,sysdate,123123,123123);
65. INSERT INTO emp VALUES(7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10,SYSDATE,SYSDATE,123123,123123);
66. INSERT INTO emp VALUES(7788,'SCOTT','ANALYST',7566,to_date('19-4-87','dd-mm-yyyy'),3000,NULL,20,SYSDATE,SYSDATE,123123,123123);
67. INSERT INTO emp VALUES(7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10,sysdate,sysdate,123123,123123);
68. INSERT INTO emp VALUES(7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30,SYSDATE,SYSDATE,123123,123123);
69. INSERT INTO emp VALUES(7876,'ADAMS','CLERK',7788,to_date('23-5-87','dd-mm-yyyy'),1100,NULL,20,sysdate,sysdate,123123,123123);
70. INSERT INTO emp VALUES(7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30,sysdate,sysdate,123123,123123);
71. INSERT INTO emp VALUES(7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20,SYSDATE,SYSDATE,123123,123123);
72. INSERT INTO emp VALUES(7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10,sysdate,sysdate,123123,123123);

插入数据之后的dept表:

插入数据之后的emp表:

1.多表关联批量插入

要求:批量复制部门dept表 loc在 CHICAGO的,且工资大于1600的人员信息到emp_copy表,emp_copy的empno需要使用序列emp_sequence

1. INSERT INTO emp_copy
2. SELECT emp_sequence.nextVal,C.* FROM (
3. SELECT
4. e.ename ,
5. e.JOB ,
6. e.mgr ,
7. e.hiredate,
8. e.sal ,
9. e.comm ,
10. e.deptno,
11. SYSDATE AS CREATEDTIME,
12. SYSDATE AS UPDATEDTIME,
13. 123124 AS CREATEDBY,
14. 123124 as UPDATEDBY
15. FROM emp e, dept d
16. WHERE e.deptno = d.deptno
17. AND d.loc='CHICAGO'
18. and e.sal>=1500
19. )C;

插入之后的emp_copy表结果:

其中SELECT emp_sequence.nextVal,C.* 这种格式保证了C.*的多条数据都能取到不同的序列值

2.多表关联批量更新

要求:批量更新部门dept表 loc在 CHICAGO的,且职位job为'SALESMAN'的员工,comm在原来基础上加200

操作之前的数据:

1. MERGE INTO emp e
2. USING
3. (
4. SELECT e1.job, e1.ename,e1.comm FROM emp e1, dept d1
5. WHERE e1.deptno = d1.deptno
6. AND d1.loc='CHICAGO'
7. and e1.job='SALESMAN'
8. )t
9. ON (
10. e.job = t.job and e.ename = t.ename
11. )
12. WHEN MATCHED THEN
13. UPDATE
14. set e.comm= t.comm+200

操作之后的数据:

其中e.job = t.job and e.ename = t.ename只是展示可以关联多个条件,这里有一个关键点为 USING ( … ) t 中查询到的是一个list,而merge可以对匹配到的list进行批量更新

3.多表关联批量删除

要求:批量删除部门dept表 loc在 CHICAGO的,且工资小于1500的人员信息

操作之前的数据:

1. DELETE FROM
2. (
3. SELECT c.*
4. FROM emp c,dept d
5. WHERE d.deptno = c.deptno AND d.loc = 'CHICAGO'
6. and c.sal < 1500
7. );

操作之后的数据:

总结

以上所述是小编给大家介绍的Oracle中多表关联批量插入批量更新与批量删除操作，希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对我们网站的支持！
如果你觉得本文对你有帮助，欢迎转载，烦请注明出处，谢谢！