oracle ORA-01403: 未找到 Select into 的数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21186411/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
ORA-01403: no data found for Select into
提问by being_uncertain
I am getting ORA-01403: no data foundexception for the following query. What are the possibilities of this error?
我遇到ORA-01403: no data found以下查询的异常。这个错误的可能性是什么？
SELECT trim(name)
INTO fullname
FROM ( SELECT n.name
FROM directory dir, store n
WHERE dir.name = n.name
AND dir.status NOT IN ('F', 'L', 'M')
ORDER BY n.imp, dir.date)
WHERE rownum <= 1;
How can I handle this error?
我该如何处理这个错误？
回答by Sandeep
Although you have put where condition, but better way would be to handle case of record not found or 'No Data Found' error. I would write above code with wrapping the SELECTstatement with it's own BEGIN/EXCEPTION/ENDblock.
尽管您已经放置了 where 条件，但更好的方法是处理未找到记录或“未找到数据”错误的情况。我会写上面的代码，SELECT用它自己的BEGIN/EXCEPTION/END块包装语句。
Code could be something like this:
代码可能是这样的：
BEGIN
SELECT trim(name)
INTO fullName
FROM (
SELECT n.name
FROM directory dir, store n
WHERE dir.name = n.name
AND dir.STATUS NOT IN ('F','L','M')
ORDER BY n.imp, dir.date
)
WHERE rownum <= 1;
EXCEPTION
WHEN NO_DATA_FOUND THEN
fullName = NULL;
END;
回答by Andreas Dietrich
If the standard exception handlingdescribed by Sandeep seems to much overhead(like in my case) and you're fine with a NULLor some individual <not found>value), you might just transform it like this:
如果Sandeep 描述的标准异常处理似乎有很多开销（就像我的情况一样），并且您可以使用一个NULL或某些单独的<not found>值），您可以像这样转换它：
select col into v_foo from bar where 1=0 -- would provoke ORA-01403
=> no ORA-01403raised:
=> 没有提出ORA-01403：
-- if NULL would be fine:
select (select col from bar where 1=0) into v_foo from dual
-- if individual "NOT_FOUND" value should be set to avoid standard exception handling:
-- (it depends on your col type, so it could e.g. be 'NOT_FOUND' or -1
-- or to_date( 'yyyy-mm-dd', '2100-01-01') )
select nvl( (select col from bar where 1=0), 'NOT_FOUND' ) into v_foo from dual
回答by A Nice Guy
Probably because your Query
可能是因为您的查询
SELECT n.name
FROM directory dir,
store n
WHERE dir.name = n.name
AND dir.STATUS NOT IN ('F','L','M')
ORDER BY n.imp,
dir.date
is not returning any rows
没有返回任何行
回答by GriffeyDog
If dir.statushas any nulls in your table, then not inis probably not doing what you think it is. You may get zero rows even though you think you should get one or more rows. You can switch to and not (dir.status in ('F,'L','M'))instead of dir.status not in ('F','L','M').
如果dir.status您的表中有任何空值，则not in可能没有按照您的想法行事。即使您认为应该获得一行或多行，也可能会得到零行。您可以切换到and not (dir.status in ('F,'L','M'))而不是dir.status not in ('F','L','M')。
See thisfor reference.
请参阅此以供参考。
