CURSOR seems empty but SELECT returns non-empty data

Question

I have a mysql Procedure for awarding allowances to socially active students. In this Procidure I'm updating Students table. Inside this procedure I have define a cursor which gives 2 values (student_id and student_participations - number activities where student participated). This means in my select statement, I have taken 2 tables: students, and subquery of students and participations tables. Since I know that this select query returns data outside procedure, I can't figure it out, why cursor remains empty.

Procedure:

DELIMITER //
create procedure update_st_value_by_participation(in how_much int)
begin

    declare student_id, value_multiplier int;
    declare is_end int default 0;
    
    declare students_cursor cursor for 
    select studs.st_id, tb1.st_participations from studs, 
    (select student_id as st_id, count(student_id) as st_participations 
    from participation group by student_id) as tb1
    where tb1.st_participations > 1 and tb1.st_id = studs.st_id;
    
    declare continue handler for not found set is_end=1;
    
    open students_cursor;
        curs: Loop
            fetch students_cursor into student_id, value_multiplier;
            
            if is_end then
                leave curs;
            end if;
            update studs
            set st_value = st_value + how_much * (value_multiplier - 1)
            where st_id = student_id;
        end loop curs;
    
    close students_cursor;
end //

(is_end takes value=1 somehow)

Tables, that I've been using:

create table studs(
st_id int primary key auto_increment,
st_name varchar(30),
st_surname varchar(30),
st_course int,
st_speciality enum('km', 'webs', 'mobile', 'mech'),
st_form enum('free', 'not free'),
st_value float
);

create table activities(
activity_id int primary key auto_increment,
activity_name varchar(50),
activity_description varchar(200),
activity_date date,
activity_place varchar(50),
activity_organizer varchar(30)
);

create table participation(
participation_id int primary key auto_increment,
student_id int,
activity_id int,
participation_role varchar(20),
participation_result varchar(50),

constraint ref_studs_to_participation foreign key (student_id) references studs (st_id),
constraint ref_activities_to_participation foreign key (activity_id) references activities (activity_id)
);

Answer 1

your problem is that your variable is called studento_id.

the rule is never use column names as variable names.

The easiest way to comply with he rule is to use a prefix like p_ for every variable, so you haven't duplicate names ( as lon as you don't have column names that start with p_, but that is only a sample.

So after changing the the variable names and simplifying your cursor.

create table studs(
st_id int primary key auto_increment,
st_name varchar(30),
st_surname varchar(30),
st_course int,
st_speciality enum('km', 'webs', 'mobile', 'mech'),
st_form enum('free', 'not free'),
st_value float
);

create table activities(
activity_id int primary key auto_increment,
activity_name varchar(50),
activity_description varchar(200),
activity_date date,
activity_place varchar(50),
activity_organizer varchar(30)
);

create table participation(
participation_id int primary key auto_increment,
student_id int,
activity_id int,
participation_role varchar(20),
participation_result varchar(50),

constraint ref_studs_to_participation foreign key (student_id) references studs (st_id),
constraint ref_activities_to_participation foreign key (activity_id) references activities (activity_id)
);

INSERT INTO studs VALUES(NULL ,'testA','testB',1,'km','free', 1.6);
INSERT INTO studs VALUES(NULL ,'testA1','testB1',2,'km','free', 1.1);
INSERT INTO studs VALUES(NULL ,'testA2','testB2',3,'km','free', 1.1);

INSERT INTO activities VALUEs (NULL,'test1','test2', NOW(),'test4','test5');
INSERT INTO activities VALUEs (NULL,'test11','test21', NOW(),'test41','test51');
INSERT INTO activities VALUEs (NULL,'test12','test22', NOW(),'test42','test52');

INSERT INTO participation VALUES (NULL,1,1,'test','test');
INSERT INTO participation VALUES (NULL,1,2,'test','test');
INSERT INTO participation VALUES (NULL,1,3,'test','test');
INSERT INTO participation VALUES (NULL,2,1,'test','test');
INSERT INTO participation VALUES (NULL,2,2,'test','test');
INSERT INTO participation VALUES (NULL,3,3,'test','test');
INSERT INTO participation VALUES (NULL,3,2,'test','test');
INSERT INTO participation VALUES (NULL,3,3,'test','test');

create procedure update_st_value_by_participation(in p_how_much int)
begin

    declare p_student_id, p_value_multiplier int;
    declare is_end int default 0;
    
    declare students_cursor cursor for 
     select student_id, count(student_id) as st_participations 
    from participation group by student_id;
    
    declare continue handler for not found set is_end=1;
    
    open students_cursor;
        curs: Loop
            fetch students_cursor into p_student_id, p_value_multiplier;
            
            if is_end then
                leave curs;
            end if;
            update studs
            set st_value = st_value + p_how_much * (p_value_multiplier - 1)
            where st_id = p_student_id;
        end loop curs;
    
    close students_cursor;
end

CALL update_st_value_by_participation(21)

SELECT * FROM studs

st_id	st_name	st_surname	st_course	st_speciality	st_form	st_value
1	testA	testB	1	km	free	43.6
2	testA1	testB1	2	km	free	22.1
3	testA2	testB2	3	km	free	43.1

fiddle