Oracle11高性能开发--（3）批量处理语句总结

批量新增数据

INSERT INTO db1_name (field1,field2) SELECT field1,field2 FROM db2_name

批量更新数据

Update （select * from a,b where a.id=b.id）set a.name = b.name

如果是对a表字段更新，要求select中对b表的过滤字段必须有唯一约束

批量更新或插入数据

MERGE INTO products p   
 USING newproducts np   
    ON (p.product_id = np.product_id)   
    WHEN MATCHED THEN  
    UPDATE  
     SET p.product_name = np.product_name,   
    WHERE p.category = 'DVD'  
    WHEN NOT MATCHED THEN  
     INSERT VALUES (np.product_id, np.product_name) 
    WHERE np.category != 'BOOKS'

BULK COLLECT和forall

open detailRuleCursor;
  loop
    FETCH detailRuleCursor BULK COLLECT
      INTO detailRuleList limit 500;
   forall i in detailRuleList.FIRST .. detailRuleList.LAST
      update C_DETAIL_RULE r
         set r.actdatekey = detailRuleList(i).actdatekey, r.check_result = detailRuleList(i).result  where rowid = detailRuleList(i).rowid;
    commit;
    exit when detailRuleCursor%notfound;
  end loop;
  close detailRuleCursor;