概述
今天主要介绍一下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);
其中e.job = t.job and e.ename = t.ename只是展示可以关联多个条件,这里有一个关键点为 USING ( … ) t 中查询到的是一个list,而merge可以对匹配到的list进行批量更新
四、多表关联批量删除
要求:批量删除部门dept表 loc在 CHICAGO的,且工资小于1500的人员信息
DELETE FROM( SELECT c.* FROM emp c,dept d WHERE d.deptno = c.deptno
AND d.loc = \'CHICAGO\' and c.sal < 1500);
觉得有用的朋友多帮忙转发哦！后面会分享更多devops和DBA方面的内容，感兴趣的朋友可以关注下~
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，请发送邮件至 55@qq.com 举报，一经查实，本站将立刻删除。转转请注明出处：https://www.szhjjp.com/n/32834.html