錯誤代碼
CREATE PROCEDURE searchStudentGrade(IN id CHAR)
BEGIN
IF id IS NULL
SELECT ...; -- 錯誤：IF 後必須跟 THEN
ELSE
SELECT ...;
END IF;
END;
修正代碼
CREATE PROCEDURE searchStudentGrade(IN id CHAR)
BEGIN
IF id IS NULL THEN -- 關鍵：添加 THEN
SELECT student.StudentID, student.StudentName, student.ClassNumber,
grade.ExamID, course.CourseName, grade.Grade, course.CourseID
FROM student, grade, course
WHERE student.StudentID = grade.StudentID
AND grade.CourseID = course.CourseID;
ELSE
SELECT student.StudentID, student.StudentName, student.ClassNumber,
grade.ExamID, course.CourseName, grade.Grade, course.CourseID
FROM student, grade, course
WHERE student.StudentID = grade.StudentID
AND grade.CourseID = course.CourseID
AND grade.StudentID = id;
END IF;
END;
MySQL 流程控制語法速查
| 結構 | 語法模板 |
|---|
| IF-THEN-ELSE | IF condition THEN statements; [ELSE statements;] END IF; |
| CASE | CASE WHEN cond THEN stmt; [ELSE stmt;] END CASE; |
| LOOP | [label:] LOOP statements; END LOOP; |
| WHILE | WHILE condition DO statements; END WHILE; |
| REPEAT | REPEAT statements; UNTIL condition END REPEAT; |
Navicat 使用技巧
- 編輯器中輸入
IF 後按 Ctrl+Space 喚醒代碼補全，自動插入 THEN ... END IF; 骨架。
- 執行前點擊「語法檢查」圖標（或 F7），提前發現語法錯誤。
- 存儲過程參數類型建議具體化：
IN id INT 或 IN id VARCHAR(20)，避免 CHAR 隱式補空格導致匹配失敗。
