insert all 和insert first用法解析

考题

Evaluate this statement:
INSERT All,
WHEN order_total < 10000 THEN INTO small_orders
WHEN order_total > 10000 AND order_total < 20000 THEN
INTO medium_orders
WHEN order_total > 2000000 THEN INTO large_orders
SELECT order_id order_total, customer_id
FROM orders ;
Which statement is true regarding the evaluation of rows returned by the subquery In the Insert
statement?
A) Each row is evaluated by the first when clause and if the condition is false then row would
be evaluated by the subsequent when clauses.
B) The insert statement will return an error because the else clause is missing.
C) All rows are evaluated by all the three when clauses.
D) Each row is evaluated by the first when clause and if the condition is true, then the row
is evaluated by the subsequent when clauses.

Answer: C

解析

此题考查insert all的用法。

insert all
If you specify ALL, the default value, then the database evaluates each WHEN clause regardless of the results of the evaluation of any other WHEN clause. For each WHEN clause whose condition evaluates to true, the database executes the corresponding INTO clause list.
(如果指定ALL（默认值），则数据库将评估每个WHEN子句，而不管任何其他WHEN子句的评估结果如何。 对于条件计算结果为true的每个WHEN子句，数据库将执行相应的INTO子句列表。)

insert first
If you specify FIRST, then the database evaluates each WHEN clause in the order in which it appears in the statement. For the first WHEN clause that evaluates to true, the database executes the corresponding INTO clause and skips subsequent WHEN clauses for the given row
(如果指定FIRST，则数据库按照它在语句中出现的顺序计算每个WHEN子句。 对于计算结果为true的第一个WHEN子句，数据库执行相应的INTO子句并跳过给定行的后续WHEN子句)

实验

1、创建3个空表。

   CREATE table emp10 as select * from emp where 1=2;
    CREATE table emp20 as select * from emp where 1=2;
    CREATE table emp30 as select * from emp where 1=2;

2、 使用insert all验证

 insert all
 when deptno = 10 then into emp10
 when deptno between 10 and 30 then into emp20
 select * from emp；

select * from emp10;

 EMPNO ENAME      JOB          MGR HIREDATE                  SAL       COMM     DEPTNO

---

  7782 CLARK      MANAGER     7839 09-JUN-81                2450                    10
  7839 KING       PRESIDENT        17-NOV-81                5000                    10
  7934 MILLER     CLERK       7782 23-JAN-82                1300                    10

SCOTT@PROD> select * from emp20;

 EMPNO ENAME      JOB          MGR HIREDATE                  SAL       COMM     DEPTNO

---

  7369 SMITH      CLERK       7902 17-DEC-80                 800                    20
  7499 ALLEN      SALESMAN    7698 20-FEB-81                 800        300         30
  7521 WARD       SALESMAN    7698 22-FEB-81                1250        500         30
  7566 JONES      MANAGER     7839 02-APR-81                2975                    20
  7654 MARTIN     SALESMAN    7698 28-SEP-81                1250       1400         30
  7698 BLAKE      MANAGER     7839 01-MAY-81                2850                    30
  7782 CLARK      MANAGER     7839 09-JUN-81                2450                    10
  7788 SCOTT      ANALYST     7566 19-APR-87                3000                    20
  7839 KING       PRESIDENT        17-NOV-81                5000                    10
  7844 TURNER     SALESMAN    7698 08-SEP-81                1500          0         30
  7876 ADAMS      CLERK       7788 23-MAY-87                1100                    20
  7900 JAMES      CLERK       7698 03-DEC-81                 950                    30
  7902 FORD       ANALYST     7566 03-DEC-81                3000                    20
  7934 MILLER     CLERK       7782 23-JAN-82                1300                    10

3、使用insert first 验证

 insert first
 when deptno = 10 then into emp10
 when deptno between 10 and 30 then into emp20
 select * from emp;

select * from emp10;

 EMPNO ENAME      JOB          MGR HIREDATE                  SAL       COMM     DEPTNO

---

  7782 CLARK      MANAGER     7839 09-JUN-81                2450                    10
  7839 KING       PRESIDENT        17-NOV-81                5000                    10
  7934 MILLER     CLERK       7782 23-JAN-82                1300                    10

select * from emp20;

 EMPNO ENAME      JOB          MGR HIREDATE                  SAL       COMM     DEPTNO

---

  7369 SMITH      CLERK       7902 17-DEC-80                 800                    20
  7499 ALLEN      SALESMAN    7698 20-FEB-81                 800        300         30
  7521 WARD       SALESMAN    7698 22-FEB-81                1250        500         30
  7566 JONES      MANAGER     7839 02-APR-81                2975                    20
  7654 MARTIN     SALESMAN    7698 28-SEP-81                1250       1400         30
  7698 BLAKE      MANAGER     7839 01-MAY-81                2850                    30
  7788 SCOTT      ANALYST     7566 19-APR-87                3000                    20
  7844 TURNER     SALESMAN    7698 08-SEP-81                1500          0         30
  7876 ADAMS      CLERK       7788 23-MAY-87                1100                    20
  7900 JAMES      CLERK       7698 03-DEC-81                 950                    30
  7902 FORD       ANALYST     7566 03-DEC-81                3000                    20