create or replace procedure test(position_9_10 in char)
as
type my_cursor is ref cursor;
my_all_record my_cursor;
my_each_record my_cursor;
every_record varchar2(13);
every_bh varchar2(10);
prior_record char(3);
next_record char(3);
last3 char(3);
sqlstr varchar2(100);
begin
sqlstr := 'select bh from aa where substrb(bh,9,2)!='''||position_9_10||'''';
open my_all_record for sqlstr;
loop
fetch my_all_record into every_record;
exit when my_all_record%notfound;
every_bh := substrb(every_record,1,8)||position_9_10;
last3 := '';
sqlstr := 'select substrb(bh,11,3) from aa where substrb(bh,1,10)='''||every_bh||''' order by bh';
open my_each_record for sqlstr;
fetch my_each_record into prior_record;
if my_each_record%notfound then
last3:='001';
else
if to_number(prior_record)>=2 then
last3:='001';
else
while my_each_record%rowcount<=999 loop
last3:=to_char(my_each_record%rowcount);
fetch my_each_record into next_record;
if my_each_record%rowcount=to_number(last3) then
if my_each_record%rowcount=999 then
last3:='000';
else
last3:=to_char(my_each_record%rowcount+1);
end if;
exit;
else
if to_number(next_record)!=to_number(prior_record)+1 then
last3:=to_char(to_number(prior_record)+1);
exit;
else
prior_record:=next_record;
end if;
end if;
end loop;
end if;
end if;
if to_number(last3)<10 then
last3:=concat('00',to_number(last3));
elsif to_number(last3)<100 then
last3:=concat('0',to_number(last3));
end if;
close my_each_record;
update aa set bh=every_bh||last3 where bh=every_record;
commit;
end loop;
close my_all_record;
end;
/