mysql - 声明变量时 MySQL 存储过程中的语法错误

Question

我正在尝试创建一个过程，以根据其他几个表自动将一些值添加到表中。

这些是我的表格:

ENROLMENT 表(我在其中存储学生注册的类(class)):

+----------------+---------+------+-----+---------+----------------+
| Field          | Type    | Null | Key | Default | Extra          |
+----------------+---------+------+-----+---------+----------------+
| ENROLMENT_NR   | int(10) | NO   | PRI | NULL    | auto_increment |
| ENROLMENT_DATE | date    | NO   |     | NULL    |                |
| STUDENT_NR     | int(10) | NO   | MUL | NULL    |                |
| PROGRAMME_NR   | int(10) | NO   | MUL | NULL    |                |
+----------------+---------+------+-----+---------+----------------+

PROGRAMME_MODULE 表(列出程序和相应模块组合的连接表):

+--------------+---------+------+-----+---------+-------+
| Field        | Type    | Null | Key | Default | Extra |
+--------------+---------+------+-----+---------+-------+
| PROGRAMME_NR | int(10) | NO   | PRI | NULL    |       |
| MODULE_NR    | int(10) | NO   | PRI | NULL    |       |
+--------------+---------+------+-----+---------+-------+

REGISTRATION 表(我想将学生的注册信息存储到他们注册的每个类(class)的模块中):

+-------------------+---------+------+-----+---------+----------------+
| Field             | Type    | Null | Key | Default | Extra          |
+-------------------+---------+------+-----+---------+----------------+
| REGISTRATION_NR   | int(10) | NO   | PRI | NULL    | auto_increment |
| REGISTRATION_DATE | date    | NO   |     | NULL    |                |
| MODULE_NR         | int(10) | NO   | MUL | NULL    |                |
| STUDENT_NR        | int(10) | NO   | MUL | NULL    |                |
+-------------------+---------+------+-----+---------+----------------+

我以前从未在 MySQL 中做过程序。我只用 PL/SQL 做了一些基本的。基于一些研究，我创建了这个过程:

DROP PROCEDURE IF EXISTS REGISTER_STUDENTS_PROC;
DELIMITER ;;
CREATE PROCEDURE REGISTER_STUDENTS_PROC()
DECLARE st_nr INT DEFAULT 0;
DECLARE prg_nr INT DEFAULT 0;
DECLARE enrl_date DATE DEFAULT '0000-00-00';
DECLARE mod_nr INT DEFAULT 0;
DECLARE i INT DEFAULT 0;
DECLARE n INT DEFAULT 0;
DECLARE ii INT DEFAULT 0;
DECLARE nn INT DEFAULT 0;
SELECT COUNT(*) FROM TABLE ENROLMENT INTO n;
SET i=0;
SELECT COUNT(*) FROM TABLE PROGRAMME_MODULE INTO nn;
SET ii=0;
BEGIN
WHILE i<n DO
    SELECT STUDENT_NR FROM TABLE ENROLMENT INTO st_nr;
    SELECT PROGRAMME_NR FROM TABLE ENROLMENT INTO prg_nr;
    SELECT ENROLMENT_DATE FROM TABLE ENROLMENT INTO enrl_date;
    BEGIN
    WHILE ii < n DO
        SELECT MODULE_NR FROM TABLE PROGRAMME_MODULE WHERE PROGRAMME_NR = prg_nr INTO mod_nr;
        INSERT INTO REGISTRATION (REGISTRATION_DATE,MODULE_NR,STUDENT_NR) VALUES (enrl_date,mod_nr,st_nr);
        SET ii = ii + 1;
    END WHILE;
    SET i = i + 1;
END WHILE;
END;
;;

这是我的错误:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECLARE st_nr INT DEFAULT 0;
DECLARE prg_nr INT DEFAULT 0; DECLARE enrl_date DAT' at line 2.

我发现 SQL 的一个困难是有些错误非常不明确，这对初学者来说是一个额外的挑战......这可能是一些基本的东西，但我看不到。

谢谢

Answer 1

根据@Mihai 的评论，这里是没有语法错误的代码:

DROP PROCEDURE IF EXISTS REGISTER_STUDENTS_PROC;
DELIMITER ;;
CREATE PROCEDURE REGISTER_STUDENTS_PROC()
BEGIN
DECLARE st_nr INT DEFAULT 0;
DECLARE prg_nr INT DEFAULT 0;
DECLARE enrl_date DATE DEFAULT '0000-00-00';
DECLARE mod_nr INT DEFAULT 0;
DECLARE i INT DEFAULT 0;
DECLARE n INT DEFAULT 0;
DECLARE ii INT DEFAULT 0;
DECLARE nn INT DEFAULT 0;
SELECT COUNT(*) FROM ENROLMENT INTO n;
SET i=0;
SELECT COUNT(*) FROM PROGRAMME_MODULE INTO nn;
SET ii=0;
WHILE i<n DO
    SELECT STUDENT_NR FROM ENROLMENT INTO st_nr;
    SELECT PROGRAMME_NR FROM ENROLMENT INTO prg_nr;
    SELECT ENROLMENT_DATE FROM ENROLMENT INTO enrl_date;
    BEGIN
    WHILE ii < n DO
        SELECT MODULE_NR FROM PROGRAMME_MODULE WHERE PROGRAMME_NR = prg_nr INTO mod_nr;
        INSERT INTO REGISTRATION (REGISTRATION_DATE,MODULE_NR,STUDENT_NR) VALUES (enrl_date,mod_nr,st_nr);
        SET ii = ii + 1;
    END WHILE;
    END;
    SET i = i + 1;
END WHILE;
END;
;;

---

因此，进行了三处更正:

1- 变量必须在 BEGIN 之后声明;2- 所有 BEGIN语句必须以 END 结束(嵌套的没有关闭)；3- 必须使用 SELECT FROM <<TABLE_NAME>>而不是 SELECT FROM TABLE <<TABLE_NAME>> .