µ¥±í²åÈëÒÔinsert into¿ªÍ·,²»ÄÜÓÐthen intoÓï¾ä.
¶à±í²åÈëÒÔinsert first/all ¿ªÍ·,¿ÉÒÔÓÐthen intoÓï¾ä
ÔÚ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);
ºÜ¶àÅóÓÑÔÚJava¿ª·¢ÖУ¬Ê¹ÓÃOracleÊý¾Ý¿âµÄʱºò£¬¾³£»áÅöµ½ÓÐORA-01000: maximum open cursors exceeded.µÄ´íÎó¡£
ʵ¼ÊÉÏ£¬Õâ¸ö´íÎóµÄÔÒò£¬Ö÷Òª»¹ÊÇ´úÂëÎÊÌâÒýÆðµÄ¡£
ora-01000: maximum open cursors exceeded.
±íʾÒѾ´ïµ½Ò»¸ö½ø³Ì´ò¿ªµÄ×î´óÓαêÊý¡£
ÕâÑùµÄ´íÎóºÜÈÝÒ׳öÏÖÔÚJava´úÂëÖеÄÖ÷ÒªÔÒòÊÇ£ºJava´úÂëÔÚÖ´ ......
´´½¨ÁËÒ»oracleµÄ¶¨Ê±ÈÎÎñ£¬¶¨Ê±µÄ½«Ò»¸ö±íÖеļǼ²åÈëµ½ÁíÒ»¸ö±íÖУ¬²éѯµÄʱºò±íÖо¹È»Ã»Êý¾Ý£¬ÔÚpl/sqlÖв鿴½¨µÄÈÎÎñ·¢ÏÖfailuresµÄֵΪ3£¬ÄѵÀ½¨µÄ´æ´¢¹ý³ÌÓÐ´í£¿ÔËÐÐһϴ洢¹ý³ÌÊÔÊÔ°É£¬²éÊý¾Ý¿âÊ飬ִÐд洢¹ý³ÌÃüÁîÊÇ£ºcall procedure ¹ý³ÌÃû£»
Ö´ÐÐÒ»ÏÂÃüÁ²»¶Ô£¿ÔÙ´ÓÍøÉϲé°É£¬Ò»²é£¬oracleÖÐÖ´Ðд洢¹ý³ÌµÄ ......