TO ORARichard(没钱的日子......) 最后的三位也不能随便改,要找到改完后的最大编号,再在最大编号上加1生成就是.
create sequence s start with 1 maxvalue 999;update 表 set 编号=substr(编号,1,8)||'03'||substr(to_char(1000+s.nextval),2,3) where substr(编号,9,2)='01';drop sequence s;
表数据如下,现在把第9,10位都改成03,原来是03的不改。原来不是03的遵循空号优先,小号优先的原则进行选号。。祝楼主好运。。俺也得sleep了。。SQL> select * from aa;BH ------------- 1234567803001 1234567803003 1234567803004 1234567803005 1234567801001 1234567801009已选择6行。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; /过程已创建SQL> exec test('03');PL/SQL 过程已成功完成。SQL> select * from aa;BH ------------- 1234567803001 1234567803003 1234567803004 1234567803005 1234567803002 1234567803006已选择6行。此时原来的 1234567801001 1234567801009 已分别被改成了 1234567803002 1234567803006 把那个空号002也用了,然后如果都排满了,就用最大的005加1等于006
如
update table set field=substrb(field,1,8)||'03000';
或
update table set field=substrb(field,1,8)||'03999';
不如将9,10和最后三位一起改掉,这样一句SQL就可以搞定了(最后三位用序列)
where substr(编号,9,2)='01';drop sequence s;
-------------
1234567803001
1234567803003
1234567803004
1234567803005
1234567801001
1234567801009已选择6行。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;
/过程已创建SQL> exec test('03');PL/SQL 过程已成功完成。SQL> select * from aa;BH
-------------
1234567803001
1234567803003
1234567803004
1234567803005
1234567803002
1234567803006已选择6行。此时原来的
1234567801001
1234567801009
已分别被改成了
1234567803002
1234567803006
把那个空号002也用了,然后如果都排满了,就用最大的005加1等于006