oracle 多表更新,游标定义语句.

    工作中遇到一个需求,要求多表更新数据,记录下来方便以后查看.

以下3个例子表及模拟的部分数据.
--物料细表,记录id好,单号,主表id,批号,生产日期

 

 

 

--物料主表,记录ID号,单号,物料号,
SQL> select f_id,f_billid,f_mateid from inv_changebody;

         F_ID F_BILLID              F_MATEID
------------- ---------------- -------------
     20000302 20000343              20000015
     20000303 20000343              20000024
     20000304 20000343              20000028
     20000305 20000345              20000015
     20000306 20000345              20000024
     20000307 20000345              20000028
     20000308 20000346              20000015
     20000309 20000346              20000024
     20000310 20000346              20000028
     20000311 20000348              20000015
     20000312 20000348              20000024
     20000313 20000348              20000028
     20000315 20000350              20000015
     20000316 20000350              20000024
     20000317 20000350              20000028
     20000318 20000355              20000015
     20000319 20000355              20000024
     20000320 20000355              20000028

18 rows selected

--物料库存表.记录物料号,批号,生产日期,保质期等信息

--一重循环;
'
declare
   rowa varchar2(18);
   i integer :=5;
   cursor C_row is select rowid from king03.INV_GoodsStoreLot order by rowid;
BEGIN 
  open C_row;
  loop 
    fetch C_row into rowa;
    exit when c_row%NOTFOUND;
    i:=i+1;
    update king03.INV_GoodsStoreLot set F_LOT=F_LOT+i where rowid=rowa;
  end loop;
  close c_row;
end;

'

--二重游标循环,更新记录值.
declare
   rowa varchar2(18);
   mateida varchar2(20);
   i integer :=0;
   cursor C_mateid is select distinct f_mateid from king03.INV_GoodsStoreLot;
   cursor C_row is select rowid from king03.INV_GoodsStoreLot where f_mateid=mateida order by rowid;
begin
 open C_mateid;
 loop 
  fetch C_mateid into mateida;
  exit when c_mateid%NOTFOUND;
    BEGIN 
      open C_row;
      loop 
        fetch C_row into rowa;
        exit when c_row%NOTFOUND;
        i:=i+1;
        update king03.INV_GoodsStoreLot set F_LOT=i where rowid=rowa;
      end loop;
      close c_row;
     I:=0;
    end;
 end loop;
 close c_mateid;
end;
 
 

--创建了一个临时表,
--a,b表关联后,f_mateid,f_log字段不唯一,增加了一个外查询,在重复记录中提取唯一值的记录,

 
 
--关联表查询,更新表的一个字段:  
--set 后面的子查询必须增加rownum<2 确保查询的记录是唯一的,因为前面用的是 '='号,只能取一条记录的.
--后面的where 条件是只更新在子查询存在的记录,其它的记录不更新,如果没有这个条件,则更新时会提示这样记录无法更新为null.
update inv_goodsstorelot a 
set f_productdate=(select b.f_productdate from temp1 b where b.f_mateid=b.f_mateid and a.f_lot=b.f_lot and rownum<2)  
    where exists (select 1 from temp1 b where b.f_mateid=a.f_mateid and a.f_lot=b.f_lot);
  
  --更新表的2个字段.set 的多个字段用括号引起来.
update inv_goodsstorelot a 
set (f_productdate,F_EXPIRATIONDATE)=(select b.f_productdate,b.f_expirationdate from temp1 b where b.f_mateid=b.f_mateid and a.f_lot=b.f_lot and rownum<2)
    where exists (select 1 from temp1 b where b.f_mateid=a.f_mateid and a.f_lot=b.f_lot);
    
    
     
 
 
    
select * from temp1 order by f_mateid,f_lot;
select * from inv_goodsstorelot order by f_mateid,f_lot;

--物料细表,记录id好,单号,主表id,批号,生产日期
SQL> select f_id,f_billid,f_sourceitemid,f_lot,f_productdate,f_expirationdate from inv_changebodylot;

         F_ID F_BILLID         F_SOURCEITEMID F_LOT                F_PRODUCTDATE F_EXPIRATIONDATE
------------- ---------------- -------------- -------------------- ------------- ----------------
     20000297 20000345               20000306 2                    2010-04-02    2010-05-01
     20000298 20000345               20000306 3                    2010-04-03    2010-05-02
     20000299 20000345               20000307 3                    2010-04-04    2010-05-03
     20000300 20000346               20000308 1                    2010-04-05    2010-05-04
     20000302 20000346               20000308 1                    2010-04-07    2010-05-05
     20000304 20000346               20000309 2                    2010-04-07    2010-05-06
     20000305 20000346               20000309 3                    2010-04-08    2010-05-07
     20000307 20000346               20000310 3                    2010-04-09    2010-05-08
     20000308 20000346               20000310 4                    2010-04-10    2010-05-09
     20000309 20000348               20000311 1                    2010-04-12    2010-05-10
     20000310 20000348               20000312 2                    2010-04-12    2010-05-11
     20000311 20000348               20000313 4                    2010-04-13    2010-05-12
     20000312 20000350               20000315 1                    2010-04-14    2010-05-13
     20000313 20000350               20000316 2                    2010-04-15    2010-05-14
     20000314 20000350               20000317 3                    2010-04-16    2010-05-15
     20000291 20000343               20000302 1                    2010-04-17    2010-05-16
     20000292 20000343               20000302 2                    2010-04-18    2010-05-17
     20000293 20000343               20000303 2                    2010-04-19    2010-05-18
     20000294 20000343               20000304 3                    2010-04-20    2010-05-19
     20000296 20000345               20000305 1                    2010-04-21    2010-05-20

         F_ID F_BILLID         F_SOURCEITEMID F_LOT                F_PRODUCTDATE F_EXPIRATIONDATE
