Shulou(Shulou.com)06/01 Report--

The article will be divided into three parts

1. Multi-table association batch insert

two。 Multi-table association batch update

3. Multi-table association batch deletion

First of all, we have to understand why there is the concept of batch, it is just that there is too much data, it consumes too much performance to look up the data on the java side and then update it in batches of 100-300, and the code written will be very bloated. The so-called good implementation is to implement the requirements with the least and most concise code, the less code, the less opportunities for you to make mistakes.

There is also a knowledge point is multi-table association, for the query can definitely be multi-table association, in fact, in addition to the query can also be multi-table association filter data, so as to find the target data in the Oracle can be updated, so as to avoid the previous need to separately check the data and then in accordance with the 100300 batch to do insert, update, delete operations.

Create the necessary table and sequence statements:

-- create department tables dept:CREATE TABLE dept (deptno NUMBER (2) CONSTRAINT PK_DEPT PRIMARY KEY, dname VARCHAR2 (14), loc VARCHAR2 (13), CREATEDTIME DATE, UPDATEDTIME DATE, CREATEDBY NUMBER (7L0), UPDATEDBY NUMBER (7L0)) -create employee tables 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 (7L2), comm NUMBER (7L2), deptno NUMBER (2) CONSTRAINT FK_DEPTNO REFERENCES DEPT, CREATEDTIME DATE, UPDATEDTIME DATE, CREATEDBY NUMBER (7L0), UPDATEDBY NUMBER (7L0)) -create employee tables emp_copy:CREATE TABLE emp_copy (empno NUMBER (4), ename VARCHAR2 (10), job VARCHAR2 (15), mgr NUMBER (4), hiredate DATE, sal NUMBER (7L2), comm NUMBER (7L2), deptno NUMBER (2), CREATEDTIME DATE, UPDATEDTIME DATE, CREATEDBY NUMBER (7L0), UPDATEDBY NUMBER (7L0)) -- customize a sequence create sequence emp_sequence increment by 1-- add a few at a time, here I add 1 start with 1 at a time-- count nomaxvalue from 1-- do not set the maximum nocycle-- accumulate all the time, do not loop nocache-- do not build a buffer-- insert dept table data: INSERT INTO dept VALUES. INSERT INTO dept VALUES (20GradeRESEARCHRESEARCHRESEARCHRESEARCHCHOPERATIONSSCOPERATIONSSCONSSCORESEARCHOPERATIONSSCONSSCOPERATIONScriptionsysdatePower123123123123);-insert emp table data: INSERT INTO emp VALUES (7369parcels SMITHMYYYY date date ('17-12-1980 minutes)), 800NULLL20sdatedate 123123). INSERT INTO emp VALUES (7499), 7499 (20-2-1981), ('20-2-1981), ('20-2-1981), ('20-2-1981), ('20-2-1981), ('20-2-1981),'('20-2-1981),', ('20-2-1981), ('20-2-1981), ('20-2-1981), ('20-2-1981). INSERT INTO emp VALUES (7566 MANAGERE retro 7839 meme tokyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy INSERT INTO emp VALUES (7698 MANAGERY), ('1-5-1981)), 2850 (Null 30), sysdated123123); INSERT INTO emp VALUES (7782) (7782), ('9-6-1981), 2450 (10SYSDATEL, SYATERE, 123123) INSERT INTO emp VALUES (7788); INSERT INTO emp VALUES (7839)); INSERT INTO emp VALUES (7839). INSERT INTO emp VALUES (7844), 768-886 (8-9-1981), 1500-30, SYSDATE, 123123, 123123, INSERT INTO emp VALUES (7876, 778, 778, 778, and 778, respectively), 1100, LNULLEL, 20sysdate, 123123, 123, 123, 123,) INSERT INTO emp VALUES (7900 JAMEScriptionEncyclopedia), 766 (3-12-1981), 950, Null, 30, sysdatememe, sysdatememe, 123123, 123123, INSERT INTO emp VALUES (7902), 756, 756 (3-12-1981), 3000NULLLlast 20SYATE SDATE, 123123 INSERT INTO emp VALUES (7934 MILLERECHERGER, CLERKLERKY) 7782 to date ('23-1-1982), and 1300 to NULL10)

Dept table after inserting data:

Emp table after inserting data:

1. Multi-table association batch insert

Requirements: batch copy department dept table loc in CHICAGO, and salary greater than 1600 personnel information to emp_ copy table, emp_copy empno needs to use sequence emp_sequence

INSERT INTO emp_copySELECT 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.lockedCHICAGO 'and e.sal > = 1500) C

The result of emp_copy table after insertion:

Among them, the format of SELECT emp_sequence.nextVal,C.* ensures that multiple pieces of data of C. * can get different sequence values.

two。 Multi-table association batch update

Requirements: batch update department dept table loc in CHICAGO, and the position job is' SALESMAN' 'employees, comm on the basis of the original plus 200

Data before operation:

MERGE INTO emp e USING (SELECT e1.job, e1.enamePowere1.comm FROM emp e1, dept D1 WHERE e1.deptno = d1.deptno AND d1.localizationCHICAGO'and e1.jobblemedSalesman') t ON (e.job = t.job and e.ename = t.ename) WHEN MATCHED THEN UPDATE set e.comm= t.comm+200

Data after operation:

Where e.job = t.job and e.ename = t.ename only shows that multiple conditions can be associated, and one key point here is USING (… ) what is queried in t is a list, and merge can update the matched list in batches

3. Multi-table association batch deletion

Requirements: batch delete the personnel information of the department dept table loc in CHICAGO and the salary is less than 1500

Data before operation:

DELETE FROM (SELECT c. * FROM emp c. Dept d WHERE d.deptno = c.deptno AND d.loc = 'CHICAGO' and c.sal < 1500)

Data after operation:

Summary

The above is the related batch insert batch update and batch delete operation of multiple tables in Oracle introduced by the editor. I hope it will be helpful to you. If you have any questions, please leave me a message and the editor will reply to you in time. Thank you very much for your support to the website!

If you think this article is helpful to you, you are welcome to reprint it, please indicate the source, thank you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.