PL/SQL 块问题：No data found 错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1256112/
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
PL/SQL block problem: No data found error
提问by Orapps
SET SERVEROUTPUT ON
DECLARE
v_student_id NUMBER := &sv_student_id;
v_section_id NUMBER := 89;
v_final_grade NUMBER;
v_letter_grade CHAR(1);
BEGIN
SELECT final_grade
INTO v_final_grade
FROM enrollment
WHERE student_id = v_student_id
AND section_id = v_section_id;
CASE -- outer CASE
WHEN v_final_grade IS NULL THEN
DBMS_OUTPUT.PUT_LINE ('There is no final grade.');
ELSE
CASE -- inner CASE
WHEN v_final_grade >= 90 THEN v_letter_grade := 'A';
WHEN v_final_grade >= 80 THEN v_letter_grade := 'B';
WHEN v_final_grade >= 70 THEN v_letter_grade := 'C';
WHEN v_final_grade >= 60 THEN v_letter_grade := 'D';
ELSE v_letter_grade := 'F';
END CASE;
-- control resumes here after inner CASE terminates
DBMS_OUTPUT.PUT_LINE ('Letter grade is: '||v_letter_grade);
END CASE;
-- control resumes here after outer CASE terminates
END;
the above code I have taken from the book "Oracle PL/SQL by Example, 4th Edition 2009"my problem is when I enter a student_idnot present in the table it returns me the following error
我从“Oracle PL/SQL by Example, 4th Edition 2009”一书中获取的上述代码我的问题是，当我student_id在表中输入一个不存在时，它返回以下错误
Error report: ORA-01403: no data found ORA-06512: at line 7 01403. 00000 - "no data found" *Cause: *Action:
but according to the book it should have returned a null value and then follow the case flow.
但根据这本书，它应该返回一个空值，然后按照案例流程进行。
回答by brian newman
When you are selecting INTO a variable and there are no records returned you should get a NO DATA FOUND error. I believe the correct way to write the above code would be to wrap the SELECT statement with it's own BEGIN/EXCEPTION/END block. Example:
当您选择 INTO 变量并且没有返回记录时，您应该收到 NO DATA FOUND 错误。我相信编写上述代码的正确方法是用它自己的 BEGIN/EXCEPTION/END 块包装 SELECT 语句。例子：
...
v_final_grade NUMBER;
v_letter_grade CHAR(1);
BEGIN
BEGIN
SELECT final_grade
INTO v_final_grade
FROM enrollment
WHERE student_id = v_student_id
AND section_id = v_section_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
v_final_grade := NULL;
END;
CASE -- outer CASE
WHEN v_final_grade IS NULL THEN
...
回答by pjp
Might be worth checking online for the errata section for your book.
可能值得在线检查您的书的勘误部分。
There's an example of handling this exception here http://www.dba-oracle.com/sf_ora_01403_no_data_found.htm
这里有一个处理这个异常的例子http://www.dba-oracle.com/sf_ora_01403_no_data_found.htm
回答by Adam Paynter
Your SELECTstatement isn't finding the data you're looking for. That is, there is no record in the ENROLLMENTtable with the given STUDENT_IDand SECTION_ID. You may want to try putting some DBMS_OUTPUT.PUT_LINEstatements before you run the query, printing the values of v_student_idand v_section_id. They may not be containing what you expect them to contain.
您的SELECT语句没有找到您要查找的数据。也就是说，ENROLLMENT表中没有给定STUDENT_ID和的记录SECTION_ID。你可能想尝试把一些DBMS_OUTPUT.PUT_LINE语句运行查询之前，打印的价值v_student_id和v_section_id。它们可能不包含您期望它们包含的内容。
回答by John Harris
There is an alternative approach I used when I couldn't rely on the EXCEPTIONblock at the bottom of my procedure. I had variables declared at the beginning:
当我不能依赖EXCEPTION程序底部的块时，我使用了另一种方法。我在开始时声明了变量：
my_value VARCHAR := 'default';
number_rows NUMBER := 0;
.
.
.
SELECT count(*) FROM TABLE INTO number_rows (etc.)
IF number_rows > 0 -- Then obtain my_value with a query or constant, etc.
END IF;
回答by Ankur Nirmalkar
This data not found causes because of some datatype we are using .
由于我们使用的某些数据类型，未找到此数据的原因。
like select empid into v_test
像选择 empid 到 v_test
above empid and v_test has to be number type , then only the data will be stored .
以上 empid 和 v_test 必须是数字类型，那么只会存储数据。
So keep track of the data type , when getting this error , may be this will help
所以跟踪数据类型，当得到这个错误时，可能会有所帮助
