oracle 批量更新&&批量插入用法

--批量更新
declare 
cursor c1 is  SELECT orderid,orderno FROM baoxiao_apply  WHERE length(orderid)<=5;
begin
for r1 in c1 LOOP
   UPDATE orderpayment  SET baoxiao_orderno=r1.orderNO  WHERE 
      --baoxiao_orderno IS NULL AND 
      flag='2' AND paytype IN ('4','5') AND ID=r1.orderID;
--update 表 set 字段=r1.这个字段值 where 关键字=r1.关键字;
end loop;
end;
--end 
SELECT b.*,ROWID FROM orderpayment b WHERE orderno IN ('BH201406040089','BH201408130561')
SELECT * FROM wf_purchase_item b,wf_purchase_no a WHERE a.batch_no IN ('SQ201407110566 ','SQ201407110566 ','SQ201407110566 ') 
AND a.id=b.wf_purchase_id
--批量插入


INSERT INTO  orderpayment (ID,orderno,supplyid,money,flag,created,paydate,paytype,orgcode,ordertype) 
SELECT SEQ_Orderpayment.nextval,
a.batch_no,b.suppy_id,b.money,'1',SYSDATE-10,SYSDATE-10 ,b.payment_type,a.companyname,'12'
--SUM(b.money)
FROM wf_purchase_item b,wf_purchase_no a WHERE a.batch_no IN ('20201404281548') --o='20201404281549' --
 AND a.id=b.wf_purchase_id;
--end

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26495863/viewspace-1349809/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26495863/viewspace-1349809/