在oracle操作过程中经常会碰到同时向多个不同的表插入数据，此时用该语句就非常合适。 all表示非短路运算，即满足了第一个条件也得向下执行查看是否满足其它条件，，而first是短路运算找到合适条件就不向下进行。 insert all
when prod_category=’b’ then
into book_sales(prod_id,cust_id,qty_sold,amt_sold)
values(product_id,customer_id,sale_qty,sale_price)
when prod_category=’v’ then
into video_sales(prod_id,cust_id,qty_sold,amt_sold)
values(product_id,customer_id,sale_qty,sale_price)
when prod_category=’a’ then
into audio_sales(prod_id,cust_id,qty_sold,amt_sold)
values(product_id,customer_id,sale_qty,sale_price)
select prod_category ,product_id ,customer_id ,sale_qty
,sale_price
from sales_detail; merging rows into a table
merge into oe.product_information pi
using (select product_id, list_price, min_price
from new_prices) np
on (pi.product_id = np.product_id)
when matched then update set pi.list_price =np.list_price
,pi.min_price = np.min_price
when not matched then insert (pi.product_id,pi.category_id
,pi.list_price,pi.min_price)
values (np.product_id, 33,np.list_price, np.min_price);