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

最新推荐文章于 2023-10-19 09:29:39 发布

sas???

最新推荐文章于 2023-10-19 09:29:39 发布

阅读量115

点赞数

文章标签：数据库

原文链接：http://blog.51cto.com/gaoshan/293324

版权

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

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

SQL> select f_id,f_billid,f_sourceitemid,f_lot,f_productdate,f_expirationdate from inv_changebodylot;<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

F_ID F_BILLID F_SOURCEITEMID F_LOT F_PRODUCTDATE F_EXPIRATIONDATE

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

20000297 20000345 20000306 2 <?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-com:office:smarttags" />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

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

--一重循环;
'
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字段不唯一,增加了一个外查询,在重复记录中提取唯一值的记录,

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;

--关联表查询,更新表的一个字段:
--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

23 rows selected

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

18 rows selected

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

SQL> select f_mateid,f_lot,f_productdate,f_expirationdate from inv_goodsstorelot;

F_MATEID F_LOT F_PRODUCTDATE F_EXPIRATIONDATE

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

20000015 1 2010-04-05 2010-05-04

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

20000015 2 2010-04-18 2010-05-17

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

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

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

20000024 2 2010-04-18 2010-05-17

20000024 3 2010-04-03 2010-05-02

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

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

20000028 3 2010-04-03 2010-05-02

20000028 4 2010-04-23 2010-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;

--关联表查询,更新表的一个字段:
--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);

转载于:https://blog.51cto.com/gaoshan/293324

sas???

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
oracle 多表更新,游标定义语句.

工作中遇到一个需求,要求多表更新数据,记录下来方便以后查看.以下3个例子表及模拟的部分数据.--物料细表,记录id好,单号,主表id,批号,生产日期SQL> select f_id,f_billid,f_sourceitemid,f_lot,f_productdate,f_expirationdate from inv_changebodylot;...
复制链接

扫一扫