MySQL存储过程if语句不起作用

I have the following stored procedure in MySQL

我在MySQL中有以下存储过程

CREATE DEFINER=`test_db`@`%` PROCEDURE `ADD_ATTENDANCE`(IN `programID` INT, IN `clientID` INT, IN `insDate` DATETIME, IN `updDate` DATETIME, IN `insUser` INT, IN `updUser` INT, IN `lessonDate` DATE, IN `lessonTime` TIME)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT 'Add attedance to my calendar'

BEGIN
    DECLARE max_sub, availability INT;
    DECLARE cursor_max_sub CURSOR FOR SELECT max_sub FROM app_lesson WHERE id = programID;
    DECLARE cursor_availability CURSOR FOR SELECT count(*) FROM attendance WHERE program_id = programID AND lesson_date = lessonDate AND lesson_time = lessonTime;
    OPEN cursor_max_sub;
    OPEN cursor_availability;

    read_loop: LOOP
        FETCH cursor_max_sub INTO max_sub;
        FETCH cursor_availability INTO availability;
        IF (availability < max_sub) THEN
            insert into attendance (program_id, client_id, ins_date, upd_date, ins_user, upd_user, lesson_date, lesson_time) 
            values (programID, clientID, insDate, updDate, insUser, updUser, lessonDate, lessonTime);
            LEAVE read_loop;
        ELSE
            insert into attendance_hold (program_id, client_id, ins_date, upd_date, ins_user, upd_user, lesson_date, lesson_time) 
            values (programID, clientID, insDate, updDate, insUser, updUser, lessonDate, lessonTime);
        END IF;
    END LOOP;
    CLOSE cursor_max_sub;
    CLOSE cursor_availability;
END;

Even though the cursor_max_sub is equal to 6 and the cursor_availability is equal to 4 my procedure always executes the else insert statement. Can you please help me out?

即使cursor_max_sub等于6且cursor_availability等于4,我的过程总是执行else insert语句。你能帮帮我吗?

Thanks!!!

1 个解决方案

#1

OK that was tricky... For some reason when i change the max_sub variable into maxNumberOfSubscription everything worked perfectly... Is max_sub some kind of reserved key word for MySQL or there was a complication because my variable had the same name with the returned field of select statement?

好的,这很棘手...出于某种原因,当我将max_sub变量更改为maxNumberOfSubscription时,一切都运行得很好...... max_sub是MySQL的某种保留关键字还是有一个复杂因为我的变量与返回的字段具有相同的名称选择声明?