多表oracle语句怎么写,Oracle多表插入语句

多表插入语句用于一次性有选择的将数据分别插入到多张表中。多表插入包含以下几种：

无条件insert all

有条件insert all

有条件insert first

以下用Oracle内置的示例用户scott和表emp来测试其用法：

sqlplus scott/tiger

select * from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------

7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20

7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30

7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30

7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20

7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30

7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30

7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10

7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20

7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10

7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30

7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20

7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30

7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20

7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10

创建两张表

create table scott.emp1 as select empno, ename, job from scott.emp where 1=2;

create table scott.emp2 as select empno, ename, sal from scott.emp where 1=2;

1、无条件insert all

insert all

into scott.emp1 values(empno, ename, job)

into scott.emp2 values(empno, ename, sal)

select empno, ename, job, sal from scott.emp where deptno = 30;

commit;

select * from scott.emp1;

EMPNO ENAME JOB

---------- ---------- ---------

7499 ALLEN SALESMAN

7521 WARD SALESMAN

7654 MARTIN SALESMAN

7698 BLAKE MANAGER

7844 TURNER SALESMAN

7900 JAMES CLERK

select * from scott.emp2;

EMPNO ENAME SAL

---------- ---------- ----------

7499 ALLEN 1600

7521 WARD 1250

7654 MARTIN 1250

7698 BLAKE 2850

7844 TURNER 1500

7900 JAMES 950

这里各个表没有追加条件，大家同时插入相同数量的记录，包含的员工都是一样的，只是选择了不同的列。

2、有条件insert all

delete scott.emp1;

delete scott.emp2;

commit;

insert all

when job in ('SALESMAN', 'CLERK') then

into scott.emp1 values(empno, ename, job)

when sal >= 1500 then

into scott.emp2 values(empno, ename, sal)

select empno, ename, job, sal from scott.emp where deptno = 30;

commit;

select * from scott.emp1;

EMPNO ENAME JOB

---------- ---------- ---------

7499 ALLEN SALESMAN

7521 WARD SALESMAN

7654 MARTIN SALESMAN

7844 TURNER SALESMAN

7900 JAMES CLERK

select * from scott.emp2;

EMPNO ENAME SAL

---------- ---------- ----------

7499 ALLEN 1600

7698 BLAKE 2850

7844 TURNER 1500

这里对要插入的表单独附加了条件，包含的员工不再一样，但是会有相同的员工存在，如7499、7844编号的员工即存在于emp1表中，也存在于emp2表中。

3、有条件insert first

delete scott.emp1;

delete scott.emp2;

commit;

insert first

when job in ('SALESMAN', 'CLERK') then

into scott.emp1 values(empno, ename, job)

when sal >= 1500 then

into scott.emp2 values(empno, ename, sal)

select empno, ename, job, sal from scott.emp where deptno = 30;

commit;

select * from scott.emp1;

EMPNO ENAME JOB

---------- ---------- ---------

7499 ALLEN SALESMAN

7521 WARD SALESMAN

7654 MARTIN SALESMAN

7844 TURNER SALESMAN

7900 JAMES CLERK

select * from scott.emp2;

EMPNO ENAME SAL

---------- ---------- ----------

7698 BLAKE 2850

这里当第一个表满足条件后，第二个表将不再插入对应的行，表emp2中不再有与表emp1相同的数据，这就是insert first与insert all的区别。