阅读提示:
本文简单给出了在oracle中使用All、First语句实现向多表插入数据的操作,供大家参考![@more@]
在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); |
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/9099175/viewspace-906807/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/9099175/viewspace-906807/