--批量更新
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
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博客 ” ，链接：https://blog.itpub.net/26495863/viewspace-1349809/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
oracle 外部表用法
请登录后发表评论
登录
全部评论
