SQL 为什么 no_data_found ORA-01403 在 Oracle 中是一个异常？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3940522/
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
Why is no_data_found ORA-01403 an exception in Oracle?
提问by Stephan Schielke
If the SELECT INTO statement doesn't return at least one row, ORA-01403 is thrown.
如果 SELECT INTO 语句没有返回至少一行，则抛出 ORA-01403。
For every other DBMS I know this is normal on a SELECT. Only Oracle treats a SELECT INTO like this.
对于每个其他 DBMS，我知道这在 SELECT 上是正常的。只有 Oracle 像这样对待 SELECT INTO。
CREATE OR REPLACE PROCEDURE no_data_proc IS
dummy dual.dummy%TYPE;
BEGIN
BEGIN
SELECT dummy
INTO dummy
FROM dual
WHERE dummy = 'Y';
EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line('Why is this needed?');
END;
END no_data_proc;
Why?
为什么？
In my opinion you don't need this exception really. It is too much overhead. Sometimes it is handy but you have to write a whole BEGIN, EXCEPTION, WHEN, END Block.
在我看来，你真的不需要这个例外。这是太多的开销。有时它很方便，但你必须写一个完整的 BEGIN、EXCEPTION、WHEN、END 块。
Are there any essential reasons I don't see?
有什么我看不到的重要原因吗？
回答by Vincent Malgrat
The exception block is not needed, you might use it or not, depending on the context.
不需要异常块，您可以使用或不使用它，具体取决于上下文。
Here you are actively ignoring the exception (the procedure will return successfully) but most of the time if you're doing a SELECT INTO you want it to failif it doesn't return a row, consider:
在这里，您正在主动忽略异常（该过程将成功返回），但大多数情况下，如果您正在执行 SELECT INTO，您希望它在不返回行的情况下失败，请考虑：
PROCEDURE update_employee_salary (p_empno) IS
l_salary NUMBER;
BEGIN
SELECT sal INTO l_salary FROM emp WHERE empno = p_empno FOR UPDATE;
/* do something with emp data */
END;
Here I want my function to fail if it is called with an empnothat doesn't exist in the EMP table. I might catch the exception to raise a meaningful error message (with raise_application_error) but most of the time I'm happy with the ORA-01403.
在这里，我希望我的函数empno在使用 EMP 表中不存在的函数调用时失败。我可能会捕获异常以引发有意义的错误消息（使用raise_application_error），但大多数时候我对 ORA-01403 感到满意。
In general, the only exceptions you should catch are the expected exceptions (i.e. this should not be the standard to catch all ORA-01403, or all exceptions for that matter).
通常，您应该捕获的唯一异常是预期的异常（即，这不应成为捕获所有 ORA-01403 或与此相关的所有异常的标准）。
回答by Bob Jarvis - Reinstate Monica
But we still need to answer the question of "why is an exception thrown in the case where a SELECT has no data to be retrieved".
但是我们仍然需要回答“为什么在SELECT没有数据可检索的情况下会抛出异常”的问题。
I believe this is done because it's a common situation which might otherwise be overlooked. Writing code as though it always expects to find data is a common thing to do, and if we were supposed to put in error checks such as
我相信这样做是因为这是一种常见的情况，否则可能会被忽视。编写代码就好像它总是期望找到数据是一件常见的事情，如果我们应该进行错误检查，例如
SELECT <something...>
IF SQLCODE = 100 THEN -- No data found
<no-data handler>
END IF
it is likely IMHO that the check for SQLCODE = 100 would be skipped frequently. Having an exception raised rams it right up your nose that A) an important condition (no data found) occurred, and B) NO ALLOWANCE WAS MADE FOR THIS. IMO having the PL/SQL engine raise an exception is better than having the program continue merrily on its way under the assumption that data was retrieved when in fact it wasn't, which can lead to all sorts of other-than-merry problems.
恕我直言，对 SQLCODE = 100 的检查可能会经常被跳过。出现异常会让您觉得 A) 发生了重要情况（未找到数据），并且 B) 没有为此提供任何许可。IMO 让 PL/SQL 引擎引发异常比让程序继续愉快地继续执行要好，假设数据已被检索，而实际上并没有，这可能导致各种其他问题。
Share and enjoy.
分享和享受。
回答by Michael Pakhantsov
You can try use MIN for avoid use EXCEPTION clause.
您可以尝试使用 MIN 来避免使用 EXCEPTION 子句。
SELECT MIN(dummy)
INTO dummy
FROM dual
WHERE dummy = 'Y';
then dummy variable will be NULL
那么虚拟变量将为 NULL
回答by Thilo
Because you are doing SELECT INTO which requires exactly one row (more rows would also be an error).
因为您正在执行 SELECT INTO ，它只需要一行（更多行也会出错）。
If you can have one or no row, you can use a cursor.
如果您可以有一行或没有行，则可以使用游标。
It is not the database's job to decide for you that a missing row is not an error, and just set the value to null.
为您确定缺失的行不是错误并不是数据库的工作，只需将该值设置为 null。
回答by Immortal Code
You can also use the sql MAXor MINfunctions. If no row is return then these functions will return a NULL.
您还可以使用 sql MAX或MIN函数。如果没有返回任何行，那么这些函数将返回一个NULL。
For example: Select MAX(column1) Into variable From Table Where Column1 = 'Value';
例如： Select MAX(column1) Into variable From Table Where Column1 = 'Value';
The MAXfunction will return the Maximum value or if no row is returned then it will return NULL.
该MAX函数将返回最大值或者如果没有返回行则它会返回NULL。
回答by Gaurav londhe
MAX function works it does not throws error ORA-01403works when NULL is returned by select INTO
MAX 函数工作它不会抛出错误ORA-01403在 select INTO 返回 NULL 时工作
回答by Gaius
Because it's not clear what the PL/SQL engine should do - should it exit the block? Should it press on with NULL in the variable? What if in the next block you try to insert that into a NOT NULL column, how should it report the location of the error? Making it an exception forces you to be explicit about it.
因为不清楚 PL/SQL 引擎应该做什么——它应该退出块吗？它应该在变量中使用 NULL 吗？如果在下一个块中您尝试将其插入 NOT NULL 列中，它应该如何报告错误的位置？让它成为例外会迫使你明确说明它。
