INSERT ALL/FIRST 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 and ottl < 300000 THEN INTO large_orders VALUES(oid, ottl, sid, cid) ELSE 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;
有两种方法!insert all when [condition] then into table_name1 values(); when [condition] then into table_name2 values(); else into others; ------------ insert first when [condition] then into table_name1 values(); when [condition] then into table_name2 values(); else into others;/*两种区别在于当用insert all 的时候当数据对两个以上的[condition] 都满足的时候数据会写入所有满足条件的表里。 当用 insert first 的时候 出现这种情况的时候数据直插入第一次满足条件的表。*/
操作几个表,写几个sql 语句
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 and ottl < 300000 THEN
INTO large_orders VALUES(oid, ottl, sid, cid)
ELSE
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;
when [condition] then
into table_name1 values();
when [condition] then
into table_name2 values();
else
into others;
------------
insert first
when [condition] then
into table_name1 values();
when [condition] then
into table_name2 values();
else
into others;/*两种区别在于当用insert all 的时候当数据对两个以上的[condition]
都满足的时候数据会写入所有满足条件的表里。 当用 insert first 的时候
出现这种情况的时候数据直插入第一次满足条件的表。*/