A表，B表如下：
A:
每个批量(lot)有20或10个顺位(id),每个批量的name一样，每个批量的id不一定是从F0001开始，但在一
个批量中都是依次增加。
id name LOT TID
F0003 a 1101
F0004 a 1101
F0005 a 1101
.....
.....
F0001 b 1102
F0002 b 1102
.....
.....
B:
name num
a 7
b 5
c 7
...
要求：
每个根据B表更新A表的TID字段。
例如：批量1101有二十个顺位的话，则按照id的大小顺序，前七个顺位
的TID为1，第二个7个顺位的TID更新为2，剩下的6个顺位更新为3。即根据20/7的值更新相应顺位的TID
如下表：
F0003 a 1101 1
F0004 a 1101 1
F0005 a 1101 1
F0006 a 1101 1
F0007 a 1101 1
F0008 a 1101 1
F0009 a 1101 1
F0010 a 1101 2
... 2
... 2
请问这样的procedure应该怎样写？？
------解决方案--------------------
Create or replace procedure update_A
IS
v_num int;
v_lot varchar(20);
cursor c_b is select distinct a.lot,b.num from a,b where a.name = b.name;
begin
open c_b;
loop
fetch c_b into v_lot,v_num ;
exit when c_b%NOTFOUND;
update a outa
set tid = ( select trunc(count(*)/v_num)+1 from a where id <outa.id and lot = v_lot )
where lot = v_lot ;
end loop;
close c_b;
end ;
------解决方案--------------------
楼主参照一下如下代码：
drop table t1;
create table t1(
fid varchar2(10),
Fname varchar2(3),
lot varchar2(10),
tid varchar(3)
);
insert into t1
select 'F0001 ', 'a ', '1001 ', ' ' from dual
union all
select 'F0002 ', 'a ', '1001 ', ' ' from dual
union all
select 'F0003 ', 'a ', '1001 ', ' ' from dual
union all
select 'F0004 ', 'a ', '1001 ', ' ' from dual
union all
select 'F0005 ', 'a ', '1001 ', ' ' from dual
union all
select 'F0006 ', 'a ', '1001 ', ' ' from dual
union all
select 'F0001 ', 'b ', '1002 ', ' ' from dual
union all
select 'F0002 ', 'b ', '1002 ', ' ' from dual;
commit;
select rk, trunc((rk-1)/7) +1 tid, fid,fname,lot
from
(select rank() over(partition by Fname order by Fid) rk,FID,FName,lot from t1) t;