高手帮忙看看我的存储过程

来源：百度知道 编辑：UC知道 时间：2024/06/17 21:54:39
create or replace procedure procedurefm
as
v_stz temp_2009stz2.id1%type;
v_stzid varchar2(13);
v_temp varchar2(3);

cursor c_ysstz(stzpreid varchar2)
is
select id1 from temp_2009stz2 where substr(id1,1,13)=stzpreid for update;

cursor c_preid
is
select distinct(substr(id1,1,13)) from temp_2009stz2;

begin
open c_preid;
loop
fetch c_preid into v_stzid;--提取游标
v_temp:='000';
if c_preid%FOUND THEN
open c_ysstz(v_stzid);
loop
fetch c_ysstz into v_stz;
if c_ysstz%FOUND THEN
v_temp:=v_temp+1;
update temp_2009stz2 set id1=v_stz+1
where current of c_ysstz;
END IF;
end loop;
end if;
end loop;
close c_preid;
close c_ysstz;
end;
大致意思就是：把一个表中的ID按照前13位分组，然后每组中的分别进行编号，比如说
1231231231234000 ---改成新1231231231234001
1231231231234000 1231231231234002
1231231231234000 1231231231234003

备注--**的地方修改了两处。

create or replace procedure procedurefm as
v_stz temp_2009stz2.id1%type;
v_stzid varchar2(13);
v_temp varchar2(3);

cursor c_ysstz(stzpreid varchar2) is
select id1
from temp_2009stz2
where substr(id1, 1, 13) = stzpreid
for update;

cursor c_preid is
select distinct (substr(id1, 1, 13)) from temp_2009stz2;

begin
open c_preid;
loop
fetch c_preid
into v_stzid; --提取游标
v_temp := '000';
if c_preid%FOUND THEN
open c_ysstz(v_stzid);
loop
fetch c_ysstz
into v_stz;
if c_ysstz%FOUND THEN
v_temp := v_temp + 1;
update temp_2009stz2
set id1 = v_stz + to_char(v_temp,'000') --**应该加上序号
where current of c_ysstz;
END IF;
end loop;
close c_preid; --**移到此处，关闭游标c_preid。
end if;
end loop;
close c_ysstz;
end;

其实可以考虑用sql语句来完成

select t.id|