Question

执行函数时，报错ORA-01403: no data found，ORA-06512: at "RTS_T.XUNHGX", line 9，ORA-06512: at line

此函数编译时没有错误，但执行时却报错，请哪位高手给指点一下，谢谢
create or replace procedure xunhgx is
i number :=10000;
j number :=10000;
m number :=0;
n number :=10000;
begin
while i<19000 loop
select count(wwe_approver) into m from rts_t.WBOB_WB_ENDING_CS where wb_id=i;
select to_number(substr(wwe_approver,1,2)) into n from rts_t.WBOB_WB_ENDING_CS where wb_id=i;
if m !=0 and n=12
then
select to_number(wwe_approver) into j from rts_t.WBOB_WB_ENDING_CS where wb_id=i;
update rts_t.WBOB_WB_ENDING_CS set wwe_approver = (select user_code from rts_t.xzp_user where person_id=j) where wb_id=i;
else
null;
end if;
i:=i+1;
end loop;
end xunhgx;

Answer 1

Oracle的存储过程中，
当用select into时，必须保证 返回一条并且只返回一条数据。

所以，你这只要对 m进行一下判断即可

create or replace procedure xunhgx is
i number :=10000;
j number :=10000;
m number :=0;
n number :=10000;
begin
while i<19000 loop
select count(wwe_approver) into m from rts_t.WBOB_WB_ENDING_CS where wb_id=i;
if m=1 then -- add
select to_number(substr(wwe_approver,1,2)) into n from rts_t.WBOB_WB_ENDING_CS where wb_id=i;
if m !=0 and n=12
then
select to_number(wwe_approver) into j from rts_t.WBOB_WB_ENDING_CS where wb_id=i;
update rts_t.WBOB_WB_ENDING_CS set wwe_approver = (select user_code from rts_t.xzp_user where person_id=j) where wb_id=i;
else
null;
end if;
end if; -- add
i:=i+1;
end loop;
end xunhgx;