嘗試在 oracle 中使用“游標”PL/SQL 編寫查詢時遇到錯誤“未找到數據”

希望你做得很好。我是 oracle PL/SQL 的新手，我正在嘗試編寫一個查詢，目的是練習 ‘Cursor’s 。我有三個具有以下結構的表：

Student:(Student_id , Student_name)
Course :(COURSE_NO , description)
Student_Course:(Student_id , Course_id , Nomreh)

表Student_Course儲存每個學生在每門課程中的分數。“Nomreh”這個詞是馬克的波斯語。我只想知道我的查詢有什麼問題？一切似乎都完美無瑕！但我收到這些錯誤：

ORA-01403:no data found
ORA-06512:at line 13

這是我的查詢：

DECLARE
 CURSOR C1 IS
       SELECT S.STUDENT_ID FROM STUDENT S;

       C1_REC               C1%ROWTYPE;
       V_STUDENT_FIRST_NAME STUDENT.STUDENT_NAME%TYPE;
       V_COURSE_DESCRIPTION COURSE.DESCRIPTION%TYPE;
       V_NOMRE              STUDENT_COURSE.NOMREH%TYPE;

 BEGIN
    FOR C1_REC IN C1 LOOP

       SELECT S.STUDENT_NAME, C.DESCRIPTION, SC.NOMREH
       INTO V_STUDENT_FIRST_NAME, V_COURSE_DESCRIPTION, V_NOMRE
       FROM STUDENT S
            INNER JOIN STUDENT_COURSE SC
       ON S.STUDENT_ID = SC.STUDENT_ID
            INNER JOIN COURSE C
       ON SC.COURSE_ID = C.COURSE_NO
       WHERE S.STUDENT_ID = C1_REC.STUDENT_ID;

 INSERT INTO TARGETTABLE
 (STUDENT_NAME, COURSE_NAME, NOMRE)
     VALUES
 (V_STUDENT_FIRST_NAME, V_COURSE_DESCRIPTION, V_NOMRE);
END LOOP;
  COMMIT;
END;

提前致謝

循環內的 SQL 語句可能沒問題，但仍然沒有返回數據。

SELECT ... INTO ...``ORA-01403在以下情況下拋出錯誤"no data found"：

declare
 l_num number;
begin
 select 1 into l_num from dual where 1 = 2;
end;
/

ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 4

您編寫的程式碼不僅在查詢不返回行時失敗，而且如果查詢返回多行（學生參加多門課程）也會失敗：

declare
 l_num number;
begin
 select 1 into l_num from dual connect by level <= 2;
end;
/

ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 4

這是應該起作用的東西（僅出於此範例的目的）：

DECLARE
 CURSOR C1 IS
       SELECT S.STUDENT_ID FROM STUDENT S;
       C1_REC               C1%ROWTYPE;
BEGIN
   FOR C1_REC IN C1 LOOP

      INSERT INTO TARGETTABLE
      (STUDENT_NAME, COURSE_NAME, NOMRE)
      SELECT S.STUDENT_NAME, C.DESCRIPTION, SC.NOMREH
      FROM STUDENT S
           INNER JOIN STUDENT_COURSE SC
      ON S.STUDENT_ID = SC.STUDENT_ID
           INNER JOIN COURSE C
      ON SC.COURSE_ID = C.COURSE_NO
      WHERE S.STUDENT_ID = C1_REC.STUDENT_ID;
END LOOP;
 COMMIT;
END; 
/

引用自：https://dba.stackexchange.com/questions/241711