insert all 和insert first多表插入用法

一、用法

  all: 不考虑先后关系，只要满足条件，就全部插入；

 first: 考虑先后关系，如果有数据满足第一个 when 条件又满足第二个 when 条件，则执行第一个 then 插入语句，第二个 then 就不插入第一个 then 已经插入过的数据了。

其区别也可描述为， all 只要满足条件，可能会作重复插入； first 首先要满足条件，然后筛选，不做重复插入。

二、建表

CREATE TABLE small_orders

(order_id NUMBER(12) NOT NULL,

customer_id NUMBER(6) NOT NULL,

order_total NUMBER(8,2),

sales_rep_id NUMBER(6)

)

CREATE TABLE medium_orders AS SELECT * FROM small_orders;

CREATE TABLE large_orders AS SELECT * FROM small_orders; CREATE TABLE special_orders (order_id NUMBER(12) NOT NULL, customer_id NUMBER(6) NOT NULL, order_total NUMBER(8,2), sales_rep_id NUMBER(6), credit_limit NUMBER(9,2), cust_email VARCHAR2(30) );

三、用法案例

(1)insert all-不带条件

  insert all

   into small_orders(order_id,customer_id,order_tall)

       values (oid,cid,ottl)

    into medium_orders(order_id,customer_id,sales_rep_id)

      values (oid,cid,sid)

SELECT o.order_id oid, o.customer_id cid, o.order_total ottl, o.sales_rep_id sid, c.credit_limit cl, c.cust_email cem FROM orders o, customers c WHERE o.customer_id = c.customer_id;

(2)insert all 带条件

INSERT ALL WHEN order_total < 1000000 THEN INTO small_orders WHEN order_total > 1000000 AND order_total < 2000000 THEN INTO medium_orders WHEN order_total > 2000000 THEN INTO large_orders SELECT order_id, order_total, sales_rep_id, customer_id FROM orders;

INSERT ALL

WHEN ottl < 100000 THEN

INTO small_orders VALUES(oid, ottl, sid, cid) WHEN ottl > 100000 and ottl < 200000 THEN INTO medium_orders VALUES(oid, ottl, sid, cid) WHEN ottl > 200000 THEN into large_orders VALUES(oid, ottl, sid, cid) WHEN ottl > 290000 THEN INTO special_orders SELECT o.order_id oid, o.customer_id cid, o.order_total ottl, o.sales_rep_id sid, c.credit_limit cl, c.cust_email cem FROM orders o, customers c WHERE o.customer_id = c.customer_id; （3）insert first

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

转载于:http://blog.itpub.net/22526482/viewspace-2122757/