无条件INSERT ALL
INSERT ALL
INTO sal_history VALUES(EMPID,HIREDATE,SAL)
INTO mgr_history VALUES(EMPID,MGR,SAL)
SELECT employee_id EMPID,hire_date HIREDATE,salary SAL,manager_id MGR
FROM employees
WHERE employee_id>200;
因为没有附加限制于检索出的记录,所以所有检索返回数据讲根据其列名插入相应的表中.
有条件的INSERT ALL
INSERT ALL
WHEN SAL>10000 THEN
INTO sal_history VALUES(EMPID,HIREDATE,SAL)
WHEN MGR>200 THEN
INTO mgr_history VALUES(EMPID,MGR,SAL)
SELECT employee_id EMPID,hire_date HIREDATE,salary SAL,manager_id MGR
FROM employees
WHERE employee_id>200;
这里就将比较了,返回记录将比较其SAL,如果大于10000则插入SAL_HISTORY,同理,MGR大于200的记录将插入MGR_HISTORY表.其余都将丢弃.
有条件的FIRST INSERT
FIRST与ALL的区别在于当遇到第一个求值为TRUE的子句之后停止对WHEN子句求值,而ALL不论是否求值为TRUE.
INSERT FIRST
WHEN SAL>25000 THEN
INTO special_sal VALUES(DEPTID,SAL)
WHEN HIREDATE like ('%00%') THEN
INTO hiredate_history_00 VALUES(DEPTID,HIREDATE)
WHEN HIREDATE like ('%99%') THEN
INTO hiredate_history_99 VALUES(DEPTID,HIREDATE)
ELSE
INTO hiredate_history VALUES(DEPTID,HIREDATE)
SELECT department_id DEPTID,SUM(salary) SAL,
MAX(hire_date) HIREDATE
FROM employees
GROUP BY department_id;
上例中如果第一个WHEN子句求值为TRUE则其后的WHEN子句将不会被执行,反之将直到遇到第一个满足条件的子句执行为止.