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的區別。