PL/SQL 块问题:No data found 错误

SET SERVEROUTPUT ON宣布v_student_id NUMBER := &sv_student_id;v_section_id 编号:= 89；v_final_grade NUMBER;v_letter_grade CHAR(1);开始选择最终成绩INTO v_final_grade从注册WHERE student_id = v_student_idAND section_id = v_section_id;CASE -- 外箱当 v_final_grade 为 NULL THENDBMS_OUTPUT.PUT_LINE('没有最终成绩.');别的CASE -- 内部CASEWHEN v_final_grade >= 90 THEN v_letter_grade := 'A';WHEN v_final_grade >= 80 THEN v_letter_grade := 'B';当 v_final_grade >= 70 THEN v_letter_grade := 'C';WHEN v_final_grade >= 60 THEN v_letter_grade := 'D';ELSE v_letter_grade := 'F';结束案例；-- 内部 CASE 终止后，控制在此处恢复DBMS_OUTPUT.PUT_LINE('字母等级为:'||v_letter_grade);结束案例；-- 外部 CASE 终止后，控制在此处恢复结尾;

以上代码是我从Oracle PL/SQL by Example, 4th Edition 2009"一书中摘录的，我的问题是当我输入一个student_id表它返回给我以下错误

<前>错误报告:ORA-01403:未找到数据ORA-06512:在第 7 行01403. 00000 - 未找到数据"*原因:*行动:

但根据这本书，它应该返回一个空值，然后按照案例流程进行.

解决方案

当您选择 INTO 变量并且没有返回任何记录时，您应该得到一个 NO DATA FOUND 错误.我相信编写上述代码的正确方法是用它自己的 BEGIN/EXCEPTION/END 块包装 SELECT 语句.示例:

<代码>...v_final_grade NUMBER;v_letter_grade CHAR(1);开始开始选择最终成绩INTO v_final_grade从注册WHERE student_id = v_student_idAND section_id = v_section_id;例外WHEN NO_DATA_FOUND THENv_final_grade := NULL;结尾;CASE -- 外箱当 v_final_grade 为 NULL THEN...

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_id not present in the table it returns me the following error

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.

解决方案

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:

...
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
      ...