因为你的游标只返回一行。
游标内的更新是不能动态影像游标的结果的。实现的参考代码如下:
declare
cursor op_notes_cur is
select ROWID FROM OP_SCHEME_MASTER_CHILD WHERE SCHEME_CHILD_ID =0;
-- variables definition
l_rowid varchar2(100);
V_ID NUMBER(12) :=0;
V_SCHEME_ID NUMBER(18) :=0;
BEGIN
select max(sheme_child_id) into v_id from scheme_master_child;
open op_notes_cur;
loop
fetch op_notes_cur into l_rowid;
exit when op_notes_cur%NotFound;
update op_scheme_master_child
set scheme_child_id = V_SCHEME_ID + 1
where rowid = l_rowid;
commit;
end loop;
END;
游标内的更新是不能动态影像游标的结果的。实现的参考代码如下:
declare
cursor op_notes_cur is
select ROWID FROM OP_SCHEME_MASTER_CHILD WHERE SCHEME_CHILD_ID =0;
-- variables definition
l_rowid varchar2(100);
V_ID NUMBER(12) :=0;
V_SCHEME_ID NUMBER(18) :=0;
BEGIN
select max(sheme_child_id) into v_id from scheme_master_child;
open op_notes_cur;
loop
fetch op_notes_cur into l_rowid;
exit when op_notes_cur%NotFound;
update op_scheme_master_child
set scheme_child_id = V_SCHEME_ID + 1
where rowid = l_rowid;
commit;
end loop;
END;
mynum number(18):=0;
myrowid varchar(40);
cursor mycur is select rowid from OP_SCHEME_MASTER_CHILD ;
begin
open mycur;
fetch mycur into myrowid;
while mycur%found loop
update OP_SCHEME_MASTER_CHILD set SCHEME_ID =mynum where rowid=myrowid;
--chartorowid(myrowid)
mynum:=mynum+1;
fetch mycur into myrowid;
end loop;
close mycur;
commit;
end;
已经验证通过
update OP_SCHEME_MASTER_CHILD set SCHEME_ID = rownum
就可以了。