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的区别。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/28974745/viewspace-2141812/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/28974745/viewspace-2141812/