------------- ---------------- -------------- -------------------- ------------- ----------------
     20000315 20000355               20000318 2                    2010-04-22    2010-05-21
     20000317 20000355               20000319 4                    2010-04-23    2010-05-22
     20000318 20000355               20000320 3                    2010-04-24    2010-05-23

23 rows selected

--物料主表,记录ID号,单号,物料号,
SQL> select f_id,f_billid,f_mateid from inv_changebody;

         F_ID F_BILLID              F_MATEID
------------- ---------------- -------------
     20000302 20000343              20000015
     20000303 20000343              20000024
     20000304 20000343              20000028
     20000305 20000345              20000015
     20000306 20000345              20000024
     20000307 20000345              20000028
     20000308 20000346              20000015
     20000309 20000346              20000024
     20000310 20000346              20000028
     20000311 20000348              20000015
     20000312 20000348              20000024
     20000313 20000348              20000028
     20000315 20000350              20000015
     20000316 20000350              20000024
     20000317 20000350              20000028
     20000318 20000355              20000015
     20000319 20000355              20000024
     20000320 20000355              20000028

18 rows selected

--物料库存表.记录物料号,批号,生产日期,保质期等信息

--创建了一个临时表,
--a,b表关联后,f_mateid,f_log字段不唯一,增加了一个外查询,在重复记录中提取唯一值的记录,
create table temp1 as
 select bb.f_mateid,bb.f_Lot,bb.f_ProductDate,bb.f_ExpirationDate from
   (select * from (
                    select s.*,dense_rank() over(partition by s.f_mateid,s.f_lot order by s.rowid) rowa  from
                     ( select a.rowid,a.f_SourceItemID,a.f_Lot,a.f_BillID,b.f_MateID,a.f_ProductDate,a.f_ExpirationDate 
                      from king03.INV_ChangeBodyLot a,king03.INV_ChangeBody b where a.f_sourceitemid=b.f_id(+) and a.f_billid=b.f_billid ) s
                  )
    where rowa<2
  ) bb;

 

 

由于是模拟的数据,记录字段不唯一,需要重新生成记录.

--一重循环;
'
declare
   rowa varchar2(18);
   i integer :=5;
   cursor C_row is select rowid from king03.INV_GoodsStoreLot order by rowid;
BEGIN 
  open C_row;
  loop 
    fetch C_row into rowa;
    exit when c_row%NOTFOUND;
    i:=i+1;
    update king03.INV_GoodsStoreLot set F_LOT=F_LOT+i where rowid=rowa;
  end loop;
  close c_row;
end;

'

--二重游标循环,更新记录值.
declare
   rowa varchar2(18);
   mateida varchar2(20);
   i integer :=0;
   cursor C_mateid is select distinct f_mateid from king03.INV_GoodsStoreLot;
   cursor C_row is select rowid from king03.INV_GoodsStoreLot where f_mateid=mateida order by rowid;
begin
 open C_mateid;
 loop 
  fetch C_mateid into mateida;
  exit when c_mateid%NOTFOUND;
    BEGIN 
      open C_row;
      loop 
        fetch C_row into rowa;
        exit when c_row%NOTFOUND;
        i:=i+1;
        update king03.INV_GoodsStoreLot set F_LOT=i where rowid=rowa;
      end loop;
      close c_row;
     I:=0;
    end;
 end loop;
 close c_mateid;
end;
 

 

 

--关联表查询,更新表的一个字段:  
--set 后面的子查询必须增加rownum<2 确保查询的记录是唯一的,因为前面用的是 '='号,只能取一条记录的.
--后面的where 条件是只更新在子查询存在的记录,其它的记录不更新,如果没有这个条件,则更新时会提示这样记录无法更新为null.
update inv_goodsstorelot a 
set f_productdate=(select b.f_productdate from temp1 b where b.f_mateid=b.f_mateid and a.f_lot=b.f_lot and rownum<2)  
    where exists (select 1 from temp1 b where b.f_mateid=a.f_mateid and a.f_lot=b.f_lot);
  
  --更新表的2个字段.set 的多个字段用括号引起来.
update inv_goodsstorelot a 
set (f_productdate,F_EXPIRATIONDATE)=(select b.f_productdate,b.f_expirationdate from temp1 b where b.f_mateid=b.f_mateid and a.f_lot=b.f_lot and rownum<2)
    where exists (select 1 from temp1 b where b.f_mateid=a.f_mateid and a.f_lot=b.f_lot);

本文转自 gjm008 51CTO博客，原文链接：http://blog.51cto.com/gaoshan/293324，如需转载请自行联系原作者