Question

调用存储过程出现ORA-01403: 未找到数据。高分求高手，急！！！！！

存储过程代码为：
create or replace procedure PROC_CNB_NDJXMT (message out varchar2,
recordIds varchar2,
empId number) as
cnb_nvarA number default 0;
cnb_nvarB number default 0;
cnb_nvarC number default 0;
cnb_nvarD number default 0;
cnb_nvarE number default 0;
cnb_nvarF number default 0;
cnb_vvarA varchar2(2048);
cnb_vvarB varchar2(128);
cnb_vvarC varchar2(128);
begin
cnb_vvarA := recordIds;
cnb_nvarA := 3;
--cnb_nvarB???????id
select instr(cnb_vvarA, ',', 1) into cnb_nvarB from dual;
--cnb_vvarA????
if cnb_nvarB > 0 then
select substr(cnb_vvarA, 1, cnb_nvarB - 1) into cnb_vvarA from dual;
end if;

--cnb_vvarB????ID??reportcode ,cnb_nvarF??ID
select a.c_reportcode, c.c_unitid
into cnb_vvarB, cnb_nvarF
from tb_org_unitrelation a, TB_CNB_NDJXINFO b,tb_inf_employee c
where b.c_empoid=c.c_oid
and a.c_orgunitid = c.c_unitid
and b.c_oid = to_number(cnb_vvarA);

--cnb_nvarC??'-'???????
select length(regexp_replace(cnb_vvarB, '[^-]', ''))
into cnb_nvarC
from dual;

--cnb_nvarD??>=0?????????????
cnb_nvarD := cnb_nvarC - cnb_nvarA;

if cnb_nvarD > 0 then

--cnb_nvarE???cnb_nvarA?'-'???
select instr(cnb_vvarB, '-', 1, cnb_nvarA) into cnb_nvarE from dual;
--cnb_vvarC???cnb_nvarE??????
select substr(cnb_vvarB, 1, cnb_nvarE) into cnb_vvarC from dual;

update TB_CNB_NDJXINFO a
set a.c_shzt = '1'
where exists (select 1 from tb_inf_employee c where c.c_unitid in
(select b.c_orgunitid
from tb_org_unitrelation b
where b.c_reportcode like cnb_vvarC || '%')
and a.c_empoid=c.c_oid ) ;

elsif cnb_nvarD = 0 then

update TB_CNB_NDJXINFO a
set a.c_shzt = '1'
where exists (select 1 from tb_inf_employee b where a.c_empoid=b.c_oid
and b.c_Unitid = cnb_nvarF);

end if;

message := '';

--??????
EXCEPTION
WHEN OTHERS THEN
message := SUBSTR(SQLERRM, 1, 200);

end;
数据库表如图：

Answer 1

注意select into 语句，如果不是从dual中取值，那么最好加上异常捕获。
如：
BEGIN
select a.c_reportcode, c.c_unitid
into cnb_vvarB, cnb_nvarF
from tb_org_unitrelation a, TB_CNB_NDJXINFO b,tb_inf_employee c
where b.c_empoid=c.c_oid
and a.c_orgunitid = c.c_unitid
and b.c_oid = to_number(cnb_vvarA);
EXCEPTION
WHEN NO_DATA_FOUND THEN
cnb_vvarB := 0;
cnb_nvarF := 0;
END;

Answer 2

请参照以下步骤：
1、根据PLSQL提示的错误行数定位到具体报错的那一行代码
2、查看该行代码是否有select .. into ...
3、修改程序兼容select 不到数据的情况，可以加max(),或者nvl(ksks,'默认值')之类的，这样就不会
报no data found exception了。

Answer 3

提示哪行