MySQL存储过程的问题

Question

基本上，我会在学生注册/退出类(class)时运行此程序。我正在尝试设置 Student_total = 类(class)中的学生人数，然后使用 (student_total + 1) 更新相应部分，但我无法找到存储过程的良好文档。我的 Declare Student_total int; 行出现错误。我做错了什么？

DELIMITER $$

CREATE PROCEDURE `mydb`.`update_seats` (IN section_ID varchar(20))
BEGIN

SET @section_id=section_id;

DECLARE student_total int;
-- count the number of students in the course --

SET student_total = SELECT count(student_ID) from course
WHERE section_ID = @section_id;


Update Section SET students_enrolled = (student_total + 1) 
WHERE section_ID = @section_id;




END

Answer 1

问题

错误1

来自MySql documentation :

DECLARE is permitted only inside a BEGIN ... END compound statement and must be at its start, before any other statements.

因此，您应该将 DECLARE... 语句移至 SET @section_id... 语句之前。

错误2

您正在尝试使用无效的 snytax 将值选择到变量中!您应该使用 SELECT ... INTO 而不是 SET ... = SELECT ... (这是无效语法)。

删除冗余

无需将参数(section_ID)分配给全局变量(@section_ID)。您只需更改参数名称即可避免与 section.section_ID 列发生名称冲突。

解决方案

DELIMITER ;;

CREATE PROCEDURE `update_seats` (IN p_section_ID VARCHAR(20))
BEGIN
    DECLARE student_total INT;

    SELECT  count(student_ID)
    INTO    student_total
    FROM    course
    WHERE   section_ID = p_section_ID;

    UPDATE  section
    SET     students_enrolled = (student_total + 1)
    WHERE   section_ID = p_section_ID;
END;;

DELIMITER ;