您好,登錄后才能下訂單哦!
該文章會(huì)分為三部分
1.多表關(guān)聯(lián)批量插入
2.多表關(guān)聯(lián)批量更新
3.多表關(guān)聯(lián)批量刪除
首先要明白一點(diǎn),為什么會(huì)有批量這一個(gè)概念,無(wú)非就是數(shù)據(jù)太多了,在java端把數(shù)據(jù)查出來(lái)然后在按照100-300的批次進(jìn)行更新太耗性能了,而且寫(xiě)出來(lái)的代碼會(huì)非常的臃腫,所謂好的實(shí)現(xiàn)是用最少的,最精簡(jiǎn)的代碼實(shí)現(xiàn)需求,代碼越少,留給自己犯錯(cuò)誤的機(jī)會(huì)更少。
還有一個(gè)知識(shí)點(diǎn)就是多表關(guān)聯(lián),對(duì)于查詢肯定是可以多表關(guān)聯(lián)的,其實(shí)對(duì)于除了查詢之外也是可以進(jìn)行多表關(guān)聯(lián)過(guò)濾數(shù)據(jù)的,從而達(dá)到在Oracle中查到目標(biāo)數(shù)據(jù)即可更新,從而規(guī)避規(guī)避以往需要單獨(dú)查一次數(shù)據(jù)然后在按照100-300的批次做插入,更新,刪除的操作 。
創(chuàng)建必須的表和序列語(yǔ)句:
--創(chuàng)建部門(mén)表 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) ) ; --創(chuàng)建員工表 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) ); --創(chuàng)建員工表 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) ); --自定義一個(gè)序列 create sequence emp_sequence increment by 1 --每次增加幾個(gè),我這里是每次增加1 start with 1 --從1開(kāi)始計(jì)數(shù) nomaxvalue --不設(shè)置最大值 nocycle --一直累加,不循環(huán) nocache --不建緩沖區(qū) --插入dept表數(shù)據(jù): 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表數(shù)據(jù): 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);
插入數(shù)據(jù)之后的dept表:
插入數(shù)據(jù)之后的emp表:
1.多表關(guān)聯(lián)批量插入
要求:批量復(fù)制部門(mén)dept表 loc在 CHICAGO的,且工資大于1600的人員信息到emp_copy表,emp_copy的empno需要使用序列emp_sequence
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;
插入之后的emp_copy表結(jié)果:
其中SELECT emp_sequence.nextVal,C.* 這種格式保證了C.*的多條數(shù)據(jù)都能取到不同的序列值
2.多表關(guān)聯(lián)批量更新
要求:批量更新部門(mén)dept表 loc在 CHICAGO的,且職位job為'SALESMAN'的員工,comm在原來(lái)基礎(chǔ)上加200
操作之前的數(shù)據(jù):
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
操作之后的數(shù)據(jù):
其中e.job = t.job and e.ename = t.ename只是展示可以關(guān)聯(lián)多個(gè)條件,這里有一個(gè)關(guān)鍵點(diǎn)為 USING ( … ) t 中查詢到的是一個(gè)list,而merge可以對(duì)匹配到的list進(jìn)行批量更新
3.多表關(guān)聯(lián)批量刪除
要求:批量刪除部門(mén)dept表 loc在 CHICAGO的,且工資小于1500的人員信息
操作之前的數(shù)據(jù):
DELETE FROM ( SELECT c.* FROM emp c,dept d WHERE d.deptno = c.deptno AND d.loc = 'CHICAGO' and c.sal < 1500 );
操作之后的數(shù)據(jù):
總結(jié)
以上所述是小編給大家介紹的Oracle中多表關(guān)聯(lián)批量插入批量更新與批量刪除操作,希望對(duì)大家有所幫助,如果大家有任何疑問(wèn)請(qǐng)給我留言,小編會(huì)及時(shí)回復(fù)大家的。在此也非常感謝大家對(duì)億速云網(wǎng)站的支持!
如果你覺(jué)得本文對(duì)你有幫助,歡迎轉(zhuǎn)載,煩請(qǐng)注明出處,謝謝!
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng),如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。