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

--创建了一个临时表,

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

createtable 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

(selecta.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

wherea.f_sourceitemid=b.f_id(+) and a.f_billid=b.f_billid ) s

) where rowa<2) bb;

--关联表查询,更新表的一个字段:

--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

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

SQL> select f_mateid,f_lot,f_productdate,f_expirationdatefrom inv_goodsstorelot;

F_MATEID F_LOTF_PRODUCTDATE F_EXPIRATIONDATE

------------- -------------------- ------------- ----------------

20000015 12010-04-052010-05-04

20000015 52010-04-10 09 2010-04-10 09:32

20000015 22010-04-182010-05-17

20000015 32010-04-10 09 2010-04-10 09:32

20000015 42010-04-10 09 2010-04-10 09:32

20000024 12010-04-10 09 2010-04-10 09:32

20000024 22010-04-182010-05-17

20000024 32010-04-032010-05-02

20000028 12010-04-10 09 2010-04-10 09:32

20000028 22010-04-10 09 2010-04-10 09:32

20000028 32010-04-032010-05-02

20000028 42010-04-232010-05-22

12 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);